Gearmix » Excel для продвинутых: 9 полезных трюков » Печать

^[1]Microsoft Excel, пожалуй, лучшая офисная программа из когда-либо созданных. На ней держатся целые отрасли экономики, так что, виртуозно овладев этим инструментом, вы сразу заметите, что дела у вашего бизнеса идут в гору.

Кроме множества базовых приёмов работы с этой программой, которые вы наверняка уже знаете, полезно изучить некоторые хитрости «для продвинутых», которые сделают вас на голову выше всех остальных. Так что, если хотите впечатлить своих боссов и разгромить конкурентов, вам пригодятся эти 9 хитрых функций Excel.

1. Функция  ВПР

^[2]

^{[3] [4]} Эта функция позволяет быстро найти нужное вам значение в таблице. Например, нам нужно узнать финальный балл Бетт, мы пишем: =ВПР(“Beth”,A2:E6,5,0), где Beth – имя ученика, A2:E6 – диапазон таблицы, 5 – номер столбца, а 0 означает, что мы не ищем точного соответствия значению.

Функция очень удобна, однако нужно знать некоторые особенности её использования. Во-первых, ВПР ищет только слева направо, так что, если вам понадобится искать в другом порядке, придется менять параметры сортировки целого листа. Также если вы выберете слишком большую таблицу, поиск может занять много времени.

2. Функция ИНДЕКС

^[5]Отражает значение или ссылку на ячейку на пересечении конкретных строки и столбца в выбранном диапазоне ячеек.

Например, чтобы посмотреть, кто стал четвёртым в списке самых высокооплачиваемых топ-менеджеров Уолл-стрит, набираем: =ИНДЕКС(А3:А11, 4).

3. Функция ПОИСКПОЗ

^[6]Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне(Диапазон. Две или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными.) ячеек и отражает относительную позицию этого элемента в диапазоне.

Например, вот как можно найти позицию Bank of America в выбранном диапазоне ячеек.

Чем это полезно?

^{[7] [8] [9] [10] [11]}По отдельности ИНДЕКС и ПОИСКПОЗ не особо полезны. Но вместе они могут заменить функцию ВПР.

Например, чтобы в большой таблице найти, кто является главой Wells Fargo, пишем =ИНДЕКС(А3:А11,ПОИСКПОЗ(«Wells Fargo»,B3:B11,0).
С помощью функции ВПР этого не сделать, потому что она ищет только слева направо. А сочетание двух последних позволяет сделать это с легкостью.

4. 3D-сумма

^[12]Допустим, вы решили подсчитать, сколько денег вы тратите каждый день и вести учёт в течение семи недель. Под каждую неделю вы создали отдельный лист, куда по дням заносите расходы на алкоголь, еду и прочие мелочи.

^[13]Теперь на вкладке TOTAL (ИТОГО) нам нужно увидеть, сколько и в какой день вы потратили за этот период. Набираем =СУММ(‘Week1:Week7’!B2), и формула суммирует все значения в ячейке B2 на всех вкладках.

^[14]Теперь, заполнив все ячейки, мы выяснили, в какой день недели тратили больше всего, а также в итоге подбили все свои расходы за эти 7 недель.

^[15]5. $

^[16]Одна из самых удобных функций в арсенале Excel, а также одна из самых простых – это знак $. Он указывает программе, что не нужно делать автоматической корректировки формулы при её копировании в новую ячейку, как Excel поступает обычно.

^{[16] [17] [18] [19] [20]}На рисунках выше четыре разных способа указать программе, что верхняя левая ячейка должна быть идентична ячейке А1. Теперь, если вы будете копировать эту формулу со знаком $ в ячейки ниже, её содержимое останется неизменным.

^[21]

При этом знак $ перед «А» не даёт программе изменять формулу по горизонтали, а перед «1» – по вертикали. Если же написать «$A$1», то значение скопированной ячейки будет одинаковым в любом направлении.

^[22]Очень удобный приём, когда приходится работать с большими базами данных.

6. &

^[23]Если вы хотите собрать все значения из разных ячеек в одну, вы можете использовать функцию СЦЕПИТЬ. Но зачем набирать столько букв, если можно заменить их знаком «&», как в примере выше?

7. Массивы

^[24]Для создания массива или матрицы вам потребуется несколько операций, но они сложнее, чем в случае с обычными формулами, ведь для отображения результата требуется не одна, а несколько ячеек.

^{[25] [26]}Например, давайте перемножим две эти матрицы. Для этого используем функцию МУМНОЖ(Массив 1, Массив 2). Главное, не забудьте закрыть формулу круглой скобкой. Теперь нажмите сочетание клавиш Ctrl+Shift+Enter, и Excel покажет результат умножения в виде матрицы. То же самое касается и других функций, работающих с массивами – вместо простого нажатия Enter для получения результата используйте Ctrl+Shift+Enter.

8. Подбор параметра

^[27]

Без этой функции Excel целым легионам аналитиков, консультантов и прогнозистов  пришлось бы туго. Спросите кого угодно из сферы консалтинга или продаж, и вам расскажут, насколько полезной бывает эта возможность Excel.

Например, вы занимаетесь продажами новой видеоигры, и вам нужно узнать, сколько экземпляров ваши менеджеры должны продать в третьем месяце, чтобы заработать 100 миллионов долларов.

^{[28] [29] [30]}Для этого в меню «Инструменты» выберите функцию «Подбор параметра». Нам нужно, чтобы в ячейке Total revenue (Общая выручка) оказалось значение 100 миллионов долларов. В поле set cell (Установить в ячейке) указываем ячейку, в которой будет итоговая сумма, в поле to value (Значение) – желаемую сумму, а в by Changing cell (Изменяя значение ячейки) выберите ячейку, где будет отображаться количество проданных в третьем месяце товаров. И – вуаля! – программа справилась. Параметрами и значениями в ячейках можно легко манипулировать, чтобы получить нужный вам результат.

9. Сводные таблицы

^[31]Если вам нужно провести макроанализ и увидеть общую картину, а на руках у вас только бесконечные колонки цифр, то сводная таблица станет отличным способом оптимизировать и ускорить вашу работу.

^{[32] [33] [34] [35]}Для этого выберете в меню «Вставка» пункт «Сводная таблица». Введя нужные диапазоны, вы получите данные в виде удобной для восприятия таблицы. Вы легко можете изменять параметры отображения данных с помощью конструктора сводных таблиц, сравнивать и анализировать полученные числа и прочее, прочее.

Сводная таблица – прекрасный способ превратить огромный бесполезный объём информации в удобные для работы данные в рекордно короткие сроки.

Примечание: название функций и операций на русском языке указано в соответствии с русифицированной версией Microsoft Excel 2010.