Елена Кузнецова
Контент-менеджер

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

Создание таблицы в Microsoft Excel

При запуске  программы нам предложат создать новую книгу – так называют все файлы, имеющие расширение .xlsx или .xls.

стартовая эксель

Далее откроется пустой лист с сетчатой разметкой: горизонтальная шкала обозначена цифрами (от 1 до 1048576), а вертикальная – латинскими буквами (от A до XFD). 

Преимущества такого элемента как умная таблица (УТ) заключаются в том, что:
  • ему можно присвоить имя, на которое потом будете ссылаться;
  • в заголовках имеется встроенный фильтр;
  • при добавлении новых столбцов или строк формулы автоматически копируются в них.

Если у вас уже есть набор данных, преобразовать их в искомое значение будет не трудно. Достаточно выделить любую область и выбрать на панели вкладку «Главная» → «Стили» → «Форматировать как таблицу» или применить горячие клавиши Ctrl + T.

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

выделенная область

Редактура внешних параметров осуществляется через «‎Конструктор», там же находим пакет готовых стилей для оформления. 

выбор цвета

Заметим, что если вас интересует создание не умной сетки, а самой обыкновенной, в программе Эксель это можно сделать тоже быстрым и удобным способом: 

  • зажимаем ЛК мыши;
  • выделяем нужную область;
  • вызываем контекстное меню правой кнопкой;
  • выбираем пункт «Формат ячеек»;
  • во вкладке «Границы» нажимаем на «Внешние» и «Внутренние».
форматирование

Основные элементы редактирования

Многие опции из рабочей панели повторяют текстовый редактор из пакета Microsoft Office. Они вынесены на верхнюю ленту и сгруппированы по назначению и располагаются в тематических закладках, внизу имеются подписи-подсказки. 

параметры

Однако для работы в Экселе нужно также знать, что у программы есть свои особенные инструменты, основной из них – это ячейка. Она имеет уникальный адрес из сочетания буквы и числа – по принципу шахматной доски. Под лентой находится поле ее имени. Если ввести туда координаты, программа выделит это место рамкой (табличным курсором). 

нумерация ячеек

Справа от поля имени находится строка функции (fx), куда вводятся формулы для расчетов.

fx

Внизу показываются ярлычки листов, которые создаются по умолчанию в количестве от 1 до 3, в зависимости от версии программы. Новые страницы добавляются с помощью значка справа на нижней панели.

листы
Контент-маркетинг в социальных сетях от студии SEMANTICA – комплексный метод взаимодействия и выстраивания доверительных отношений с вашей целевой аудиторией. Разработаем стратегию, составим и реализуем контент-план, будем администрировать аккаунт и запустим таргетированную рекламу для привлечения потенциальных клиентов.
Оставить заявку
Подробнее…

Использование функций Excel

Делать табличные отчеты можно во многих редакторах, однако программа Excel выгодно отличается тем, что имеет много интересных расширенных возможностей и фишек. Она располагает большим количеством готовых вычислительных выражений, которые вызываются двумя способами: через инструмент «Вставить функцию» в разделе «Формулы» или при нажатии на «fx». Ниже мы рассмотрим несколько фишек, которые сильно упростят для вас подсчеты и аналитику и сберегут вашу нервную систему. 

Как сделать «ВПР»

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

пример таблиц

Для начала добавим в первую табличку столбики для категорий Цена и Стоимость. После этого выделяем верхнюю ячейку столбца, куда следует перенести сведения (у нас это C2), кликаем на значок «fx» и выбираем ВПР. Выскочит окно для заполнения аргументов, а рядом с fx появится =ВПР().

значения
  • В «Искомое значение» вводим диапазон, по которому будет проводиться поиск в прайсе. У нас это наименования игрушек, то есть A2:A6.
  • В следующей графе указываем программе, где находится источник, из которого будет подтягиваться информация (в нашем случае E2:F6), и фиксируем эту ссылку значками $, чтобы выражение корректно сработало для всех строк и данные не смещались. Получится $E$2:$F$6
  • Далее пишем номер столбика в прайсе. В нашем примере это 2.
  • В последнем пункте указываем «ЛОЖЬ» или 0, поскольку нам требуются точные, а не приближенные к какому-то числу или дате значения.
аргументы

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

цена
товар

Сводные таблицы

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

сводка

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

выбор поля

А так это будет выглядеть, если перетащить «Регион» в область для строк.

название строк

Редактировать СТ нужно через специальный пункт «числовой формат», чтобы изменения касались всей информации.

Бесплатные и платные фотостоки: где брать фото без авторских прав Читайте также Бесплатные и платные фотостоки: где брать фото без авторских прав

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

При составлении отчетов часто требуется показать динамику данных или их структуру наглядно.

Для этого есть несколько типов диаграмм:
  • гистограмма;
  • линейчатая;
  • круговая;
  • классический график;
  • с областями;
  • точечная;
  • биржевая;
  • поверхность;
  • кольцевая;
  • пузырьковая;
  • лепестковая.

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

объекты

При выборе откроется выпадающий список с вариантами шаблонов.

типы диаграмм

После этого диаграмма сразу появится рядом. Редактирование стилей и структуры происходит достаточно интуитивно через «Конструктор» и «Формат» или по клику правой кнопки мыши прямо на макете. Также график можно перенести на отдельный лист. По такому же принципу работаем со всеми остальными графическими макетами.

перемещение

Содержание диаграммы настраивается через инструмент «Выбрать данные» в контекстном меню. 

Формулы в Excel –  самые полезные функции и интересные фишки

Информация в Эксель вносится вручную или подставляется автоматически в зависимости от применяемого правила. Рассмотрим несколько самых востребованных из них.

  • МАКС и МИН – первая находит наибольшее число в диапазоне, а вторая – наименьшее. Синтаксис: =МАКС/МИН (координаты ячеек).
колонки
колонка В
  • СРЗНАЧ – складывает все выделенные числа и делит результат на их количество. Синтаксис: = СРЗНАЧ(координаты).
среднее значение
  • СУММ – вычисляет сумму элементов. Синтаксис: = СУММ(диапазон)
сумма
  • СЧЁТ – поможет подсчитать в выбранном промежутке количество числовых значений. Синтаксис: =СЧЁТ(координаты элементов).
кол-во

Функция «ЕСЛИ»

Здесь речь идет уже не о простейших вычислениях, а о проверке на соблюдение определенных условий. Если они выполняются, Эксель воспринимает содержимое ячейки как истину, а если нет, то как ложь. Нам необходимо прописать не только условие, но и то, какие данные выдавать в каждом из сценариев. Синтаксис такой: =ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь]).

Например, премия сотруднику выдается только в случае выполнения плана продаж и составляет 5000 рублей. В строку «лог_выражение» мы введем условие, у нас это E5<100%. Далее прописываем, что должна выдать программа при истинности или ложности высказывания. Таким образом, сотрудник получит 0, если процент продаж меньше 100. 

выражения
Как посмотреть статистику ВК: анализ посещаемости на странице Читайте также Как посмотреть статистику ВК: анализ посещаемости на странице

Макросы

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

Обычно макросы скрыты по умолчанию, поэтому правой кнопкой мыши кликаем по ленте и выбираем «Настройку панели быстрого доступа». В открывшемся окне переносим из левого столбика пункт «Макросы» или «Разработчик» в некоторых версиях. 

добавление

После этого на ленте появится соответствующая закладка или раздел в категории «Вид». Давайте теперь решим, какой алгоритм следует автоматизировать. 

Например, мы хотим: 
  • добавить к сводке с наименованиями столбик с итоговой стоимостью;
  • перемножить количество и цену;
  • найти наибольшую статью расходов среди получившихся цифр.

Начинаем запись макроса. Сначала вам предложат ввести имя макрокоманды, настроить для нее горячие клавиши и придумать описание.

запись
имя

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

колонка D
строка 14

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

личная книга

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

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

условия для форматирования

Например, нам нужно выделить в столбце со стоимостью игрушек те цены, которые превышают 3000. Для этого мы выбираем актуальный промежуток и среди правил кликаем на «Больше…». В диалоговом окне вписываем пороговое значение и выбираем цвет для оформления. 

диапазон
выделение

Такое форматирование возможно и с помощью гистограмм, цветовых шкал и наборов значков. Например, тут готовый шаблон окрасил числа по возрастанию от зеленого к желтому и красному.

шкала цвета
Визуализация данных: виды и методы визуального предоставления информации Читайте также Визуализация данных: виды и методы визуального предоставления информации

Функция ПЛТ 

С её помощью можно быстро рассчитать простые кредитные задачи. Рассмотрим на примере: Вика планирует взять в банке 300 000 рублей и вернуть их в течение 2 лет. При этом кредитная ставка на эту сумму составляет 7% годовых. Чтобы выяснить, сколько предстоит платить банку, чтобы уложиться в срок, составим наглядную таблицу. 

параметры

Заходим в «Формулы» или пишем вручную =ПЛТ().

В окно с аргументами вносим:
  • для «Ставки» координаты годового процента и делим на 12 месяцев;
  • для «Креп» срок выплаты;
  • для «ПС» общую сумму;
  • для «БС» нуль или ЛОЖЬ.
ставка

Итоговое число должно получиться отрицательным. Тут же можно узнать, какая переплата будет по кредиту при таких условиях. Для этого достаточно перемножить получившийся взнос и значение для «Креп», а после прибавить размер кредита. В нашем случае выражение будет выглядеть вот так: =ПЛТ(D5/12;D6;D7;0)*D6+D7. Переплата при ежемесячном взносе в 13 432 рубля составит 22 363.

условия задачи

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

Он входит в блок «Анализ “что если”» во вкладке «Данные» и используется для поиска неизвестной, которую нужно ввести в одиночную формулу, чтобы получить желаемый (известный) результат. 

скриншот таблицы

Рассмотрим, как это работает на классическом примере. Рассчитаем процентную ставку, если мы знаем размер кредита (2 миллиона) и срок выплаты (2 года). 

результат

В строку с ежемесячным платежом вставляем ПЛТ, о которой рассказывали выше. Выходит, что при нулевой ставке следует в течение двух лет выплачивать каждый месяц почти 84 тысячи. Но мир не идеален, поэтому банк требует оплату в размере не ниже 90 000. Чтобы выяснить процент, выбираем одну из трех функций анализа «Подбор параметра». В первом поле открывшегося окна вводим координаты места, где находится формула для расчета, у нас это C5. Во втором указываем сумму, которую будет забирать банк (не забудьте про минус). А в третьем даем координаты, где должно находиться ставка. 

адрес ячейки

Эксель выдал нам результат 7,5%. Это значит, что при этом значении и ежемесячном взносе в 90 тысяч за два года можно выплатить кредит в два миллиона.  

результат подбора

Формула «ИНДЕКС»

Это классный способ быстро отыскивать данные по их координатам. В базовом виде здесь всего два аргумента: диапазон и порядковый номер в нём. Например, у нас есть ТОП самых популярных песен на радио, а нас просят найти, какая из них занимает 5 строчку. Вызываем функцию и в первой диалоговой рамке выбираем 1-ый пункт.

мастер функций

Для аргумента «массив» выделяем исследуемый промежуток, у нас это D3:D12, вписываем номер искомой строки, для столбца в нашем случае указываем 0 или ЛОЖЬ. 

песни

Если же мы, напротив, хотим выяснить, на каком месте ТОПа находится, например, песня Феллини, то добавляется еще одна формула – ПОИСКПОЗ.  Для этого начинаем снова с ИНДЕКСА, только в качестве массива поиска указываем столбец с местами в чарте.  

Далее добавляем ПОИСКПОЗ и вписываем аргументы уже для неё: 
  • искомое значение (в нашем случае Феллини);
  • просматриваемый массив, в котором оно находится ( столбик с песнями);
  • нуль или ЛОЖЬ для точного типа сопоставления.
индекс

Использование «Защиты ячеек»

Иногда на заполнение документа уходит уйма времени и сил, будет обидно, если ваша кошка случайно что-то нажмет и все исчезнет. Есть несколько способов это предотвратить. 

  • С помощью пункта «Формат ячеек» в контекстном меню по клику правой кнопки мыши. Откроется окно, в котором нужно выбрать вкладку «Защита». Там мы видим подсказку, как активировать инструмент.
скрытие формул

Переходим в раздел «Рецензирование» и выбираем «Защита листа». Вам предложат ввести пароль для отключения предохранения (не обязательно), а также выбрать, какие права редактирования будут иметь другие пользователи.

пароль

Теперь при попытке изменить что-то, программа выдаст вот такое сообщение.

форма предупреждения
  • Если нужна защита не всей страницы, а только какого-то участка, то выделяем его и нажимаем в той же вкладке «Разрешить изменение диапазонов» и выбираем «Создать» в появившемся окошке.
диапазоны защиты
установка пароля

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

Проведем полный, комплексный SEO аудит сайта, включая: техническая проверку, оптимизацию, коммерческие факторы, внешние характеристики. Никакой воды в отчете! Только описание существующих проблем и их эффективных решений.
Оставить заявку
Подробнее…

Закрепление заголовков строк и столбцов

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

закрепление
таблица вид

Тот же принцип работает и по горизонтали: при прокрутке вправо, у нас есть возможность постоянно видеть первый столбец. Окей, а что делать, если актуальные элементы не находятся в верхних строках? Тут нам пригодится пункт «Закрепить области».

Работает это так: 
  • выделяем на числовой или буквенной шкале место, перед которыми находятся закрепляемые ячейки;
  • кликаем на нужный пункт для закрепления.
закрепление области
вид

Использование «Специальной вставки» для транспонирования

Бывает так, что необходимо поменять местами заголовки по горизонтали и вертикали. Чтобы не переписывать всё вручную, в Эксель есть удобный лайфхак, который позволит это автоматизировать. 

  • Сначала выделите вашу таблицу и скопируйте через контекстное меню или горячие клавиши Ctrl + C.
  • Далее определите на листе место, куда будет помещаться транспонированный объект. Выделите его.
  • Вызовите контекстное меню и выберите пункт «Специальная вставка».
  • Отметьте галочкой «транспонировать».
  • В более новых версиях транспонирование изображено в виде пиктограммы, как указано на рисунке ниже.
спец вставка

Вот, как в итоге заголовки поменялись местами.

две таблицы

Использование «Мгновенного заполнения»

Эта функция встречается в версиях 2013 года и позже.

Крайне полезная фича, позволяющая:
  • вытягивать из массивов текста нужные слова или цифры;
  • переводить слова-числительные в числа;
  • преобразует даты в UNIX формат;
  • менять части текста местами;
  • исправлять регистр и формат и т.д.

Посмотрим, как это работает на практике. Например, у нас есть две колонки с текстами, которые через дефис требуется совместить в третьей. Понадобится ввести два-три примера вручную, чтобы программа догадалась, чего мы от нее хотим и подсветила эти варианты серым. Также мгновенное заполнение доступно на ленте инструментов или через горячие клавиши Ctrl + E.

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

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

Элементы разметки страницы

Поменять формат документа можно в один клик. Для этого находим в правом нижнем углу иконки около ползунка с масштабом.

значки

Крайняя слева активирует привычное рабочее поле сервиса, а остальные – постраничные версии для печати. Переключиться между ними можно также с помощью вкладки «Вид».

режимы
Существует также отдельный подраздел «Разметка страницы» с группами:
  • темы;
  • параметры;
  • вписать;
  • упорядочить.

Эти инструменты помогают отформатировать внешний вид документа, который вы хотите распечатать: ширину отступов, наличие подложки, размер листа, принудительные разрывы страничек. 

разметка

Переключение между таблицами

Часто в документах содержатся десятки страниц, среди которых пользователь вынужден научиться ориентироваться. Существует три способа перемещаться между ними. Рассмотрим каждый из них, чтобы вы выбрали наиболее удобный.  

  • Вариант номер один – горячие клавиши Ctrl + Page Up/Page Down. При нажатии этих комбинаций происходит перелистывание на одну страницу вперед или назад. Способ годится для не очень больших книг. Для объемных документов подойдут следующие два лайфхака.
  • Переключение с помощью полосы прокрутки. В нижней части рабочего пространства, слева от ярлычков с номерами страниц находятся стрелки. Если кликнуть по ним правой кнопкой мыши, откроется оглавление, по которому можно быстро перемещаться.
листы
  • И третий способ – создание гиперссылок, которые ведут с одного листа на другой. В этом нам помогут уже описанные выше ИНДЕКС и ПОИСКПОЗ. Например, у нас есть сетка на одной странице и табличка к ней на другой.

Чтобы выставить правильно гиперссылки необходимо:

  • Добавить столбец на первой странице и ввести туда =ИНДЕКС(диапазон, из которого будем извлекать данные). У нас это первый столбик.
поле для заполнения
колонка с данными
  • Теперь необходимо вычислить порядковый номер ячейки в этом столбце и для этого нам понадобится ПОИСКПОЗ, в котором всего три аргумента: что ищем, где и насколько тщательно. В нашем случае найти требуется название продукта, в первой колонке, совпадение точное. Получится вот такое выражение.
поле и ссылки
  • Чтобы преобразовать получившийся текст в ссылку на табличку, заворачиваем наше правило в функцию ЯЧЕЙКА и выбираем адрес в выпавшем списке. Не забудьте закрыть скобку в конце.
ячейка со ссылками
  • Вместо названий теперь будут стоять указания координатов. Приводим их к привычному виду с помощью формулы ГИПЕРССЫЛКА. У неё два аргумента: адрес, который у нас уже есть, и название. У нас будет «посмотреть в сводной».
поля в таблице
  • Чтобы Эксель корректно понял все выражение, перед ЯЧЕЙКА добавляем “#” и знак склейки &.
просмотр в сводной
  • Теперь, когда мы будем кликать по ссылке, нас автоматически перекинет в нужную строку на соответствующей странице книги.
продукты в таблице
Как попасть в быстрые ответы Google и Яндекса – советы и инсайды Читайте также Как попасть в быстрые ответы Google и Яндекса – советы и инсайды

Применение окна контрольного значения

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

Эта проблема решается следующим образом: 
  • Выберите диапазон, который желаете наблюдать.
  • На ленте найдите вкладку «Формулы» и кликните на пиктограмму с подписью «Окно контрольного значения».
контроль значений
  • В открывшейся рамке нажимаем на соответствующий пункт в первой вкладке.
ячейка контроля
  • Теперь этот элемент постоянно находится в поле зрения поверх рабочего пространства.

Заключение

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

Оставить заявку
Оставить заявку
Получите консультацию и персональное предложение по развитию вашего бизнеса.
У меня есть промокод
Заявка отправлена!
Мы свяжемся с вами в ближайшее время.
0 0 голоса
Рейтинг статьи
Подписаться
Уведомить о
0 Комментарий
Межтекстовые Отзывы
Посмотреть все комментарии