Коллеги! Необходимость автоматизации расчетов, с учетом огромного количества исходных данных, связанных с моей профессиональной деятельностью, проявила большой интерес к изучению 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, о которых вы знаете, делитесь ими в комментариях!
Поверьте, чем больше вы знаете об 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).
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.
Которые позволяют оптимизировать работу в 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, о которых вы знаете, делитесь ими в комментариях!
