Microsoft excel продвинутый уровень. Excel для продвинутых

17.09.2014

Знание основных функций Microsoft Excel - от сводных таблиц до Power View - поможет вам влиться в ряды специалистов по электронным таблицам.

ANTHONY DOMANICO. 11 tricks for Excel power users. PCWorld , май 2014.

Знание этих функций - от сводных таблиц до Power View - поможет вам влиться в ряды специалистов по электронным таблицам.

Пользователи Microsoft Excel делятся на две категории: представителям первой кое-как удается справляться с маленькими табличками, а те, кто относятся ко второй, поражают коллег сложными диаграммами, мощным анализом данных и волшебством эффективного применения формул и макросов. Одиннадцать приемов, которые мы рассмотрим в этой статье, помогут вам стать полноправным членом второй группы.

ВПР

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

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

Вставьте в формулу функцию ВПР, указав в первом ее аргументе искомое значение, по которому осуществляется связь (1). Во втором аргументе задайте диапазон ячеек, в которых следует производить выборку (2), в третьем - номер столбца, из которого будут подставляться данные, а в четвертом введите значение ЛОЖЬ, если хотите найти точное соответствие, или ИСТИНА, если нужен ближайший приблизительный вариант (4).

Создание диаграмм

Для создания диаграммы введите в Excel данные с указанием заголовков столбцов (1), выберите на вкладке «Вставка» пункт «Диаграммы» (2) и укажите требуемый тип диаграммы. В Excel 2013 имеется вкладка «Рекомендуемые диаграммы» (3), на которой присутствуют типы, соответствующие введенным вами данным. После определения общего характера диаграммы Excel открывает вкладку «Конструктор», где производится ее более точная настройка. Огромное количество наличиствующих здесь параметров позволяет придать диаграмме тот внешний вид, который вам нужен.

Функции ЕСЛИ и ЕСЛИОШИБКА

К числу наиболее популярных функций Excel относятся ЕСЛИ и ЕСЛИОШИБКА. Функция ЕСЛИ позволяет определить условную формулу, которая при выполнении условия вычисляет одно значение, а при его невыполнении - другое. Например, студентам, получившим за экзамен 80 баллов и больше (оценки выставлены в столбце C), можно присвоить признак «Сдал», а тем, кто получил 79 баллов и меньше, - признак «Не сдал».

Функция ЕСЛИОШИБКА представляет собой частный случай более общей функции ЕСЛИ. Она возвращает какое-то конкретное значение (или пустое значение), если в процессе вычисления формулы произошла ошибка. К примеру, при выполнении функции ВПР над другим листом или таблицей, функция ЕСЛИОШИБКА может возвращать пустое значение в тех случаях, когда ВПР не находит искомого параметра, задаваемого первым аргументом.

Сводная таблица

Сводная таблица, по сути, представляет собой итоговую таблицу, позволяющую подсчитывать число элементов и вычислять среднее значение, сумму и другие функции на основе определенных пользователем опорных точек. В версии Excel 2013 дополнительно появились «Рекомендуемые сводные таблицы», упрощающие создание таблиц, в которых будут отображаться нужные вам данные.

Сводная таблица - это инструмент для проведения над таблицей

различных итоговых расчетов в соответствии с выбранными

опорными точками

Например, чтобы подсчитать средний балл студентов в зависимости от их возраста, переместите поле «Возраст» в раздел Строки (1), а поля с оценками - в раздел «Значения» (2). В меню значений выберите пункт «Параметры полей значений» и в качестве операции укажите «Среднее» (3). Таким же образом можно подсчитывать итоги и по другим категориям, например, вычислять число сдавших и не сдавших экзамен в зависимости от пола.

Сводная диаграмма

Сводные диаграммы обладают чертами как сводных таблиц, так и традиционных диаграмм Excel. Сводная диаграмма позволяет легко и быстро формировать простое для восприятия визуальное представление сложных наборов данных. Сводные диаграммы поддерживают многие функции традиционных диаграмм, в том числе ряды, категории и т.д. Возможность добавления интерактивных фильтров позволяет манипулировать выбранными подмножествами данных.

В Excel 2013 появились «Рекомендуемые сводные диаграммы». Откройте вкладку «Вставка», перейдите в раздел «Диаграммы» и выберите пункт «Рекомендуемые диаграммы». Переместив указатель мыши на выбранный вариант, вы увидите, как он будет выглядеть. Для создания сводной диаграммы вручную нажмите на вкладке «Вставка» кнопку «Сводная диаграмма».

Мгновенное заполнение

Лучшая, пожалуй, новая функция Excel 2013 - «Мгновенное заполнение» - позволяет эффективно решать повседневные задачи, связанные с быстрым переносом нужных блоков информации из смежных ячеек. В прошлом, при работе со столбцом, представленным в формате «Фамилия, Имя», пользователю приходилось вручную извлекать из него имена или искать какие-то очень сложные обходные пути.

«Мгновенное заполнение» позволяет извлекать нужные

блоки информации и заполнять ими смежные ячейки

Предположим, что тот же самый столбец с фамилиями и именами есть и в Excel 2013. Достаточно ввести имя первого человека в ближайшую справа ячейку (1) и на вкладке «Главная» выбрать «Заполнить» и «Мгновенное заполнение». Excel автоматически извлечет все прочие имена и заполнит ими ячейки справа от исходных.

Быстрый анализ

Новый инструмент быстрого анализа Excel 2013 помогает ускорить создание диаграмм из простых наборов данных. После выделения данных рядом с правым нижним углом выделенной области появляется характерный значок (1). Щелкнув на нем, вы переходите в меню «Быстрого анализа» (2).

Там представлены инструменты «Форматирования», «Диаграмм», «Итогов», «Таблиц» и «Спарклайнов». Щелкая мышью на этих инструментах, вы увидите поддерживаемые ими возможности.

Power View

Интерактивный инструмент исследования и визуализации данных, Power View, предназначен для извлечения и анализа больших объемов данных из внешних источников. В Excel 2013 для вызова функции Power View перейдите на вкладку «Вставка» (1) и нажмите кнопку «Отчеты» (2).

Отчеты, созданные с помощью Power View, уже готовы к презентации и поддерживают режимы чтения и полноэкранного представления. Интерактивную их версию можно даже экспортировать в PowerPoint. Руководства по бизнес-анализу, представленные на сайте Microsoft, помогут вам в кратчайшие сроки стать специалистом в этой области.

Условное форматирование

Расширенные функции условного форматирования Excel позволяют легко и быстро выделять нужные данные. Соответствующий элемент управления находится на вкладке «Главная». Выделите диапазон ячеек, которые требуется отформатировать, и нажмите кнопку «Условное форматирование» (2). В подменю «Правила выделения ячеек» (3) перечислены условия форматирования, которые встречаются чаще всего.

Транспонирование столбцов в строки и наоборот

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

Важнейшие комбинации клавиш

Приведенные здесь восемь комбинаций клавиш особенно полезны для быстрого перемещения по электронным таблицам Excel и выполнения часто встречающихся операций.

Поверьте, чем больше вы знаете об Excel, чем лучше разбираетесь в её функциях, тем эффективнее будет ваша работа. А мы пока расскажем вам о пяти наиболее важных инструментах Excel.

1. Запрет на изменения задним числом

Итак, у вас есть таблица, где пока пустуют поля «Количество» и «Дата». Таблицу будет заполнять менеджер Василий, и вы немного сомневаетесь в его честности. Как же сделать так, чтобы Василий не смог внести в таблицу изменения задним числом?

1) Наведите курсор на ячейку «Дата» и выберите пункт «Данные».

2) Щёлкните на пункт «Проверка данных». Выскочит табличка.

3) В пункте «Тип данных» щёлкните на «Другой».

4) Найдите графу «Формула» и впишите туда: =А2=СЕГОДНЯ().

5) Найдите пункт «Игнорировать пустые ячейки» и уберите с него галочку.

6) Нажмите «ОК».

Ту же самую процедуру можно повторить со столбцом «Количество», да и в принципе - с любым другим столбцом.

2. Создание абсолютно скрытого листа

Предположим, над одним документом Excel работают сразу несколько человек. Предположим, вы хотите скрыть определённый лист. Если сделать это простым способом, то пользователь всё равно увидит имя скрытого листа. Мы же предлагаем вам супертайный вариант:

1) Наберите ALT+F11, слева выскочит вытянутое окошко.

2) В верхней части окошка укажите, который из листов вы хотите скрыть.

3) В нижней части окошка отыщите свойство Visible и переведите его в режим xlSheetVeryHidden.

Всё! Теперь об этом листе будете знать только вы.

3. Запрет на ввод дублей

Допустим, вы загружаете список продуктов в прайс-лист. Как избежать повторов? Приводим пример организации прайс-листа на десять ячеек; при желании вы можете создать любое их количество.

1) Выделите ячейки, на которые будет распространяться запрет дублей. Например, так: А1:А10.

2) Нажмите на вкладку «Данные» и выберите пункт «Проверка данных».

3) В выпадающем списке «Тип данных» из вкладки «Параметры» выберите пункт «Другой».

4) Введите =СЧЁТЕСЛИ($A$1:$A$10;A1)<=1 в графу «Формула».

5) Перейдите на вкладку «Сообщение об ошибке» и впишите туда текст, который будет выпадать при каждом дубле.

6) Нажмите «ОК».

4. Сводная таблица

Допустим, у вас принято делать таблицы, в которых расписывается, сколько всего продуктов продал определённый менеджер. Нередко бывает так, что таблица разрастается, и работать с ней становится сложно. Именно для облегчения работы с разросшимся документом и существует сервис «сводные таблицы». Как их создать?

1) Нажмите на «Вставку». Там есть пункт «Сводная таблица», нажмите на него.

2) Появится окошко. Нажмите «ОК».

3) Появится окошко, которое позволит вам сформировать сводную таблицу с теми данными, которые вас интересуют: например, самый продуктивный менеджер или самый востребованный товар.

5. Выборочное суммирование

Предположим, у вас есть таблица, в которой указано, что разные заказчики покупали у вас разные товары на разные суммы. Вы хотите узнать, на какую сумму в общей сложности клиент по имени VASILIY купил у вас буженины (Boiled Salted Pork).

Microsoft Excel, пожалуй, самая распространенная офисная программа. Ей пользуются все от президента до школьника, так что, виртуозно овладев этим инструментом, Вы смоете выделиться серди офисных работников или облегчить свой повседневный труд.

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

1. 3D-сумма

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

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

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

По отдельности ИНДЕКС и ПОИСКПОЗ не особо полезны. Но вместе они могут заменить функцию ВПР.

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

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

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

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

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

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

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

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

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

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

1. Массивы

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

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

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

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

Коллеги! Необходимость автоматизации расчетов, с учетом огромного количества исходных данных, связанных с моей профессиональной деятельностью, проявила большой интерес к изучению Excel, как программы с колоссальными возможностями. На просторах интернета много информации, онлайн-тренингов, вебинаров и учебных пособий. Скажу честно: наиболее эффективным для меня оказалось изучение курса «Продвинутый уровень MS Excel», оказавшегося доступным для понимания даже «дилетанту», лаконичного, имеющего в своем составе огромный упорядоченный материал для изучения. Отдельно хочется сказать о преподавании уроков автором: это продуманное, доходчивое, легко воспринимаемое на слух изложение материала лекции. Сложно конкретизировать, чем именно помог курс, потому что каждый из 6-ти модулей внес свою положительную лепту в мою работу: и в выполнение сложных расчетов, и консолидация файлов, и сводные таблицы, и форматирование и т.д. Спасибо за прекрасный экскурс в бескрайние просторы и возможности программы Excel. Кроме того, спасибо за постоянную, полезную и ненавязчивую рассылку примеров, уроков и трюков для изучения и повторения уже изученного материала. Повторение - мать учения! Успеха Вам, Дмитрий, желаю от души!

Начальник отдела ПОС логистической компании

Продвинутый уровень MS Excel - полезный курс для тех, кто хочет упростить свою работу и автоматизировать многие процессы за короткий срок. Очень удобный формат обучения: видео + прикрепленный файл Excel, в котором можно повторить на практике полученные новые знания. Видеоролики небольшие (их можно смотреть во время обеда на работе), но в них кратко и понятно изложена информация. Особенно полезным для меня оказался модуль сводных таблиц. Несмотря на то что у меня уже был опыт работы с ними, открыла для себя много полезной информации: использование срезов, формирование сводных таблиц по клиентам на отдельных листах, применение условного форматирования для сводной таблицы, группировка, сортировка, сводные таблицы и т.д. Отдельно хочу отметить урок «Модель данных (урезанный Power Pivot): до изучения этого урока я тратила много времени на подтягивание данных из одной таблицы в другую с помощью функций ВПР либо СУММЕСЛИ. Теперь могу строить сводную таблицу, которая строится из двух и более исходных таблиц. Также полезными оказались уроки по условному форматированию: до просмотра этих уроков я использовала только собственную формулу для определения форматируемых ячеек. Это было не всегда удобно, т.к., как оказалось, часто приходилось изобретать велосипед. Теперь, перед написанием собственной формулы, просматриваю уже готовые решения условного форматирования и часто нахожу там то, что мне необходимо. Отличный курс для тех, кто хочет улучшить свои знания по MS Excel и освежить уже имеющиеся.

В видео уроках автор доступным языком излагает материалы курса с использованием конкретных наглядных примеров из повседневной практики. Неоспоримым преимуществом курса является его интерактивность – автор лично проверяет выполненные домашние задания, при необходимости к нему можно обратиться с уточняющими вопросами либо оставить сообщение на форуме. Несмотря на то, что я являюсь достаточно опытным пользователем Excel, в материалах курса с нескрываемым удивлением обнаружил целый ряд практических приемов, которые сейчас использую в своей работе. Отдельные приемы настолько эффективны, что позволяют экономить часы и даже дни работы! Кроме того, на курсе освоил новые диаграммы, о которых раньше даже не слышал (водопад, воронка продаж, Парето и др.).

Которые позволяют оптимизировать работу в MS Excel. А сегодня хотим предложить вашему вниманию новую порцию советов для ускорения действий в этой программе. О них расскажет Николай Павлов - автор проекта «Планета Excel», меняющего представление людей о том, что на самом деле можно сделать с помощью этой замечательной программы и всего пакета Office. Николай является IT-тренером, разработчиком и экспертом по продуктам Microsoft Office, Microsoft Office Master, Microsoft Most Valuable Professional. Вот проверенные им лично приёмы для ускоренной работы в Excel. ↓

Быстрое добавление новых данных в диаграмму

Если для вашей уже построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).

Эта функция появилась только в последней версии Excel 2013, но она стоит того, чтобы обновиться до новой версии досрочно. Предположим, что у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.). Чтобы выполнить такое преобразование, нужно просто начать писать желаемый текст в соседнем столбце вручную. На второй или третьей строке Excel попытается предугадать наши действия и выполнит дальнейшую обработку автоматически. Останется только нажать клавишу Enter для подтверждения, и все имена будут преобразованы мгновенно.

Подобным образом можно извлекать имена из email’ов, склеивать ФИО из фрагментов и т. д.

Копирование без нарушения форматов

Вы, скорее всего, знаете про «волшебный» маркер автозаполнения - тонкий чёрный крест в правом нижнем углу ячейки, потянув за который можно скопировать содержимое ячейки или формулу сразу на несколько ячеек. Однако есть один неприятный нюанс: такое копирование часто нарушает дизайн таблицы, т. к. копируется не только формула, но и формат ячейки. Этого можно избежать, если сразу после протягивания чёрным крестом нажать на смарт-тег - специальный значок, появляющийся в правом нижнем углу скопированной области.

Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Microsoft Excel скопирует вашу формулу без формата и не будет портить оформление.

В последней версии Excel 2013 появилась возможность быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам и т. п. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин Bing Maps. Это можно сделать и по прямой ссылке с сайта , нажав кнопку Add. После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней.

При желании в настройках плагина можно выбрать тип диаграммы и цвета для отображения.

Если в вашей книге количество рабочих листов перевалило за 10, то ориентироваться в них становится трудновато. Щёлкните правой кнопкой мыши по любой из кнопок прокрутки ярлычков листов в левом нижнем углу экрана.

Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом, правда? Всего-то пара десятков итераций «недолёт - перелёт», и вот оно, долгожданное «попадание»!

Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Вставка» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert - What If Analysis - Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.

Если этот подробный обзор охватил не все полезные фишки MS Excel, о которых вы знаете, делитесь ими в комментариях!