Встроенный офисный продукт Microsoft Excel удобен для создания баз данных, ведения оперативного учета. Надстройки программы предоставляют пользователю возможность для продвинутых пользователей автоматизировать работу и исключить ошибки с помощью макросов.
В данном обзоре рассмотрим полезные возможности программы Excel, которые используют продвинутые пользователи для решения различных задач. Мы узнаем, как работать с базой данных в Excel. Научимся применять макросы на практике. А также рассмотрим использование совместного доступа к документам для совместной (многопользовательской) работы.
Как работать с базой данных в Excel
База данных (БД) – это таблица с определенным набором информации (клиентская БД, складские запасы, учет доходов и расходов и т.д.). Такая форма представления удобна для сортировки по параметру, быстрого поиска, подсчета значений по определенным критериям и т.д.
Для примера создадим в Excel базу данных.
Информация внесена вручную. Затем мы выделили диапазон данных и форматировали «как таблицу». Можно было сначала задать диапазон для БД («Вставка» - «Таблица»). А потом вносить данные.
Найдем нужные сведения в базе данных
Выбираем Главное меню – вкладка «Редактирование» - «Найти» (бинокль). Или нажимаем комбинацию горячих клавиш Shift + F5 или Ctrl + F. В строке поиска вводим искомое значение. С помощью данного инструмента можно заменить одно наименование значения во всей БД на другое.
Отсортируем в базе данных подобные значения
Наша база данных составлена по принципу «умной таблицы» - в правом нижнем углу каждого элемента шапки есть стрелочка. С ее помощью можно сортировать значения.
Отобразим товары, которые находятся на складе №3. Нажмем на стрелочку в углу названия «Склад». Выберем искомое значение в выпавшем списке. После нажатия ОК нам доступна информация по складу №3. И только.
Выясним, какие товары стоят меньше 100 р. Нажимаем на стрелочку около «Цены». Выбираем «Числовые фильтры» - «Меньше или равно».
Задаем параметры сортировки.
После нажатия ОК:
Примечание. С помощью пользовательского автофильтра можно задать одновременно несколько условий для сортировки данных в БД.
Найдем промежуточные итоги
Посчитаем общую стоимость товаров на складе №3.
С помощью автофильтра отобразим информацию по данному складу (см.выше).
Под столбцом «Стоимость» вводим формулу: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;E4:E41), где 9 – номер функции (в нашем примере – СУММА), Е4:Е41 – диапазон значений.
Обратите внимание на стрелочку рядом с результатом формулы:
С ее помощью можно изменить функцию СУММ.
В чем прелесть данного метода: если мы поменяем склад – получим новое итоговое значение (по новому диапазону). Формула осталась та же – мы просто сменили параметры автофильтра.
Как работать с макросами в Excel
Макросы предназначены для автоматизации рутинной работы. Это инструкции, которые сообщают порядок действий для достижения определенной цели.
Многие макросы есть в открытом доступе. Их можно скопировать и вставить в свою рабочую книгу (если инструкции выполняют поставленные задачи). Рассмотрим на простом примере, как самостоятельно записать макрос.
Создадим инструкцию, с помощью которой будет автоматически подсчитываться стоимость клиентского заказа:
- Скопируем таблицу на новый лист.
- Уберем данные по количеству. Но проследим, чтобы для этих ячеек стоял числовой формат без десятичных знаков (так как возможен заказ товаров поштучно, не в единицах массы).
- Для значения «Цены» должен стоять денежный формат.
- Уберем данные по стоимости. Введем в столбце формулу: цена * количество. И размножим.
- Внизу таблицы – «Итого» (сколько единиц товара заказано и на какую стоимость). Еще ниже – «Всего».
Талица приобрела следующий вид:
Теперь научим Microsoft Excel выполнять определенный алгоритм.
Как работать в Excel одновременно нескольким людям
Чтобы несколько пользователей имели доступ к базе данных в Excel, необходимо его открыть. Для версий 2007-2010: «Рецензирование» - «Доступ к книге».
Примечание. Для старой версии 2003: «Сервис» - «Доступ к книге».
Но! Если 2 и более пользователя изменили значения одной и той же ячейки во время обращения к документу, то будет возникать конфликт доступа.
Либо внесенная ранее одним пользователем информация будет удаляться при внесении следующим пользователем нового значения в эту же ячейку.
Программа не только увеличивает производительность работы пользователей, но и отслеживает их ошибки!
К сожалению, в многопользовательском режиме существуют некоторые ограничения. Например:
- нельзя удалять листы;
- нельзя объединять и разъединять ячейки;
- создавать и изменять макросы;
- ограниченная работа с XML данными (импорт, удаление карт, преобразование ячеек в элементы и др.).
Но в целом данный режим весьма привлекательный для большой и рутиной работы, которую вместе легче и быстрее выполнять. Совместный доступ существенно повышает производительность работы сотрудников на офисе.
В примере: совместный доступ к файлу - более детально описан процесс подключения и работы в многопользовательском режиме.
Microsoft Excel, пожалуй, самая распространенная офисная программа. Ей пользуются все от президента до школьника, так что, виртуозно овладев этим инструментом, Вы смоете выделиться серди офисных работников или облегчить свой повседневный труд.
Кроме множества простых функций, которые Вы наверняка уже знаете и используете, полезно изучить некоторые хитрости «для крутых», которые сделают Вас на порядок выше всех остальных. Так что, если хотите выпендриться перед начальником и разгромить конкурентов, Вам пригодятся эти 8 хитрых функций Excel.
- 3D-сумма
Теперь на вкладке TOTAL (ИТОГО) нам нужно увидеть, сколько и в какой день Вы потратили за этот период. Набираем =СУММ(‘Week1:Week7’!B2), и формула суммирует все значения в ячейке B2 на всех вкладках. Теперь, заполнив все ячейки, мы выяснили, в какой день недели тратили больше всего, а также в итоге подбили все свои расходы за эти 7 недель.
- Функция ПОИСКПОЗ
Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне (Диапазон — две или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными) ячеек и отражает относительную позицию этого элемента в диапазоне.
По отдельности ИНДЕКС и ПОИСКПОЗ не особо полезны. Но вместе они могут заменить функцию ВПР.
Например, чтобы в большой таблице найти, кто является главой Wells Fargo, пишем =ИНДЕКС(А3:А11,ПОИСКПОЗ(«Wells Fargo»,B3:B11,0).
С помощью функции ВПР этого не сделать, потому что она ищет только слева направо. А сочетание двух последних позволяет сделать это с легкостью.
Одна из самых удобных функций в арсенале Excel, а также одна из самых простых – это знак $. Он указывает программе, что не нужно делать автоматической корректировки формулы при её копировании в новую ячейку, как Excel поступает обычно.
При этом знак $ перед «А» не даёт программе изменять формулу по горизонтали, а перед «1» – по вертикали. Если же написать «$A$1», то значение скопированной ячейки будет одинаковым в любом направлении. Очень удобный приём, когда приходится работать с большими базами данных.
- Подбор параметра
Без этой функции Excel целым легионам аналитиков, консультантов и прогнозистов пришлось бы туго. Спросите кого угодно из сферы консалтинга или продаж, и Вам расскажут, насколько полезной бывает эта возможность Excel.
Например, Вы занимаетесь продажами новой видеоигры, и Вам нужно узнать, сколько экземпляров Ваши менеджеры должны продать в третьем месяце, чтобы заработать 100 миллионов долларов. Для этого в меню «Инструменты» выберите функцию «Подбор параметра». Нам нужно, чтобы в ячейке Total revenue (Общая выручка) оказалось значение 100 миллионов долларов. В поле set cell (Установить в ячейке) указываем ячейку, в которой будет итоговая сумма, в поле to value (Значение) – желаемую сумму, а в by Changing cell (Изменяя значение ячейки) выберите ячейку, где будет отображаться количество проданных в третьем месяце товаров. И – вуаля! – программа справилась. Параметрами и значениями в ячейках можно легко манипулировать, чтобы получить нужный Вам результат.
- Функция ВПР
Эта функция позволяет быстро найти нужное Вам значение в таблице. Например, нам нужно узнать финальный балл Бетт, мы пишем: =ВПР(“Beth”,A2:E6,5,0), где Beth – имя ученика, A2:E6 – диапазон таблицы, 5 – номер столбца, а 0 означает, что мы не ищем точного соответствия значению.
Функция очень удобна, однако нужно знать некоторые особенности её использования. Во-первых, ВПР ищет только слева направо, так что, если Вам понадобится искать в другом порядке, придется менять параметры сортировки целого листа. Также если Вы выберете слишком большую таблицу, поиск может занять много времени.
Если Вы хотите собрать все значения из разных ячеек в одну, Вы можете использовать функцию СЦЕПИТЬ. Но зачем набирать столько букв, если можно заменить их знаком «&».
- Массивы
Для создания массива или матрицы Вам потребуется несколько операций, но они сложнее, чем в случае с обычными формулами, ведь для отображения результата требуется не одна, а несколько ячеек.
Например, давайте перемножим две матрицы. Для этого используем функцию МУМНОЖ (Массив 1, Массив 2). Главное, не забудьте закрыть формулу круглой скобкой. Теперь нажмите сочетание клавиш Ctrl+Shift+Enter, и Excel покажет результат умножения в виде матрицы. То же самое касается и других функций, работающих с массивами, – вместо простого нажатия Enter для получения результата используйте Ctrl+Shift+Enter.
- Функция ИНДЕКС
Отражает значение или ссылку на ячейку на пересечении конкретных строки и столбца в выбранном диапазоне ячеек. Например, чтобы посмотреть, кто стал четвёртым в списке самых высокооплачиваемых топ-менеджеров Уолл-стрит, набираем: =ИНДЕКС(А3:А11, 4).
Коллеги!
Необходимость автоматизации расчетов, с учетом огромного количества исходных данных, связанных с моей профессиональной деятельностью, проявила большой интерес к изучению Excel, как программы с колоссальными возможностями.
На просторах интернета много информации, онлайн-тренингов, вебинаров и учебных пособий. Скажу честно: наиболее эффективным для меня оказалось изучение курса "Продвинутый уровень MS Excel", оказавшегося доступным для понимания даже "дилетанту", лаконичного, имеющего в своем составе огромный упорядоченный материал для изучения. Отдельно хочется сказать о преподавании уроков автором: это продуманное, доходчивое, легко воспринимаемое на слух изложение материала лекции.
Сложно конкретизировать, чем именно помог курс, потому что каждый из 6-ти модулей внес свою положительную лепту в мою работу: и в выполнение сложных расчетов, и консолидация файлов, и сводные таблицы, и форматирование и т.д.
Спасибо за прекрасный экскурс в бескрайние просторы и возможности программы Excel. Кроме того, спасибо за постоянную, полезную и ненавязчивую рассылку примеров, уроков и трюков для изучения и повторения уже изученного материала. Повторение - мать учения!
Успеха Вам, Дмитрий, желаю от души!
Начальник отдела ПОС логистической компании
В видео уроках автор доступным языком излагает материалы курса с использованием конкретных наглядных примеров из повседневной практики. Неоспоримым преимуществом курса является его интерактивность – автор лично проверяет выполненные домашние задания, при необходимости к нему можно обратиться с уточняющими вопросами либо оставить сообщение на форуме. Несмотря на то, что я являюсь достаточно опытным пользователем Excel, в материалах курса с нескрываемым удивлением обнаружил целый ряд практических приемов, которые сейчас использую в своей работе. Отдельные приемы настолько эффективны, что позволяют экономить часы и даже дни работы! Кроме того, на курсе освоил новые диаграммы, о которых раньше даже не слышал (водопад, воронка продаж, Парето и др.).
Аналитик
Продвинутый уровень MS Excel - полезный курс для тех, кто хочет упростить свою работу и автоматизировать многие процессы за короткий срок. Очень удобный формат обучения: видео + прикрепленный файл Excel, в котором можно повторить на практике полученные новые знания. Видеоролики небольшие (их можно смотреть во время обеда на работе), но в них кратко и понятно изложена информация.
Особенно полезным для меня оказался модуль сводных таблиц. Несмотря на то что у меня уже был опыт работы с ними, открыла для себя много полезной информации: использование срезов, формирование сводных таблиц по клиентам на отдельных листах, применение условного форматирования для сводной таблицы, группировка, сортировка, сводные таблицы и т.д.
Отдельно хочу отметить урок «Модель данных (урезанный Power Pivot): до изучения этого урока я тратила много времени на подтягивание данных из одной таблицы в другую с помощью функций ВПР либо СУММЕСЛИ. Теперь могу строить сводную таблицу, которая строится из двух и более исходных таблиц.
Также полезными оказались уроки по условному форматированию: до просмотра этих уроков я использовала только собственную формулу для определения форматируемых ячеек. Это было не всегда удобно, т.к., как оказалось, часто приходилось изобретать велосипед. Теперь, перед написанием собственной формулы, просматриваю уже готовые решения условного форматирования и часто нахожу там то, что мне необходимо.
Отличный курс для тех, кто хочет улучшить свои знания по MS Excel и освежить уже имеющиеся.
Ведущий специалист по закупкам
