Microsoft Excel имеет сотни инструментов и функций, поэтому очень легко упустить из виду некоторые из самых полезных. В этом руководстве я поделюсь четырьмя менее известными, скрытыми приемами, которые могут изменить то, как вы используете популярное программное обеспечение для работы с электронными таблицами.
4. Поиск цели для долгосрочного планирования
Малоизвестный инструмент Microsoft Excel «Подбор цели» поможет вам, когда вы знаете конечную цель, но не знаете, как ее достичь.
Допустим, вы планируете купить новый дом через два года. В настоящее время вы ежемесячно откладываете $400 при годовой процентной ставке 2.5%, то есть за два года вы сэкономите $9833.55, как рассчитано с помощью функции будущей стоимости (FV):
=БС(B1/12,B2,-B3)
в котором B1 / 12 превращает годовую процентную ставку (ставка, ячейка B1) в ежемесячную процентную ставку, B2 количество платежей (nper), и B3 — это сумма платежа (pmt), указанная как отрицательное число (поскольку она списывается с вашего обычного счета).
Однако вы намерены собрать в общей сложности $15,000 XNUMX, чтобы покрыть стоимость депозита и другие расходы, которые неизбежно возникают при переезде, поэтому вам нужно подсчитать, сколько вы должны откладывать в месяц в течение двух лет, чтобы достичь этой цели. Вот тут-то и вступает в игру инструмент Excel Goal Seek.
Сначала выберите ячейку, содержащую вычисление (в данном случае ячейку B4), и на вкладке «Данные» на ленте нажмите «Анализ «Что если»» > «Подбор цели».
Общее значение должно быть вычислением. Поиск цели не будет работать, если вы введете это значение вручную.
Появившееся диалоговое окно «Поиск цели» содержит три поля:
- Установить ячейку: Это ячейка, которая содержит текущий итоговый расчет, в данном примере это ячейка B4. Поскольку вы выбрали эту ячейку перед запуском диалогового окна, она заполнилась автоматически.
- Оценить: Это значение, которого вам нужно достичь для достижения конечной цели. В данном случае это 15000.
- При изменении ячейки: Переменная, которую можно изменить в этом расчете, — это сумма, сэкономленная за месяц (ячейка B3).
После нажатия «ОК» Excel выполняет несколько итераций расчета, прежде чем скорректировать By Changing Cell, на который вы ссылались ранее. В этом случае он сообщает вам, что вы должны откладывать $610.16 в месяц в течение двух лет, чтобы достичь своей цели.
Однако в этот момент вы понимаете, что не можете позволить себе откладывать такую сумму каждый месяц. Вместо этого вы решаете продолжать откладывать по 400 долларов каждый месяц и переедете, как только достигнете цели в 15,000 XNUMX долларов. Поэтому на этот раз вы хотите, чтобы Excel подсказывал вам, сколько времени это займет.
Сначала вручную сбросьте настройки плт значение в ячейке B3 до $400. Затем выберите ячейку, содержащую расчет конечного значения, и нажмите Анализ «Что если» > Поиск цели на вкладке Данные.
Опять же, заданная ячейка — B4, а конечное значение по-прежнему 15000, но ячейка, которую можно изменить, — это ячейка месяца, то есть B2.
Наконец, нажмите «ОК», чтобы узнать, что накопление 15,000 400 долларов США путем уплаты 2.5 долларов США в месяц с процентной ставкой 36% займет чуть более XNUMX месяцев.
Итак, инструмент Excel Goal Seek помог вам вычислить, что если вы отчаянно хотите переехать в другой дом через два года, вам нужно будет собирать $610.16 в месяц. С другой стороны, если вы хотите продолжать платить только £400 в месяц, вы можете переехать в другой дом всего за три года.
Инструмент поиска цели в Excel не только поможет вам управлять сбережениями, но и поможет рассчитать, сколько товаров вам необходимо продать, чтобы достичь цели, или какую процентную ставку вам необходимо обеспечить, чтобы кредит стал доступным.
3 Инструмент камеры для создания моментальных снимков данных
Инструмент «Камера» в Microsoft Excel позволяет вам сделать снимок некоторых данных и вставить его как изображение. Более того, это изображение является динамическим, то есть оно обновляется, чтобы отразить любые изменения в исходных данных.
Этот инструмент может быть полезен, если вы хотите создать панель мониторинга, которая извлекает данные из нескольких листов в вашей рабочей книге, а также он может быть удобен, если вы работаете с большими наборами данных и хотите держать ключевую информацию на виду. Поскольку данные дублируются как изображение, они располагаются поверх ячеек Excel, не влияя на макет вашей электронной таблицы.
Из всех инструментов, которые я обсуждаю в этом руководстве, камера, вероятно, самая скрытая, потому что она не доступна сразу через ленту. Вместо этого вам придется добавить ее на панель быстрого доступа (QAT).
Для этого нажмите стрелку вниз на правой стороне ленты. Если вы видите «Скрыть панель быстрого доступа», QAT уже активирован. Однако если вы видите «Показать панель быстрого доступа», нажмите эту опцию, чтобы активировать ее.
Затем нажмите стрелку вниз на панели быстрого доступа в верхней части окна Excel и выберите «Дополнительные команды».
Затем в раскрывающемся меню «Выбрать команду из» нажмите «Все команды», прокрутите и выберите «Камера». Затем нажмите «Добавить», чтобы добавить ее в QAT, и нажмите «ОК», чтобы закрыть диалоговое окно.
Теперь инструмент «Камера» находится на вашем QAT и готов к использованию.
Давайте представим, что вам нужно сделать снимок некоторых данных, введенных в таблицу Excel. Для этого выберите ячейки, содержащие эти данные, и щелкните значок «Камера» в вашем QAT.
Чтобы выделить все ячейки в таблице Excel, сначала выделите одну ячейку, затем нажмите Ctrl+A.
Теперь щелкните ячейку, в которую вы хотите продублировать версию изображения скопированных данных.
Инструмент «Камера» может работать с отдельными рабочими книгами, но изменения исходных данных будут отражены во вставленном изображении только в том случае, если вы вошли в свою учетную запись Microsoft и активировали функцию автосохранения для обоих файлов.
Поскольку данные реплицируются в виде изображения, вы можете щелкнуть и перетащить маркеры, чтобы изменить размер графика, а также можете отформатировать снимок с помощью вкладки «Формат изображения» на ленте.
Инструмент «Камера» также работает с другими элементами Excel, такими как диаграммы и фигуры. Просто выберите ячейки позади и вокруг объекта и щелкните инструмент «Камера» в панели быстрого доступа. После вставки данных в виде изображения обрежьте изображение, чтобы удалить ненужное пространство по краям.
Скройте линии сетки (Alt > W > V > G) и кнопки фильтров (Ctrl+Shift+L) из ваших данных перед тем, как захватить их с помощью инструмента «Камера». Это сделает изображение более аккуратным, и поскольку кнопки фильтров не работают во вставленной графике, в них нет необходимости.
2 группы данных для управления большими наборами данных
Если вы работаете с таблицей Microsoft Excel, содержащей множество столбцов, вам может надоесть постоянная прокрутка влево и вправо для чтения данных. Кроме того, иногда некоторые столбцы не всегда должны быть видны.
Предположим, у вас есть рабочая тетрадь, содержащая ежемесячные данные о продажах в разных магазинах, а также годовой итог в крайнем правом столбце.
Чтобы проанализировать годовые данные и сделать цифры более удобными для чтения, вам нужно скрыть столбцы B–Q, то есть столбцы A и R будут располагаться рядом друг с другом на вашем листе.
На этом этапе у вас может возникнуть соблазн выбрать столбцы от B до Q, щелкнуть правой кнопкой мыши по одному из заголовков выбранных столбцов и нажать «Скрыть». Однако легко забыть, что эти столбцы скрыты, а процесс их повторного отображения может оказаться обременительным.
Вместо этого выберите столбцы от B до Q, щелкнув заголовок столбца B и перетащив выделение на столбец Q.
Excel также позволяет использовать этот же метод для создания групп строк.
Далее на вкладке «Данные» нажмите «Структура» и выберите верхнюю половину кнопки «Группировать».
Теперь вы можете нажать на знак «минус» или линию индикатора группы над этими столбцами, чтобы скрыть эту группу.
Обратите внимание, как при этом знак «минус» превращается в знак «плюс», на который вы можете нажать, чтобы при желании снова развернуть эту группу.
Вы также можете создавать подгруппы внутри основных групп, при условии, что каждая подгруппа отделена пробелом или итоговым столбцом.
Используя тот же пример, что и выше, предположим, что вы также хотите свернуть ежемесячные данные, оставив видимыми только квартальные и годовые итоги. Это возможно, поскольку столбцы E, I, M и Q действуют как барьеры между каждой подгруппой.
Итак, чтобы создать подгруппу для Q1, выберите столбцы B–D и нажмите «Группировать» на вкладке «Данные».
Теперь, помимо основной группы, включающей ячейки от B до Q, в вашей электронной таблице также есть подгруппа, включающая ячейки от B до D.
Повторите этот процесс для оставшихся кварталов, пока все подгруппы не получат индивидуальные линии группового индикатора и знаки минус.
Затем щелкните знаки «минус» четырех подгрупп, чтобы отобразить только квартальные и годовые итоги, помня, что вы можете щелкнуть знаки «плюс», чтобы снова развернуть их.
Помимо нажатия на знаки минус и плюс для сворачивания и разворачивания группы или подгруппы, вы можете нажать на числовые кнопки слева от линий индикатора группы. Нажатие на наибольшее число открывает все сгруппированные данные, а нажатие на наименьшее число сводит данные к наименьшей подгруппе, которую вы создали.
1 Пользовательские числовые форматы для быстрой визуализации данных
Инструмент «Пользовательский числовой формат» — одна из моих любимых скрытых функций Microsoft Excel, поскольку она, вероятно, экономит мне больше всего времени.
Каждая ячейка в новой электронной таблице имеет общий числовой формат.
Это означает, что числа отображаются точно так же, как они введены, если только они не являются десятичными числами, которые слишком широки для ячейки, или числами, которые начинаются с нуля.
Однако когда вы вставляете в ячейку определенный тип числа, например дату или процент, Excel распознает его и соответствующим образом обновляет числовой формат.
Одним из скрытых числовых форматов является «Пользовательский», который позволяет форматировать ячейку способом, не указанным в стандартных числовых форматах Excel.
Выберите ячейку или ячейки, к которым вы хотите применить форматирование, которое вы собираетесь ввести, и на вкладке «Главная» на ленте щелкните кнопку запуска диалогового окна в углу группы «Число».
Либо нажмите Ctrl+1, чтобы открыть диалоговое окно «Формат ячеек», нажмите Tab, чтобы открыть меню «Категория», и введите Кас.
Информация, которую вы вводите в поле «Тип», зависит от типов данных, которые вы хотите отформатировать.
Первый аргумент, который вы вводите, форматирует положительные числа, второй — отрицательные числа, третий — нули, а четвертый — текст. Обратите внимание, как для разделения аргументов используются точки с запятой.
ПОЛОЖИТЕЛЬНЫЙ;ОТРИЦАТЕЛЬНЫЙ;НОЛЬ;ТЕКСТ
Одна из комбинаций, которую вы можете ввести в это поле:
;;;
что сообщает Excel, что вы хотите, чтобы ячейка отображалась пустой, поскольку вы не указали никакого форматирования для какого-либо типа данных.
С другой стороны, если набрать:
[Зеленый]#.00;[Красный]#.00;[Синий];
преобразует положительные числа в зеленое значение с двумя десятичными знаками, отрицательные числа в красное значение с двумя десятичными знаками, нули в синее и создает пустую ячейку для любых текстовых значений.
Вы, возможно, привыкли достигать того же результата с помощью инструмента условного форматирования Excel, поскольку он более заметен на ленте, чем пользовательский числовой формат. Однако это требует ввода различных правил и занимает гораздо больше времени.
Если вы хотите отформатировать значения, используя цвета, отличные от черного, белого, красного, зеленого, синего, желтого, пурпурного и голубого, введите код цвета в квадратных скобках вместо названия цвета.
Вы также можете добавлять символы в пользовательский числовой формат ячейки. Например, набрав:
▲;▼;»=»;
отображает стрелку вверх вместо положительных чисел, стрелку вниз вместо отрицательных чисел и знак равенства вместо нулей.
Если вы используете операционную систему Windows, нажмите Alt+30 для ▲ и Alt+31 для ▼.
Более того, вы можете объединить все вышеперечисленные коды, чтобы создать аккуратно отформатированные ячейки и улучшить визуальную привлекательность ваших данных. Например, набрав
[Зелёный]▲#%;[Красный]▼#%;»-«;
для пользовательского формата ячеек D2–D12 получается следующий результат:
Сочетания клавиш Microsoft Excel — это еще одна скрытая функция, которая действительно может ускорить ваш рабочий процесс. Наряду с использованием известных Ctrl+C (копировать) и Ctrl+V (вставить), потратьте немного времени на изучение некоторых менее известных сочетаний клавиш, таких как F2 для входа в режим редактирования ячейки и Ctrl+Shift+L для добавления кнопок фильтров в первую строку ваших данных.
