Excel для продвинутых: 9 полезных трюков
Excel для продвинутых: 9 полезных трюков
Microsoft Excel, пожалуй, лучшая офисная программа из когда-либо созданных. На ней держатся целые отрасли экономики, так что, виртуозно овладев этим инструментом, Вы сразу заметите, что дела у Вашего бизнеса идут в гору.
Кроме множества базовых приёмов работы с этой программой, которые Вы наверняка уже знаете, полезно изучить некоторые хитрости «для продвинутых», которые сделают Вас на голову выше всех остальных. Так что, если хотите впечатлить своих боссов и разгромить конкурентов, Вам пригодятся эти 9 хитрых функций Excel.
1. Функция ВПР
Эта функция позволяет быстро найти нужное Вам значение в таблице. Например, нам нужно узнать финальный балл Бетт, мы пишем: =ВПР(“Beth”,A2:E6,5,0), где Beth – имя ученика, A2:E6 – диапазон таблицы, 5 – номер столбца, а 0 означает, что мы не ищем точного соответствия значению.
Функция очень удобна, однако нужно знать некоторые особенности её использования. Во-первых, ВПР ищет только слева направо, так что, если Вам понадобится искать в другом порядке, придется менять параметры сортировки целого листа. Также если Вы выберете слишком большую таблицу, поиск может занять много времени.
2. Функция ИНДЕКС
Отражает значение или ссылку на ячейку на пересечении конкретных строки и столбца в выбранном диапазоне ячеек. Например, чтобы посмотреть, кто стал четвёртым в списке самых высокооплачиваемых топ-менеджеров Уолл-стрит, набираем: =ИНДЕКС(А3:А11, 4).
3. Функция ПОИСКПОЗ
Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне (Диапазон - две или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными) ячеек и отражает относительную позицию этого элемента в диапазоне.
По отдельности ИНДЕКС и ПОИСКПОЗ не особо полезны. Но вместе они могут заменить функцию ВПР.
Например, чтобы в большой таблице найти, кто является главой Wells Fargo, пишем =ИНДЕКС(А3:А11,ПОИСКПОЗ(«Wells Fargo»,B3:B11,0).
С помощью функции ВПР этого не сделать, потому что она ищет только слева направо. А сочетание двух последних позволяет сделать это с легкостью.
4. 3D-сумма
Допустим, Вы решили подсчитать, сколько денег Вы тратите каждый день, и вести учёт в течение семи недель. Под каждую неделю Вы создали отдельный лист, куда по дням заносите расходы на алкоголь, еду и прочие мелочи.
Теперь на вкладке TOTAL (ИТОГО) нам нужно увидеть, сколько и в какой день Вы потратили за этот период. Набираем =СУММ(‘Week1:Week7’!B2), и формула суммирует все значения в ячейке B2 на всех вкладках. Теперь, заполнив все ячейки, мы выяснили, в какой день недели тратили больше всего, а также в итоге подбили все свои расходы за эти 7 недель.
5. $
Одна из самых удобных функций в арсенале Excel, а также одна из самых простых – это знак $. Он указывает программе, что не нужно делать автоматической корректировки формулы при её копировании в новую ячейку, как Excel поступает обычно.
При этом знак $ перед «А» не даёт программе изменять формулу по горизонтали, а перед «1» – по вертикали. Если же написать «$A$1», то значение скопированной ячейки будет одинаковым в любом направлении. Очень удобный приём, когда приходится работать с большими базами данных.
6. &
Если Вы хотите собрать все значения из разных ячеек в одну, Вы можете использовать функцию СЦЕПИТЬ. Но зачем набирать столько букв, если можно заменить их знаком «&».
7. Массивы
Для создания массива или матрицы Вам потребуется несколько операций, но они сложнее, чем в случае с обычными формулами, ведь для отображения результата требуется не одна, а несколько ячеек.
Например, давайте перемножим две матрицы. Для этого используем функцию МУМНОЖ (Массив 1, Массив 2). Главное, не забудьте закрыть формулу круглой скобкой. Теперь нажмите сочетание клавиш Ctrl+Shift+Enter, и Excel покажет результат умножения в виде матрицы. То же самое касается и других функций, работающих с массивами, – вместо простого нажатия Enter для получения результата используйте Ctrl+Shift+Enter.
8. Подбор параметра
Без этой функции Excel целым легионам аналитиков, консультантов и прогнозистов пришлось бы туго. Спросите кого угодно из сферы консалтинга или продаж, и Вам расскажут, насколько полезной бывает эта возможность Excel.
Например, Вы занимаетесь продажами новой видеоигры, и Вам нужно узнать, сколько экземпляров Ваши менеджеры должны продать в третьем месяце, чтобы заработать 100 миллионов долларов. Для этого в меню «Инструменты» выберите функцию «Подбор параметра». Нам нужно, чтобы в ячейке Total revenue (Общая выручка) оказалось значение 100 миллионов долларов. В поле set cell (Установить в ячейке) указываем ячейку, в которой будет итоговая сумма, в поле to value (Значение) – желаемую сумму, а в by Changing cell (Изменяя значение ячейки) выберите ячейку, где будет отображаться количество проданных в третьем месяце товаров. И – вуаля! – программа справилась. Параметрами и значениями в ячейках можно легко манипулировать, чтобы получить нужный Вам результат.
9. Сводные таблицы
Если Вам нужно провести макроанализ и увидеть общую картину, а на руках у Вас только бесконечные колонки цифр, то сводная таблица станет отличным способом оптимизировать и ускорить работу. Для этого выберите в меню «Вставка» пункт «Сводная таблица». Введя нужные диапазоны, Вы получите данные в виде удобной для восприятия таблицы. Вы легко можете изменять параметры отображения данных с помощью конструктора сводных таблиц, сравнивать и анализировать полученные числа и т.д.
Сводная таблица – прекрасный способ превратить огромный бесполезный объём информации в удобные для работы данные в рекордно короткие сроки.
Примечание: название функций и операций на русском языке указано в соответствии с русифицированной версией Microsoft Excel 2010.
ПРОСТЫЕ РАСЧЕТЫ В MICROSOFT EXCEL
Excel, сложение, вычитание, как создать таблицу, вставить формулу, копирование формул
Как хорошо уметь … считать. Данная фраза особенно актуальна, если вы имеете дело с цифрами и часто работаете в Microsoft Excel. Это довольно удобная программа не только для сложных финансовых расчетов, но и для ведения домашней бухгалтерии. Благодаря данной статье вы научитесь складывать, вычитать, умножать и делить в Microsoft Excel.
Прежде всего, создадим плацдарм для работы – простенькие таблички, например, по теме домашней бухгалтерии. Для этого открываем Microsoft Excel и печатаем шаблоны таблиц: лист 1 назовем «Приход», лист 2 – «Расход», лист 3 – «Прибыль». Первые две таблицы заполняем числовыми значениями, на основании которых будут вычисляться значения в третьей табличке. В результате у вас должно получиться примерно следующее:
Лист «Приход»
Лист «Расход»
Лист «Прибыль»
Сложение
Теперь перед нами стоит задача – посчитать итого в первых двух табличках. Для этого необходимо сложить числовые значения по вертикали (подсчет итого по всем членам семьи за месяц) и по горизонтали (подсчет итого по одному члену семьи за 12 месяцев). Рассмотрим несколько способов сложения в Microsoft Excel.
Способ №1.
Выделим ячейку В6 и в меню «Формулы» выберем «Автосумма».
В выпадающем списке выберем пункт «Сумма».
Вы увидите как Excel выделил подлежащие суммированию ячейки пунктирной линией, а в строке формул и ячейке В6 отобразилась формула: =СУММ(В2:В5)
Расшифровывается данная запись довольно просто:
«=» — знак равенства означает, что в выделенной ячейке будет находиться результат какого-либо математического действия, в нашем случае результат суммы чисел.
«СУММ» – сумма, название математического действия (функции), результат которого будет выведен в ячейке.
«(В2:В5)»– диапазон ячеек, содержимое которых будет суммироваться. Диапазоны расположенных подряд ячеек всегда записываются через двоеточие: первая ячейка – начало диапазона, вторая – его конец.
Microsoft Excel практически всю работу сделал за нас. Осталось проверить правильность выделенных программой ячеек и нажать на клавиатуре Enter. Итоговая сумма за январь подсчитана.
Кстати, вместо Enter можно нажать кнопочку «Ввод» в строке формул. А чтобы отменить расчет суммы достаточно щелкнуть в этой же строке кнопочку «Отмена».
Сложение с применением диапазонов хорошо тем, что вы можете вставлять между начальной и конечной ячейками диапазона любое количество строк или столбцов. Итоговая сумма автоматически будет пересчитана, а сам диапазон автоматически увеличится на добавленное количество строк (столбцов). Для наглядности добавим в нашу домашнюю бухгалтерию Дедушку. Мы воочию можем наблюдать, как изменилось итого за январь. Добавленная строка автоматически вписалась в диапазон ячеек, участвующих в расчете суммы.
Способ №2.
Для сложения чисел в Microsoft Excel не обязательно постоянно открывать меню «Формулы», данное математическое действие можно выбрать и непосредственно из строки формул. Для этого также выделим ячейку, в которой должен отобразиться результат сложения (например, Е6), и щелкнем по кнопочке «fx» – «Вставить функцию»
Запустится Мастер функций. Поскольку мы уже применяли функцию СУММ, то она будет находиться в списке 10-ти недавно использовавшихся.
В остальных случаях необходимо выбрать категорию «Математические» и в списке функций отметить «СУММ». Ниже вы также сможете прочитать описание данной функции.
После нажатия кнопочки «ОК» вы попадете в окно выбора ячеек для суммирования.
Здесь указан диапазон ячеек нашей таблицы, содержащиеся в них числа, и подсчитана предварительная сумма. Также в этом окошке имеется возможность добавить к суммируемым аргументам новые ячейки. Для этого нужно нажать кнопочку с красной стрелкой в поле «Число 2» и выделить мышью нужный диапазон ячеек на листе Excel. Данная возможность используется, когда при помощи Мастера функций вы суммируете вразброс идущие ячейки. Чтобы подсчитать сумму нажимаем «ОК» и любуемся полученным результатом. Конечно, этот способ более длинный, чем предыдущий, однако он тоже имеет право на существование.
Способ №3.
Следующий способ сложения в Microsoft Excel подходит для любителей щелкать мышкой, поскольку никакие меню и мастера функций в нем не используются. Данный способ удобен не только для сложения, но и для вычитания, умножения и деления. Итак, выделим мышкой ячейку, в которую будет записан результат суммы. В нашем примере это F6. На клавиатуре нажмем знак равенства (=) и щелкнем ячейку с первой цифрой, которую мы хотим сложить (F2). Далее нажмем на клавиатуре плюсик (+) и щелкнем следующую ячейку (F3), потом опять плюсик и снова щелкаем ячейку и так далее до F5. Получится нечто вроде математического примера, который во всей свой красе отобразится в строке формул.
Для вывода результата суммы нажмем на клавиатуре кнопку Enter либо кнопочку «Ввод» в строке формул.
Теперь вы можете посчитать оставшиеся итого по месяцам самостоятельно при помощи любого из вышеприведенных способов.
У нас получились следующие цифры.
Способ №4.
С месяцами мы разобрались. Теперь рассчитаем итого по членам семьи – кто сколько денег принес за год. Тут хорошо бы воспользоваться первым способом суммирования, но… дойдя до Сына, Microsoft Excel почему-то не захотел правильно выделять диапазон подлежащих суммированию ячеек и решил сложить Папу с Мамой, что нас совершенно не устраивает.
Получив такую картину, не принимайтесь щелкать мышкой и отменять ввод формулы, а всего лишь укажите программе правильный диапазон ячеек: с В4 по М4. Для этого наведите мышь на ячейку В4 и нажмите левую кнопку мышки. Не отпуская её, двигайте мышкой вправо, пока рамка выделения не захватит ячейку М4. После этого можете отпускать левую кнопку мыши и жать Enter.Сумма ячеек посчитается правильно.
Способ №5.
Чтобы стать профессиональными Excel-математиками изучим еще один способ сложения чисел в Microsoft Excel. Если раньше при исчислении суммы мы пользовались меню, кнопками и мышкой, то теперь будем вводить все действия и функции вручную в строке формул.
Как обычно выделяем ячейку, в которую будет записан результат суммы (у нас это N5) и щелкаем левой кнопкой мыши в строке формул. Здесь печатаем нашу формулу: =B5+C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5
Т.е. мы по порядку перечисляем все ячейки, содержимое которых должно суммироваться.
Вместо этого устрашающего набора цифр и букв можно ввести знакомую нам функцию: =СУММ (В5:М5)
Результат будет одним и тем же. Если вам нравится работать со строкой формул и у вас хорошая память на названия функций, этот способ для вас. При должном уровне тренировки вы будете производить расчеты довольно быстро.
Таким образом, мы посчитали доходы семьи за год. Расходы семьи предлагаю посчитать самостоятельно любыми из понравившихся вам способов. Приведу лишь результаты, которые у нас получились.
Вычитание
Сложение мы освоили. Теперь перейдем к вычитанию. Нам предстоит рассчитать прибыль, полученную семьей за год. Прибыль рассчитывается следующим образом: приход минус расход. Например, чтобы получить прибыль Мамы за январь нам необходимо из доходов Мамы за январь вычесть расходы Мамы за январь. С этой целью откроем лист «Прибыль». Выделим ячейку В2 и наберем на клавиатуре знак равенства (=). Теперь перейдем на лист «Приход» и щелкнем мышкой ячейку В2. Далее на клавиатуре щелкнем кнопку минус (-). Теперь перейдем на лист «Расход» и щелкнем ячейку В2. Нажимаем на клавиатуре Enter и, вауля, прибыль подсчитана. А рассчиталась наша прибыль по формуле, которую мы создали всеми этими щелчками мышкой и набором на клавиатуре, и которую можно увидеть в строке формул на листе «Прибыль».
Расшифруем полученную формулу. Со знаком равенства вы уже знакомы, он говорит нам, что в выделенной ячейке появится результат какого-либо вычисления. Слова «Приход!» и «Расход!» (обязательно с восклицательным знаком) обозначают имена листов в текущей книге Microsoft Excel.
В2 – ячейка таблицы. Вкупе запись «Приход!В2» расшифровывается как «ячейка В2, принадлежащая листу Приход». Соответственно запись «Расход!В2» читается как «ячейка В2 на листе Расход». Поэтому одинаковые имена ячеек (В2) не должны вводить вас в заблуждение, т.к. эти ячейки находятся на разных листах Excel.
И, наконец, знак минус между «Приход!В2» и «Расход!В2» говорит о том, что мы вычитаем из значения, находящиеся в ячейке В2 листа Приход, число, содержащиеся в ячейке В2 листа Расход.
Вот, вы и сами не заметили, как научились не просто вычитать в Microsoft Excel, а считать разность двух чисел с разных листов одной книги. Схема работы при вычислении разности (а также суммы, умножении и делении) чисел из разных книг Excel абсолютно такая же, только вам придется перемещаться между двумя книгами. При этом обе книги должны быть одновременно открыты. С числами, находящимися на одном листе, все ещё проще. В такой формуле будут содержаться знаки равенства и вычитания, а также имена ячеек. Например,
=В2-В3
Теперь самостоятельно посчитайте прибыль Папы, Сына и Дочери за январь. У вас должно получиться следующее:
Копирование формул
Однако у нас осталась не подсчитанной прибыль для остальных месяцев. И что же, нам так и щелкать туда-сюда по листам, скажете вы. Конечно, нет. В Microsoft Excel есть замечательная возможность скопировать формулу во все необходимые ячейки.
Для этого выделим ячейку с формулой на листе «Прибыль». В нашем случае это ячейка В2. Подведем указатель мышки к правому нижнему углу клетки. Когда покажется маленький черный крестик, нажимаем левую кнопку мышки и, не отпуская её, двигаем мышь вправо, захватывая все ячейки, в которые необходимо скопировать формулу.
Заметьте, что имена ячеек в скопированных формулах изменились автоматически.
Теперь скопируйте формулы для оставшихся месяцев и получите прибыль Папы, Сына и Дочери. Имейте в виду, что вначале необходимо выделить соответствующую ячейку, например В3 для Папы или В5 для Дочери, а потом уже копировать формулу. Итого можете подсчитать, суммируя полученные значения любим из рассмотренных ранее способов. В результате у нас получились следующие значения:
Интересно, что при изменении числовых значений в ячейках, задействованных в формуле, итоговое значение также изменится. Для примера, увеличим доход Дочери за январь с 40 до 100. При этом её прибыль должна увеличиться с 30 до 90. Вводим в ячейку В5 на листе «Приход» цифру 100 и на листе «Прибыль» автоматически получаем ожидаемые 90. Всё потому, что ячейка В5 участвует в формуле «=Приход!B5-Расход!B5» и как бы ни изменялось содержащиеся в ней число, итог формулы будет пересчитан соответственно данным изменениям.
Умножение и деление
Напоследок разберем еще два простых математических действия. Принципы, лежащие в основе умножения и деления, ни чем не отличаются от рассмотренных ранее. За умножение отвечает знак * на клавиатуре, за деление – знак / Для примера создадим табличку с зарплатами и сделаем перерасчет оклада с учетом некоего коэффициента. Наша задача – умножить сумму оклада работника на присвоенный ему коэффициент. В результате мы получим итоговую заработную плату для каждого сотрудника.
Выделим мышкой ячейку D2 и поставим в ней знак равенства. Затем щелкнем ячейку В2, поставим знак умножения (клавиша *) и щелкнем ячейку С2. Нажмем на клавиатуре Enter. В результате мы получим следующую формулу:
=B2*C2
Пользуясь данным методом, предлагаю вам самостоятельно рассчитать заработную плату остальных работников. У вас должны получиться следующие цифры:
Заметьте, что при изменении любой из величин (оклада или коэффициента) итоговая заработная плата сотрудника будет автоматически пересчитана. Теперь займемся делением. Добавим в нашу табличку еще одну ячейку и разделим итоговую заработную плату каждого работника на 30, чтобы узнать доход за день.
Чтобы посчитать сколько денег товарищ Иванов имеет в день выделим ячейку Е2 и введем знак равенства. Затем щелкнем ячейку D2 и в строке формул напечатаем /30. В строке формул должна появиться следующая запись: = D2/30 Завершим расчет нажатием Enter на клавиатуре.
То же самое проделаем для остальных работников, не забывая выделять соответствующие конкретно им ячейки.
В результате мы получили следующую табличку.
 Вместо 30 можно вводить имя ячейки, в которой содержится число – делитель. Например, В2. Тогда формула будет иметь вид:
= D2/В2
При этом значение, находящееся в ячейке D2, будет разделено на число из ячейки В2.Напомню, что при изменении любого из чисел, участвующих в формуле, например оклада или коэффициента, расчетное значение, например дневной заработок работника, также будет пересчитано.
Итак, мы с вами научились не только азам бухгалтерии, но и основным математическим операциям в Microsoft Excel. Применение того или иного способа расчетов будет зависеть от ваших предпочтений и стоящей перед вами задачи.
Специально для проекта Ячайник, Елена Карлтон
|
