Excel для продвинутых: 9 полезных трюков
Кроме множества базовых приёмов работы с этой программой, которые Вы наверняка уже знаете, полезно изучить некоторые хитрости «для продвинутых», которые сделают Вас на голову выше всех остальных. Так что, если хотите впечатлить своих боссов и разгромить конкурентов, Вам пригодятся эти 8 хитрых функций 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. Сводные таблицы
Если Вам нужно провести макроанализ и увидеть общую картину, а на руках у Вас только бесконечные колонки цифр, то сводная таблица станет отличным способом оптимизировать и ускорить работу. Для этого выберите в меню «Вставка» пункт «Сводная таблица». Введя нужные диапазоны, Вы получите данные в виде удобной для восприятия таблицы. Вы легко можете изменять параметры отображения данных с помощью конструктора сводных таблиц, сравнивать и анализировать полученные числа и т.д.
Сводная таблица – прекрасный способ превратить огромный бесполезный объём информации в удобные для работы данные в рекордно короткие сроки.
уроки по обучению Эксель онлайн
Название: «Excel с 0 до PRO»
Тип: обучение на онлайн-платформе Skillbox
Длительность: 4 месяца, 20 видеоуроков
Домашние задания: есть
Автор: Олег Самойленко
Курс по Экселю для новичков и продвинутых подходит:
Как проходит обучение Excel
Изучать видеоуроки по Excel можно с компьютера, планшета и смартфона.
Вам предстоит пройти 20 видеоуроков с теорией по работе с функциями в Excel. Уроки имеют небольшую длительность, в которую интересно и понятно вместилась информация. Обучайтесь всего один день в неделю.
После каждого урока вам надо выполнить домашнее задание и отправить на проверку преподавателю. В качестве заданий могут быть представлены тесты, задачи и билеты для отработки. Домашняя работа не займет больше 2 часов в неделю.
На каждое выполненное домашнее задание вы получаете комментарий от преподавателя. Он укажет ошибки и способы их исправления. Также вы можете обмениваться опытом с другими студентами курса по Эксель онлайн.
В домашней работе вы встретитесь с практическими задачами, которые возникают в профессиональной деятельности. По окончанию курса вы получите список must-read-литературы для дальнейшего развития.
Полноценная поддержка преподавателя на протяжении всего обучения Ecxel.
Видеокурс «Excel Базовый» научит вас:
1. Основам работы в Excel
Досконало изучите интерфейс программы и используйте «горячие клавиши» для быстрой работы.
MS Excel 2013/2016: продвинутый курс
Ищете качественный самоучитель по работе в Microsoft Excel?
Специально для тех, кто знает основы работы в Microsoft Excel, но хочет детальней разобраться в этой программе, skill.im подготовила курс по работе в этой программе.
Зачем он нужен? Затем, что он позволит вам стать уверенным пользователем и применять навыки при выполнении различных задач в кратчайшие сроки. Пройдите его и никто больше не назовет вас чайником. А самое главное – это то, что учиться со skill.im не только полезно, но и интересно.
Что входит в курс по MS Excel?
Второй этап нашего самоучителя посвящен более сложным темам, поэтому, если вы еще никогда не пользовались экселем, то мы рекомендуем начать с базового курса.
Так вот, в этот этап входит семь разделов.
Первый раздел курса посвящен сложному форматированию:
• темы в Excel
• Добавлению примечаний
• Созданию списков быстрого заполнения
Во втором разделе мы поговорим о защите данных:
• Как защитить данные на листе
• Как частично защитить данные
• пароль
Третий целиком и полностью посвящен графическим объектам:
• Добавлению картинок в Excel
• Объектам типа «SmartArt»
• Вставке фигур в Excel
• Группировке объектов в Excel
• Использованию объектов совместно с математическими формулами
В четвертом пункте самоучителя мы затронем такую важную тему, как диаграммы в Excel:
• Что такое рекомендуемые диаграммы в Excel
• Несколько способов создания диаграмм в Excel
• Изменения типа диаграмм
• Форматирования элементов диаграммы
• Добавления линий тренда
• Спарклайны в Excel
Пятый пункт самоучителя посвящен теме «Excel в качестве базы данных». Из него вы узнаете о:
• Фиксации строк и столбцов в Excel
• Фиксации строк и столбцов при печати
• Сортировке данных в Excel
• Фильтрах для отбора данных
• Удалении дублирующихся записей в Excel
• Получении данных с веб-страницы.
В шестом разделе самоучителя речь пойдут о более сложных формулах:
• Функция ЕСЛИ
• ЕСЛИ для работы с ошибками
• Функция ЕСЛИОШИБКА
• Вложенные функции ЕСЛИ
• оператор И совместно с ЕСЛИ
• РАЗНДАТ – спрятанная функция
• Функция ТЕКСТ для форматирования чисел и дат
• Функции для работы с датами
• Мгновенное заполнение
• Определение ранга в множестве значений
• Функции СЧЕТЕСЛИ, СРЗНАЧЕСЛИ и СУММЕСЛИ
Последний раздел этого самоучителя мы отвели под условное форматирование:
• Форматирование ячейки в зависимости от ее содержимого в Excel
• Вставка небольших диаграмм и цветовых индикаторов
• Создание правил форматирования с нуля
• Управление правилами форматирования.
Помимо всего, мы хотим добавить, что каждый урок в самоучителе происходит на конкретных, жизненных примерах. Это помогает вам не только освоить полученное знание, но и увидеть затем, как оно применяется на практике при работе с таблицами.
Почему именно самоучитель от skill.im?
Дело в том, что в наш самоучитель по Excel входят не только видеоуроки, но еще и тесты, и симуляции.
Тесты позволяют вам закрепить полученные навыки. А симуляции – применять их на практике, при выполнение различных ситуативных заданий.
Вместе с уроками, они делают наш самоучитель максимально разнообразным и интересным. Так что, станьте экспертами по работе в Excel прямо сейчас.
Курсы Excel Advanced: продвинутые инструменты эксель — онлайн и оффлайн занятия
ВАЖНО!!! Комфортное прохождение курса возможно, если Вы УЖЕ являетесь уверенным пользователем MS EXCEL.
Пройдите небольшой чек-лист ниже для понимания готовы ли Вы к прохождению курса (автоматически подразумевается, что включенные в чек-лист темы
КУРС ПОДОЙДЕТ, ЕСЛИ У ВАС УЖЕ ЕСТЬ НАВЫКИ:
Работа с формулами и функциями рабочих листов Excel:
- понимаете как работают формулы, их смысл, абсолютная и относительная адресации ячеек, «многоэтажные» формулы Вас не смущают: можете как сконструировать, так и разобраться
- активно используете в формулах основные категории функций и основные функции (текстовые, дата-время, логические, ссылок и массивов и др.)
Использование инструментов «проверка данных» (data validation) и «условное форматирование» (conditional formatting):
- умеете накладывать на ячейки разного типа условия ограничения ввода данных, создавать выпадающие списки
- используете встроенные группы правил и типы правил условного форматирования, особенности наложения нескольких правил
- имеете представление об использовании логических формул в «проверке данных» и «условном форматировании»
Сводные таблицы и сводные диаграммы:
- хорошо понимаете концепцию сводных таблиц и диаграмм (куб данных, поля-значения (факты), поля-измерения (атрибуты))
- можете создавать новые показатели (меры) используя «дополнительные вычисления» (show value as), «вычисляемые поля» (calculated field) и «вычисляемые элементы» (calculated item). Понимаете где и как работают формулы вычислений этих инструментов
- умеете (и знаете для чего) создавать новые измерения (инструмент «группировка»), использовать срезы и временные шкалы
Визуализация:
- используете разные типы и подтипы диаграмм, понимаете отличия (например, «с группировкой» (clustered), «с накоплением» (stacked), «нормированные с накоплением» (100% stacked)), имеете представление — для каких данных какая визуализация подходит (например, особенности точечных (scatter) и пузырьковых (bubble) диаграмм, для какого типа анализа нужны)
- знаете как работать с первичной (primary) и вторичной (secondary) осями, комбинированными (combo) диаграммами, настройками элементов диаграмм
- умеете настроить дизайн (работа с заливкой, линиями, форматами фигур, 3D диаграммами)
Если, все же, какими-либо из вышеперечисленных навыков Вы не обладаете, рекомендуем сначала пройти курс «MS EXCEL: Бизнес-анализ и прогнозирование»
НА СТРАНИЦУ КУРСА «MS EXCEL: БИЗНЕС-АНАЛИЗ И ПРОГНОЗИРОВАНИЕ»
Продвинутый курс Excel — Сводные таблицы и расчеты
1.5. Доходы
- Встаем на ячейку «I2» (Доходы), заходим в мастер функций и выбираем функцию «СУММЕСЛИМН»
- Для поля «Диапазон суммирования» указываем на листе «Доходы» столбец «F» (Total Value)
- Для поля «Диапазон условия1» указываем на листе «Доходы» столбец «С» (Campaign)
- Для поля «Условие 1» указываем столбец «В» таблица с расходами по рекламной кампании Гугл
- Для поля «Диапазон условия2» указываем на листе «Доходы» столбец «А» (Source / Medium)
- Для поля «Условие 2» указываем значение «google/ cpc». По этому значения мы выберем данные по расходам на рекламу в Google Adwords.Обязательно указывать текстовое значение в кавычках.
Таблица «Доходы» содержит информацию о расходах по нескольким рекламным кампаниям с детализацией до уровня дня. Для того, что бы получить данные о расходах каждой рекламной кампании Google Adwords воспользуемся функцией «СУММЕСЛИ».
- Нажимаем «ОК» и протягиваем до конца таблицы.
1.6. Считаем CPO (расходы/количество показов)
- Вводим в ячейке J3 формулу: =h4/E3, протягиваем до конца таблицы
В данном случае так же имеет место быть ошибка, поскольку в качестве делителя используются нулевые значения.
Некоторые ячейки содержат значения «#ДЕЛ/0!». Эта ошибка возникает при делении на нуль, поскольку многие значения расходов нулевые.
Воспользуемся функцией «ЕСЛИОШИБКА» и уберем не числовые значения
Напомним, что эта функция имеет следующий вид: =ЕСЛИОШИБКА(Значение; Значение если ошибка). В качестве значения нужно указать введенную нашу функцию «ВПР». Именно значения этой функции мы будем проверять на наличие ошибок.Таким образом вид измененной формулы: =ЕСЛИОШИБКА(Н3/Е3; 0 ).
Протягиваем вниз и получаем диапазон числовых значений:
Microsoft Excel 2016/2013. Продвинутый уровень
Описание образовательной программы
Вы получите знания и навыки, которые позволят Вам эффективно работать с большими объемами данных, производить их сложный анализ, эффективно использовать встроенные функции MS Excel 2016/2013 и макросы.
Вы научитесь сравнивать таблицы, сможете использовать функцию ВПР для подстановки данных из одной таблицы в другую, осуществлять различные вычисления как с простыми, так и сложными условиями.
В курсе будет рассмотрен как мощный функционал версий Microsoft Excel 2016/2013, так и новые возможности MS Excel 2016, которые появились при работе со сводными таблицами.
Цель курса
По окончании курса Вы будете уметь:
- наиболее эффективно применять инструменты и функции Excel для анализа и обработки данных
- использовать функции для сравнения и подстановки данных
- выполнять вычисления с применением различных условий
- выделять нужные данные условным форматированием
- защищать ячейки, рабочие листы и книги
- контролировать ввод данных
- вычислять промежуточные и общие итоги в таблицах
- сортировать и фильтровать данные по значениям и по цвету
- быстро и наглядно создавать отчеты сводных таблиц и диаграмм, чтобы подводить итоги и анализировать данные по годам, кварталам, месяцам причем как в абсолютных, так и относительных величинах
Целевая аудитория
Курс будет интересен для менеджеров и офисных работников всех уровней, руководителей, секретарей, помощников руководителей, бухгалтеров, экономистов, аналитиков, логистов и многих других специалистов, которые владеют уверенными знаниями Microsoft Excel уровня 1 и хотели бы расширить свои знания для выполнения наиболее часто используемых задач в программе Microsoft Excel 2016/2013.
Необходимая подготовка
Желательно прохождение курса Microsoft Excel 2016/2013. Базовый уровень
Содержание
1. Применение встроенных функций Excel
- различные типы ссылок
- связывание листов и рабочих книг
- применение различных типов встроенных функций
- математические функции: суммирование с условиями, округление результатов вычислений
- статистические функции: вычисление количества и средних значений с условиями
- функции ссылок и подстановки: ВПР и ГПР
- логические функции: ЕСЛИ, И, ИЛИ, ЕСЛИОШИБКА
- текстовые функции: объединение и разбиение данных
- функции для работы с датами
2. Условное форматирование
- применение встроенных правил: гистограмма, цветовые шкалы, наборы значков
- создание правил форматирования с применением формул
- редактирование правил
3. Особенности совместной работы
- защита ячеек, листов и рабочих книг Excel
- проверка вводимых значений:
- установка ограничений на ввод данных
- поиск неверных значений
4. Работа с большими табличными массивами
- создание и ведение таблиц
- удаление дубликатов
- сортировка данных:
- сортировка по одному критерию
- многоуровневая сортировка
- сортировка по форматированию
- фильтрация данных:
- автофильтр
- срезы
- расширенный фильтр
- подведение промежуточных итогов
- консолидация данных
5. Анализ данных с помощью Сводных таблиц
- создание сводных таблиц
- преобразование сводных таблиц
- фильтрация данных: фильтры, срезы, временная шкала
- настройка полей сводной таблицы
- добавление вычисляемых полей в сводную таблицу
- группировка полей в сводных таблицах
- сводные диаграммы
- обновление сводных таблиц и диаграмм
Получаемый документ
Практическое освоение Excel для продвинутых пользователей – курс
Модуль 1. Функции Excel
- различные типы ссылок
- связывание листов и рабочих книг
- применение различных типов встроенных функций
- математические функции: суммирование с условиями, округление результатов вычислений
- статистические функции: вычисление количества и средних значений с условиями
- функции ссылок и подстановки: ВПР и ГПР
- логические функции: ЕСЛИ, И, ИЛИ, ЕСЛИОШИБКА
- текстовые функции: объединение и разбиение данных
- функции для работы с датами
Модуль 2. Форматирование и Особенности совместной работы
- условное форматирование
- применение встроенных правил: гистограмма, цветовые шкалы, наборы значков
- создание правил форматирования с применением формул
- редактирование правил
- защита ячеек, листов и рабочих книг Excel
- проверка вводимых значений:
- установка ограничений на ввод данных
- поиск неверных значений
Модуль 3. Работа с большими табличными массивами
- создание и ведение таблиц
- удаление дубликатов
- сортировка данных:
- сортировка по одному критерию
- многоуровневая сортировка
- сортировка по форматированию
- фильтрация данных:
- автофильтр
- расширенный фильтр
- подведение промежуточных итогов
- консолидация данных
- элементы управления: списки, счетчики, флажки, переключатели
- работа с массивами данных
- создание формул с массивами данных, особенности редактирования формул
Модуль 4. Сводные таблицы
- создание сводных таблиц
- преобразование сводных таблиц
- фильтрация данных: фильтры, срезы, временная шкала
- настройка полей сводной таблицы
- добавление вычисляемых полей в сводную таблицу
- группировка полей в сводных таблицах
- сводные диаграммы
- обновление сводных таблиц и диаграмм
Модуль 5. Введение в макросы Excel
- запись макроса макрорекордером
- создание кнопок для запуска своих макросов
- обзор редактора Visual Basic for Application
- редактирование записанных макросов
- создание макросов путем написания кода
Модуль 6. Диаграммы и Графические обьекты Excel
- добавление картинок
- фигуры, SmartArt
- форматирование графических обьектов
- рекомендуемые диаграммы
- создание и форматирование диаграмм