Elettracompany.com

Компьютерный справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Отчет в формате excel

Делаем профессиональные отчёты в Excel: пять простых советов

Ольга Базалева, руководитель аналитического отдела в РБК, создатель онлайн-школы Excellent, автор книги «Мастерство визуализации данных» и автор блога, поделилась с Нетологией советами, как делать более удобные и понятные коллегам отчёты в Excel.

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

Хотите, чтобы руководитель вас заметил и в глазах коллег вы стали классным профессионалом? Научитесь делать качественные отчёты.

ольга базалева

Создатель онлайн-школы визуализации данных Excellent

курс

Power BI

Узнать больше

  • Создавайте дашборды по ключевым метрикам самостоятельно и без программирования
  • Проводите аналитические исследования данных из различных источников

Встаньте на место начальника. Кому вы готовы платить больше? Специалисту, который присылает ему данные в таком виде?

1. Помните о наглядности

  • Располагайте данные логично: обычно это от большего к меньшему.
  • Всегда разделяйте разряды в больших числах: 10 000 000, а не 10000000.
  • Убирайте лишние знаки после запятой.
  • Убирайте яркую сетку, это визуальный мусор. Делайте линии светлыми, а иногда можно обойтись вовсе без сетки.

  • Дизайн таблиц должен быть лаконичным и не отвлекать от сути.
  • Выделяйте шапку и строки/столбцы с итогами.
  • Выделяйте отрицательные значения красным, а второстепенные значения — серым. В Excel это можно сделать автоматически с помощью условного форматирования.
  • Визуализируйте данные в таблицах. Можно строить гистограммы непосредственно в ячейках при помощи условного форматирования и создавать миниграфики, которые располагаются в одной ячейке (спарклайны).

2. Используйте диаграммы

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

3. Пишите выводы в сопроводительном письме

Отчёт вложен в письмо и готов к отправке заказчикам? Не спешите нажимать кнопку «отправить». Подумайте, что нужно человеку, который откроет ваше письмо? Ему нужны быстрые выводы. Обязательно добавьте в текст письма аналитические комментарии и подкрепите их фактами из отчёта — например, добавьте скриншот диаграмм.

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

Читать также

4. Делайте отчёты удобными

Если ваш отчёт большой и занимает 10−20 листов в Excel, сделайте навигацию по нему. Это можно реализовать одним из следующих способов:

  • создать лист Summary, показать на нём главные цифры и сделать ссылки «Подробнее» с переходом на листы, содержащие все данные;
  • сделать оглавление, куда добавить ссылки на каждый лист с аналитикой.

Читать также

5. Автоматизируйте обновление регулярных отчётов

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

В Excel есть множество инструментов, которые помогут автоматизировать отчёты:

  • настройте выгрузку всех необходимых данных на один лист в Excel, назовём этот лист «Source»;
  • создайте необходимые аналитические таблицы на других листах;
  • формулами в эти аналитические таблицы подтяните нужные данные с листа Source;
  • сделайте связку Power Point — Excel. Тогда при изменении диаграмм в Excel, в презентации Power Point они будут обновляться автоматически.

Так вы создадите отчёт один раз и сможете быстро обновлять его в последующем, просто добавляя новые данные на лист Source, а все остальные таблицы и диаграммы тут же обновятся.

Когда ваши отчёты преобразятся, они станут в разы ценнее. Заказчики данных будут быстро получать ответы на свои вопросы, а набор сухих таблиц превратится в удобные и наглядные аналитические отчёты. Специалистов, которые умеют обрабатывать информацию и помогают принимать бизнес-решения ценят гораздо больше, чем тех, кто занимается простым заполнением непонятных таблиц.

Мнение автора и редакции может не совпадать. Хотите написать колонку для «Нетологии»? Читайте наши условия публикации. Чтобы быть в курсе всех новостей и читать новые статьи, присоединяйтесь к Телеграм-каналу Нетологии.

Формируем отчеты в Excel для руководства

Как разработать формы отчетности для руководства в Excel?

Какие расчетные формулы в них следует прописать?

Как заполнить управленческие отчеты в Excel данными о работе компании?

Как визуализировать данные управленческих отчетов с помощью Excel?

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

  • во-первых, перечень таких отчетов ограничен типовым функционалом программы, набор показателей в них зафиксирован возможностями аналитики данных в этой программе. Соответственно, чтобы расширить перечень отчетов или их показателей, необходимо привлекать к доработке программистов и нести дополнительные расходы;
  • во-вторых, большинство отчетов представлены в виде табличных форм. В них есть колонки «План», «Факт» и «Отклонения», но этого недостаточно, чтобы информативно и наглядно продемонстрировать руководству результаты деятельности компании;
  • в-третьих, когда речь заходит о формировании комплексной отчетности, то неизбежно сталкиваемся с тем, что в программе легко можно сформировать несколько отчетов с разноплановыми показателями, но сделать один отчет с общими данными нескольких отчетов пользователю программы просто невозможно — для этого нужно создавать как минимум дополнительные обработки, которые опять же ограничены техническими возможностями используемой программы.

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

Разработка форм отчетности для руководства в Excel с расчетными формулами

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

Для того чтобы отчетность для руководства получилась информативной и наглядной, достаточно сначала сформировать типовые отчеты в программе и сохранить их как файлы формата Excel. Затем данные этих файлов копируем как отдельные листы в новой книге и далее формируем в ней листы с формами управленческой отчетности.

Например, руководителю финансово-экономической службы компании «Альфа» нужно предоставить руководителю и собственнику компании отчеты о результатах работы компании за I полугодие 2019 г. исходя из типовых форм регламентированной отчетности — баланса и формы 2 к нему.

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

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

  • «Структура баланса» — для расчета динамики структуры агрегированных показателей имущества компании и источников его финансирования;
  • «Оборотный капитал» — для определения размера чистого оборотного капитала компании;
  • «Безубыточность» — для расчета точки безубыточности и запаса финансовой прочности на начало и конец отчетного периода;
  • «ФинПоказатели» — для расчета комплекса финансовых показателей и коэффициентов, характеризующих состояние компании;
  • «Сводные показатели» — для визуализации обобщенной динамики финансового положения компании по итогам рассматриваемого периода.

Следующий этап построения управленческой отчетности — заполнение созданных на этих листах отчетных форм данными из представленных выше финансовых отчетов — баланса и формы 2 к нему.

Читать еще:  Вставить поле в word

Заполнение управленческих отчетов в Excel данными о работе компании

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

Начнем с первого из отчетов — «Структура баланса»:

  • в колонках отчета «01.01.2019» и «01.07.2019» по сумме пропишем ссылки на ячейки с показателями листа «Баланс»;
  • в колонках отчета «01.01.2019» и «01.07.2019» по процентам пропишем расчет доли значения каждой из строки актива или пассива в общей их сумме;
  • в колонке «Динамика» пропишем разность между значениями колонок «01.07.2019» и «01.01.2019».

Полученный отчет представлен в табл. 3.

На основе данных этого отчета уже можно легко увидеть, что за I полугодие 2019 г. у компании «Альфа» в структуре активов увеличилась доля дебиторской задолженности и уменьшилась доля внеоборотных активов и запасов ТМЦ.

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

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

Далее переходим к отчету «Оборотный капитал»: заполним ячейки по строкам «Внеоборотные активы», «Инвестированный капитал»» «Оборотные активы» и «Краткосрочные пассивы» ссылками на соответствующие разделы листа «Баланс» и пропишем по строке «Чистый оборотный капитал» расчетную формулу «Оборотные активы-Краткосрочные пассивы».

А. А. Гребенников
финансовый директор ООО «Ипатовский пивзавод»

Материал публикуется частично. Полностью его можно прочитать в журнале «Справочник экономиста» № 9, 2019.

Создание отчетов при помощи сводных таблиц

Видео

Лирическое вступление или мотивация

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

В ней каждая отдельная строка содержит полную информацию об одной отгрузке (сделке, партии):

  • кто из наших менеджеров заключил сделку
  • с каким из заказчиков
  • какого именно товара и на какую сумму продано
  • с какого из наших складов была отгрузка
  • когда (месяц и день месяца)

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

  • Сколько и каких товаров продали в каждом месяце? Какова сезонность продаж?
  • Кто из менеджеров сколько заказов заключил и на какую сумму? Кому из менеджеров сколько премиальных полагается?
  • Кто входит в пятерку наших самых крупных заказчиков?

Ответы на все вышеперечисленные и многие аналогичные вопросы можно получить легче, чем Вы думаете. Нам потребуется один из самых ошеломляющих инструментов Microsof Excel — сводные таблицы.

Если у вас Excel 2003 или старше

Ставим активную ячейку в таблицу с данными (в любое место списка) и жмем в меню Данные — Сводная таблица (Data — PivotTable and PivotChartReport) . Запускается трехшаговый Мастер сводных таблиц (Pivot Table Wizard) . Пройдем по его шагам с помощью кнопок Далее (Next) и Назад (Back) и в конце получим желаемое.

Шаг 1. Откуда данные и что надо на выходе?

На этом шаге необходимо выбрать откуда будут взяты данные для сводной таблицы. В нашем с Вами случае думать нечего — «в списке или базе данных Microsoft Excel». Но. В принципе, данные можно загружать из внешнего источника (например, корпоративной базы данных на SQL или Oracle). Причем Excel «понимает» практически все существующие типы баз данных, поэтому с совместимостью больших проблем скорее всего не будет. Вариант В нескольких диапазонах консолидации (Multiple consolidation ranges) применяется, когда список, по которому строится сводная таблица, разбит на несколько подтаблиц, и их надо сначала объединить (консолидировать) в одно целое. Четвертый вариант «в другой сводной таблице. » нужен только для того, чтобы строить несколько различных отчетов по одному списку и не загружать при этом список в оперативную память каждый раз.

Вид отчета — на Ваш вкус — только таблица или таблица сразу с диаграммой.

Шаг 2. Выделите исходные данные, если нужно

На втором шаге необходимо выделить диапазон с данными, но, скорее всего, даже этой простой операции делать не придется — как правило Excel делает это сам.

Шаг 3. Куда поместить сводную таблицу?

На третьем последнем шаге нужно только выбрать местоположение для будущей сводной таблицы. Лучше для этого выбирать отдельный лист — тогда нет риска что сводная таблица «перехлестнется» с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку Готово (Finish) и переходим к самому интересному — этапу конструирования нашего отчета.

Работа с макетом

То, что Вы увидите далее, называется макетом (layout) сводной таблицы. Работать с ним несложно — надо перетаскивать мышью названия столбцов (полей) из окна Списка полей сводной таблицы (Pivot Table Field List) в области строк (Rows) , столбцов (Columns) , страниц (Pages) и данных (Data Items) макета. Единственный нюанс — делайте это поточнее, не промахнитесь! В процессе перетаскивания сводная таблица у Вас на глазах начнет менять вид, отображая те данные, которые Вам необходимы. Перебросив все пять нужных нам полей из списка, Вы должны получить практически готовый отчет.

Останется его только достойно отформатировать:

Если у вас Excel 2007 или новее

В последних версиях Microsoft Excel 2007-2010 процедура построения сводной таблицы заметно упростилась. Поставьте активную ячейку в таблицу с исходными данными и нажмите кнопку Сводная таблица (Pivot Table) на вкладке Вставка (Insert) . Вместо 3-х шагового Мастера из прошлых версий отобразится одно компактное окно с теми же настройками:

В нем, также как и ранее, нужно выбрать источник данных и место вывода сводной таблицы, нажать ОК и перейти к редактированию макета. Теперь это делать значительно проще, т.к. можно переносить поля не на лист, а в нижнюю часть окна Список полей сводной таблицы, где представлены области:

  • Названия строк (Row labels)
  • Названия столбцов (Column labels)
  • Значения (Values) — раньше это была область элементов данных — тут происходят вычисления.
  • Фильтр отчета (Report Filter) — раньше она называлась Страницы (Pages) , смысл тот же.

Перетаскивать поля в эти области можно в любой последовательности, риск промахнуться (в отличие от прошлых версий) — минимален.

Единственный относительный недостаток сводных таблиц — отсутствие автоматического обновления (пересчета) при изменении данных в исходном списке. Для выполнения такого пересчета необходимо щелкнуть по сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду Обновить (Refresh) .

Отчеты в EXCEL

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

В качестве исходной будем использовать таблицу в формате EXCEL 2007 ( Вставка/ Таблицы/ Таблица ), содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте. Аналогичная таблица использовалась в статье Сводные таблицы .

В таблице имеются столбцы:

  • Товар – наименование партии товара, например, « Апельсины »;
  • Группа – группа товара, например, « Апельсины » входят в группу « Фрукты »;
  • Дата поставки – Дата поставки Товара Поставщиком;
  • Регион продажи – Регион, в котором была реализована партия Товара;
  • Продажи – Стоимость, по которой удалось реализовать партию Товара;
  • Сбыт – срок фактической реализации Товара в Регионе (в днях);
  • Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.
Читать еще:  Как открыть word с поддержкой макросов

Через Диспетчер имен откорректируем имя таблицы на « Исходная_таблица » (см. файл примера ).

С помощью формул создадим 5 несложных отчетов, которые разместим на отдельных листах.

Отчет №1 Суммарные продажи Товаров

Найдем суммарные продажи каждого Товара. Задача решается достаточно просто с помощью функции СУММЕСЛИ() , однако само построение отчета требует определенных навыков работы с некоторыми средствами EXCEL.

Итак, приступим. Для начала нам необходимо сформировать перечень названий Товаров. Т.к. в столбце Товар исходной таблицы названия повторяются, то нам нужно из него выбрать только уникальные значения. Это можно сделать несколькими способами: формулами (см. статью Отбор уникальных значений ), через меню Данные/ Работа с данными/ Удалить дубликаты или с помощью Расширенного фильтра . Если воспользоваться первым способом, то при добавлении новых Товаров в исходную таблицу, новые названия будут включаться в список автоматически. Но, здесь для простоты воспользуемся вторым способом. Для этого:

  • Перейдите на лист с исходной таблицей;
  • Вызовите Расширенный фильтр ( Данные/ Сортировка и фильтр/ Дополнительно );
  • Заполните поля как показано на рисунке ниже: переключатель установите в позицию Скопировать результат в другое место ; в поле Исходный диапазон введите $A$4:$A$530; Поставьте флажок Только уникальные записи .

  • Скопируйте полученный список на лист, в котором будет размещен отчет;
  • Отсортируйте перечень товаров ( Данные/ Сортировка и фильтр/ Сортировка от А до Я ).

Должен получиться следующий список.

В ячейке B6 введем нижеследующую формулу, затем скопируем ее Маркером заполнения вниз до конца списка:

Для того, чтобы понять сруктурированные ссылки на поля в таблицах в формате EXCEL 2007 можно почитать Справку EXCEL (клавиша F1 ) в разделе Основные сведения о листах и таблицах Excel > Использование таблиц Excel .

Также можно легко подсчитать количество партий каждого Товара:

Отчет №2 Продажи Товаров по Регионам

Найдем суммарные продажи каждого Товара в Регионах. Воспользуемся перечнем Товаров, созданного для Отчета №1. Аналогичным образом получим перечень названий Регионов (в поле Исходный диапазон Расширенного фильтра введите $D$4:$D$530). Скопируйте полученный вертикальный диапазон в Буфер обмена и транспонируйте его в горизонтальный. Полученный диапазон, содержащий названия Регионов, разместите в заголовке отчета.

В ячейке B 8 введем нижеследующую формулу:

=СУММЕСЛИМН(Исходная_Таблица[Продажи]; Исходная_Таблица[Товар];$A8; Исходная_Таблица[Регион продажи];B$7)

Формула вернет суммарные продажи Товара, название которого размещено в ячейке А8 , в Регионе из ячейки В7 . Обратите внимание на использование смешанной адресации (ссылки $A8 и B$7), она понадобится при копировании формулы для остальных незаполненных ячеек таблицы.

Скопировать вышеуказанную формулу в ячейки справа с помощью Маркера заполнения не получится (это было сделано для Отчета №1), т.к. в этом случае в ячейке С8 формула будет выглядеть так:

=СУММЕСЛИМН(Исходная_Таблица[Сбыт, дней]; Исходная_Таблица[Группа];$A8; Исходная_Таблица[Продажи];C$7)

Ссылки, согласно правил относительной адресации , теперь стали указывать на другие столбцы исходной таблицы (на те, что правее), что, естественно, не правильно. Обойти это можно, скопировав формулу из ячейки B8 , в Буфер обмена , затем вставить ее в диапазон С8: G 8 , нажав CTRL + V . В ячейки ниже формулу можно скопировать Маркером заполнения .

Отчет №3 Фильтрация Товаров по прибыльности

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

Создадим Выпадающий (раскрывающийся) список на основе Проверки данных со следующими значениями: (Все); Да; Нет . Если будет выбрано значение фильтра (Все) , то при расчете продаж будут учтены все записи исходной таблицы. Если будет выбрано значение фильтра « Да» , то будут учтены только прибыльные партии Товаров, если будет выбрано « Нет» , то только убыточные.

Суммарные продажи подсчитаем следующей формулой массива : =СУММПРОИЗВ((Исходная_Таблица[Группа]=A8)* ЕСЛИ($B$5=»(Все)»;1;(Исходная_Таблица[Прибыль]=$B$5))* Исходная_Таблица[Продажи])

После ввода формулы не забудьте вместо простого нажатия клавиши ENTER нажать CTRL + SHIFT + ENTER .

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

Так будет выглядеть отчет о продажах по Группам Товаров, принесших прибыль.

Выбрав в фильтре значение Нет (в ячейке B 5 ), сразу же получим отчет о продажах по Группам Товаров, принесших убытки.

Отчет №4 Статистика сроков сбыта Товаров

Вернемся к исходной таблице. Каждая партия Товара сбывалась определенное количество дней (см. столбец Сбыт в исходной таблице). Необходимо подготовить отчет о количестве партий, которые удалось сбыть за за период от 1 до 10 дней, 11-20 дней; 21-30 и т.д.

Вышеуказанные диапазоны сформируем нехитрыми формулами в столбце B .

Количество партий, сбытые за определенный период времени, будем подсчитывать с помощью формулы ЧАСТОТА() , которую нужно ввести как формулу массива :

Для ввода формулы выделите диапазон С6:С12 , затем в Строке формул введите вышеуказанную формулу и нажмите CTRL + SHIFT + ENTER .

Этот же результат можно получить с помощью обычной функции СУММПРОИЗВ() : =СУММПРОИЗВ((Исходная_Таблица[Сбыт, дней]>A6)* (Исходная_Таблица[Сбыт, дней] =B9)* (Исходная_Таблица[Дата поставки]

Excel-лайфхаки для тех, кто занимается отчётностью и обработкой данных

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

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

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

Простые альтернативы ВПР и ГПР, если искомые значения не в первом столбце таблицы: ПРОСМОТР, ИНДЕКС+ПОИСКПОЗ

Функции ВПР (VLOOKUP) и ГПР (HLOOKUP) работают только в том случае, если искомые значения находятся в первом столбце или строке той таблицы, из которой вы планируете получить данные.

В остальных случаях есть два варианта:

  1. Использовать функцию ПРОСМОТР (LOOKUP).
    У неё следующий синтаксис: ПРОСМОТР (искомое_значение; вектор_просмотра; вектор_результата). Но для её корректной работы нужно, чтобы значения диапазона вектор_просмотра были отсортированы по возрастанию:
  2. Использовать сочетание функций ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX).
    Функция ПОИСКПОЗ возвращает порядковый номер элемента в массиве (с её помощью вы можете найти, в какой строке таблицы искомый элемент), а функция ИНДЕКС возвращает элемент массива с заданным номером (который мы и узнаем с помощью функции ПОИСКПОЗ).Синтаксис функций:
    • ПОИСКПОЗ (искомое_значение; массив_поиска; тип_сопоставления) — для нашего случая нам нужен тип сопоставления «точное сопоставление», ему соответствует цифра 0.
    • ИНДЕКС (массив; номер_строки; [номер_столбца]). В данном случае номер столбца указывать не нужно, так как массив состоит из одной строки.

Как быстро заполнить пустые ячейки в списке

Задача — заполнить ячейки в столбце со значениями сверху (чтобы тематика стояла в каждой строке таблицы, а не только в первой строке блока книг по тематике):

Читать еще:  Как убрать нижнюю черту в word

Выделяем столбец «Тематика», нажимаем на ленте в группе «Главная» кнопку «Найти и выделить» → «Выделить группу ячеек» → «Пустые ячейки» и начинаем ввод формулы (то есть ставим знак равно) и ссылаемся на ячейку сверху, просто нажимая стрелку вверх на клавиатуре. После этого нажимаем Ctrl + Enter. После этого можно сохранить полученные данные как значения, так как формулы больше не нужны:

Как найти ошибки в формуле

Вычисление отдельной части формулы

Чтобы разобраться в сложной формуле (в которой в качестве аргументов функции используются другие функции, то есть одни функции вложены в другие) или найти в ней источник ошибок, часто нужно вычислить её часть. Есть два простых способа:

  1. Чтобы вычислить часть формулы прямо в строке формул, выделите эту часть и нажмите F9:

    В данном примере была проблема с функцией ПОИСК (SEARCH) — в ней были перепутаны местами аргументы. Важно помнить, что если вы не отмените вычисление части функции и нажмёте Enter, то вычисленная часть так и останется числом.
  2. Нажмите на кнопку «Вычислить формулу» в группе «Формулы» на ленте:


    В появившемся окне можно вычислять формулу по шагам и определить, на каком этапе и в какой функции возникает ошибка (если она есть):

Как определить, от чего зависит или на что ссылается формула

Чтобы определить, от каких ячеек зависит формула, в группе «Формулы» на ленте нажмите на кнопку «Влияющие ячейки»:

Появляются стрелки, указывающие, от чего зависит результат вычислений.

Если отображается символ, выделенный на картинке красным цветом, то формула зависит от ячеек, находящихся на других листах или в других книгах:

Щёлкнув на него, мы увидим, где именно находятся влияющие ячейки или диапазоны:

Рядом с кнопкой «Влияющие ячейки» находится кнопка «Зависимые ячейки», работающая аналогично: она отображает стрелки от активной ячейки с формулой к ячейкам, которые зависят от неё.

Кнопка «Убрать стрелки», расположенная в том же блоке, позволяет убрать стрелки к влияющим ячейкам, стрелки к зависимым ячейкам или же оба типа стрелок сразу:

Как найти сумму (количество, среднее) значений ячеек с нескольких листов

Допустим, у вас есть несколько однотипных листов с данными, которые вы хотите сложить, посчитать или обработать как-то иначе:

Для этого в ячейку, в которой вы хотите видеть результат, введите стандартную формулу, например СУММ (SUM), и укажите в аргументе через двоеточие название первого и последнего листов из списка тех листов, что вам нужно обработать:

Вы получите сумму ячеек с адресом B3 с листов «Данные1», «Данные2», «Данные3»:

Такая адресация работает для листов, расположенных последовательно. Синтаксис следующий: =ФУНКЦИЯ (первый_лист:последний_лист!ссылка на диапазон).

Как автоматически строить шаблонные фразы

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

  • Объединяем текст с помощью знака & (можете заменить его функцией СЦЕПИТЬ (CONCATENATE), но в этом нет особого смысла).
  • Текст всегда записывается в кавычках, ссылки на ячейки с текстом — всегда без.
  • Чтобы получить служебный символ «кавычки», используем функцию СИМВОЛ (CHAR) с аргументом 32.

Пример создания шаблонной фразы с помощью формул:

В данном случае, кроме функции СИМВОЛ (CHAR) (для отображения кавычек) используется функция ЕСЛИ (IF), позволяющая изменять текст в зависимости от того, наблюдается ли положительная динамика продаж, и функция ТЕКСТ (TEXT), позволяющая отобразить число в любом формате. Её синтаксис описан ниже:

ТЕКСТ (значение; формат)

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

Автоматизировать можно и более сложные тексты. В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчётности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать — стоит один раз озадачиться созданием формулы или макроса, которые избавят вас хотя бы от части работы.

Как сохранить данные в каждой ячейке после объединения

При объединении ячеек сохраняется только одно значение. Excel предупреждает об этом при попытке объединить ячейки:

Соответственно, если у вас была формула, зависящая от каждой ячейки, она перестанет работать после их объединения (ошибка #Н/Д в строках 3–4 примера):

Чтобы объединить ячейки и при этом сохранить данные в каждой из них (возможно, у вас есть формула, как в этом абстрактном примере; возможно, вы хотите объединить ячейки, но сохранить все данные на будущее или скрыть их намеренно), объедините любые ячейки на листе, выделите их, а затем с помощью команды «Формат по образцу» перенесите форматирование на те ячейки, которые вам и нужно объединить:

Как построить сводную из нескольких источников данных

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

Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить»:

После этого на ленте появится соответствующая иконка, нажатие на которую вызывает того самого мастера:

При щелчке на неё появляется диалоговое окно:

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

В следующем окне добавьте все диапазоны, на основании которых будет строиться сводная, и задайте им наименования:

После этого в последнем диалоговом окне укажите, где будет размещаться отчёт сводной таблицы — на существующем или новом листе:

Отчёт сводной таблицы готов. В фильтре «Страница 1» вы можете выбрать только один из источников данных, если это необходимо:

Как рассчитать количество вхождений текста A в текст B («МТС тариф СуперМТС» — два вхождения аббревиатуры МТС)

В данном примере в столбце A есть несколько текстовых строк, и наша задача — выяснить, сколько раз в каждой из них встречается искомый текст, расположенный в ячейке E1:

Для решения этой задачи можно воспользоваться сложной формулой, состоящей из следующих функций:

  1. ДЛСТР (LEN) — вычисляет длину текста, единственный аргумент — текст. Пример: ДЛСТР (“машина”) = 6.
  2. ПОДСТАВИТЬ (SUBSTITUTE) — заменяет в текстовой строке определённый текст другим. Синтаксис: ПОДСТАВИТЬ (текст; стар_текст; нов_текст). Пример: ПОДСТАВИТЬ (“автомобиль”;“авто”;“”)= “мобиль”.
  3. ПРОПИСН (UPPER) — заменяет все символы в строке на прописные. Единственный аргумент — текст. Пример: ПРОПИСН (“машина”) = “МАШИНА”. Эта функция понадобится нам, чтобы делать поиск без учёта регистра. Ведь ПРОПИСН(“машина”)=ПРОПИСН(“Машина”)

Чтобы найти вхождение определённой текстовой строки в другую, нужно удалить все её вхождения в исходную и сравнить длину полученной строки с исходной:

ДЛСТР(“Тариф МТС Супер МТС”) – ДЛСТР(“Тариф Супер”) = 6

А затем разделить эту разницу на длину той строки, которую мы искали:

6 / ДЛСТР (“МТС”) = 2

Именно два раза строка «МТС» входит в исходную.

Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» — тот, число вхождений которого нас интересует):

В нашем примере формула выглядит следующим образом:

Ссылка на основную публикацию
Adblock
detector