ПРИЛОЖЕНИЕ 3

САМЫЕ ПОЛЕЗНЫЕ ФУНКЦИИ И БИЗНЕСХАКИ ДЛЯ РАБОТЫ В EXCEL

Большинство менеджеров, офисных работников, руководи­телей, аналитиков, специалистов работают с таблицами и массивами данных.

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

Большинство рутинных и повторяющихся операций можно как минимум упростить, как максимум — полностью автоматизировать!

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

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

А операции остались теми же самыми.

Сможете ли вы при существующей методике или наборе формул обработать в 20 раз больший объем строк/столбцов/таблиц? Или это займет слишком много времени, так как вы многое делаете вручную? Если последнее верно, то вам наверняка стоит пересмотреть свои методики и приемы в данном случае.

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

Все данные в примерах вымышлены.

КАК СДЕЛАТЬ ФАЙЛ EXCEL БЫСТРЕЕ И «ЛЕГЧЕ»

• Поменять формат на .XLSX (формат версий 2007 и более поздних), .XLSM (с макросами) или .XLSB (самый быстрый и сжатый, с макросами). Удивительно, но до сих пор многие пользуются по инерции файлами версии 2003 (расширение .XLS), хотя они медленнее и могут занимать в разы больше дискового пространства.
• Не заливать строки/столбцы цветом целиком (и вообще стараться избегать излишнего форматирования).
• Не ставить фильтр на все столбцы (их в файле новых версий 16 384. В вашей таблице обычно намного меньше).
• Проверить, нет ли условного форматирования на (излишне) большом диапазоне ячеек.
• Очистить примечания, если их много и они не нужны.
• Посмотреть, нет ли проверки данных на очень большом диапазоне ячеек.
• Не сохранять кэш сводных таблиц (Параметры сводной таблицы → Сохранять исходные данные вместе с файлом).
• Удалить неиспользуемые именованные диапазоны (диспетчер имен вызывается сочетанием клавиш Ctrl + F3).
• Удалить ненужные макросы, если они есть.

СОЗДАВАЙТЕ ИМЕНА

Диапазонам Excel можно присваивать имена, благодаря которым формулы выглядят более осмысленными:

=Продажи*Ставка_налога

Вместо

=A7*$B$5

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

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

КАК АВТОМАТИЧЕСКИ УСТАНОВИТЬ ШИРИНУ СТОЛБЦОВ/СТРОК?

Выделяем столбцы (строки) (для этого нужно навести курсор именно на названия столбцов (строк)).

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

НЕСОПОСТАВИМОЕ СОПОСТАВИМО: ДИАГРАММА С ДВУМЯ ОСЯМИ

У нас есть несовместимые по объему данные — количество сотрудников в компании и выручка (план-факт). Но мы хотим сравнить их в динамике на одном графике. Строим обычный график по всем данным:

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

Меняем тип на «График» и указываем, что количество сотрудников отображается на вспомогательной оси:

Результат:

КАК ВЫДЕЛИТЬ ЗНАЧЕНИЯ ЛУЧШЕ (ХУЖЕ) СРЕДНЕГО?

Выделяем данные, нажимаем на «Условное форматиро­вание» → «Правила отбора первых и последних значений» → Выбираем нужный вариант (например, выше среднего):

В появившемся окне выбираем вариант оформления:

КАК ОТСОРТИРОВАТЬ СПИСОК НЕ ПО АЛФАВИТУ?

В Excel можно быстро отсортировать данные в алфавитном порядке (или в обратном алфавитном порядке).

Но как быть, если элементы списка должны сортироваться в произвольном порядке?

Например, у вас есть отчет по продажам в разных городах, для каждого из которых указан федеральный округ, и его нужно сортировать именно по последнему. Причем ЦФО должен идти на первом месте, СЗФО — на втором, а ПФО — на третьем. По алфавиту их отсортировать не получится.

Как быть? Заходить в Сортировку (раздел «Данные» на ленте инструментов), выбирать сортировку по региону и в списке «Порядок» выбрать «Настраиваемый список».

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

Теперь вы сможете сортировать список в нужном вам порядке.

КАК СОЗДАТЬ ВЫПАДАЮЩИЙ СПИСОК В ЯЧЕЙКЕ?

Очень просто. Заходите в раздел «Данные» на ленте инструментов, нажимайте на кнопку «Проверка данных», и появляется такое окно:

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

КАК БЫСТРО СКОПИРОВАТЬ РАБОЧИЙ ЛИСТ?

Конечно, вы можете кликнуть правой кнопкой на ярлыке рабочего листа и нажать в появившемся меню на «Пере­местить или скопировать». Но есть более быстрый способ: держите зажатой клавишу Ctrl и перемещайте ярлык листа вправо.

КАК БЫСТРО ПРЕВРАТИТЬ ФОРМУЛЫ В ЗНАЧЕНИЯ?

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

Второй путь — воспользоваться специальной функцией в надстройке PLEX (о ней — позже).

КАК ПОВЕРНУТЬ ТАБЛИЦУ НА 90°?

Выделяем таблицу и копируем ее (Ctrl + C): кликаем правой кнопкой мыши на ту ячейку, в которую хотим вставить перевернутую таблицу, в появившемся контекстном меню нажимаем на «Транспонировать».

КАК БЫСТРО ЗАПОЛНИТЬ ПУСТЫЕ ЯЧЕЙКИ?

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

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

КАК ПОСТРОИТЬ СВОДНУЮ ИЗ НЕСКОЛЬКИХ ИСТОЧНИКОВ ДАННЫХ

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

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

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

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

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

КАК ПРОСУММИРОВАТЬ ЯЧЕЙКИ С НЕСКОЛЬКИХ ЛИСТОВ?

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

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

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

=ФУНКЦИЯ­(первый_­лист:­последний_­лист!­ссылка на диапазон).

КАК РАЗДЕЛИТЬ ФРАЗУ, ЗАПИСАННУЮ В ОДНУ ЯЧЕЙКУ, НА ОТДЕЛЬНЫЕ СЛОВА В ОТДЕЛЬНЫХ ЯЧЕЙКАХ?

Выделяем ячейки и кликаем на кнопку «Текст по столбцам» (на ленте в разделе «Данные»).

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

В следующем окне выбираем разделитель — в данном случае пробел — и нажимаем «Далее».

В последнем окне выбираем, куда хотим разместить данные, и нажимаем «Готово».

КАК БЫСТРО ВЫЯВИТЬ КИРИЛЛИЦУ И ЛАТИНИЦУ?

Если нам нужно определить, где латинские символы, а где кириллические (и, например, быстро выявить, где по ошибке введена латинская «c» вместо кириллической), поменяйте шрифт в ячейке на какой-то, не поддерживающий кириллицу, например на Bauhaus 93.

КАК УБРАТЬ НЕНУЖНЫЕ ТЕКСТОВЫЕ ЭЛЕМЕНТЫ И ПРИМЕЧАНИЯ ИЗ ЦЕЛОГО МАССИВА ЯЧЕЕК?

Пример — список книг. В названиях некоторых книг в конце присутствует пометка «(т)», ее нужно убрать:

Для этого выделяем диапазон, нажимаем Ctrl + H и заполняем диалоговое окно (в разделе «Найти» — что нужно удалить, раздел «Заменить» оставляем пустым), нажимаем «Заменить».

Если же мы хотим убрать любые фразы в скобках, используем символ «звездочка» (*), который эквивалентен любому тексту. Удалятся все тексты в скобках, например: (т), (б), (переплет).

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

МАЛЕНЬКИЕ ГРАФИКИ В ЯЧЕЙКАХ: СПАРКЛАЙНЫ

У вас есть много рядов данных: допустим, данных по продажам книг (цифры в примере случайные, но это не столь важно), и вы хотите посмотреть динамику по каждому ряду, но при этом не создавать отдельных диаграмм. Для этого подойдут спарклайны — мини-графики в ячейках, которые появились в версии Excel 2010.

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

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

Выбираем диапазон с данными. На вкладке «Конструктор», которая появляется на ленте инструментов Excel при выделении ячеек со спарклайнами, можно изменить их внешний вид:

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

Кроме того, в ячейках со спарклайнами можно вводить текст.

Пример с типом спарклайна «Выигрыш/Проигрыш». Спарклайн показывает, были ли по соответствующему ряду возвраты (отрицательные «продажи») и нулевые продажи; и если были — то когда:

А ЧТО ДЕЛАТЬ, ЕСЛИ У ВАС EXCEL 2003 ИЛИ 2007?

В этих версиях спарклайнов еще не было. Но вы можете воспользоваться функцией ПОВТОР (REPT), которая повторяет текст определенное количество раз.

Ее первый аргумент — сам текст, а второй — количество повторов.

Выберите подходящий символ — можно какую-нибудь красивую иконку из шрифта Wingdings. Чтобы посмотреть, какие символы есть в шрифте и какие у них коды, напишите в столбец числа от 1 до 255, а правее введите функцию СИМВОЛ (шрифт в правом столбце нужно, соответственно, поменять на Wingdings).

Для продаж книг подойдет символ с номером 38 .

Хорош и обычный квадратик — у него номер 110 в шрифте Wingdings. Обратите также внимание на шрифты Webdings, Wingdings 2 и Wingdings 3.

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

КАК ВЫЧИСЛИТЬ ЧАСТЬ ФОРМУЛЫ?

Допустим, вы столкнулись с длинной и сложной формулой вроде этой:

=ЕСЛИ­ОШИБКА­(ЕСЛИ­ОШИБКА­(ВПР­($A3;­Отчет2015!­$A$2:­$Z­$1616;­СТОЛБЕЦ()­-14;0);­ВПР­(ВПР­($A3;­мэппинг!­$A$2:­$B­$999;­2;0);­Отчет2015!­$A­$2:­$Z­$1616;­СТОЛБЕЦ()-14;0));0).

И хотите в ней разобраться. Конечно, это лучше делать по частям — целиком осознать такую формулу сложно.

Чтобы вычислить часть ее, выделите эту часть в строке формул и нажмите F9 — после этого вместо выделенного фрагмента формулы появится тот текст или число, который он возвращает. Не забудьте затем нажать Escape — иначе в формуле так и останется вычисленное число вместо выделенного фрагмента.

Второй способ — инструмент «Вычислить формулу» на ленте инструментов в разделе «Формулы». Он поможет проанализировать пошаговое вычисление сложной формулы.

КАК ОПРЕДЕЛИТЬ, ОТ ЧЕГО ЗАВИСИТ ИЛИ НА ЧТО ССЫЛАЕТСЯ ФОРМУЛА

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

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

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

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

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

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

КАК РАССЧИТАТЬ КОЛИЧЕСТВО ВХОЖДЕНИЙ ТЕКСТА A В ТЕКСТ B. НАПРИМЕР, В ТЕКСТЕ «АВТОМАТИЧЕСКАЯ СИСТЕМА ПАРКОВКИ У НОВЫХ АВТОМОБИЛЕЙ» — ДВА ВХОЖДЕНИЯ ТЕКСТА «АВТО».

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

ДЛСТР (LEN) — вычисляет длину текста, единственный аргумент — текст. Пример: ДЛСТР ("машина") = 6.

ПОДСТАВИТЬ (SUBSTITUTE) — заменяет в текстовой строке определенный текст другим. Синтаксис: ПОДСТАВИТЬ (текст; стар_текст; нов_текст).

Пример: ПОДСТАВИТЬ ("автомобиль";"авто";«»)= «мобиль».

ПРОПИСН (UPPER) — заменяет все символы в строке на прописные. Единственный аргумент — текст. Пример: ПРОПИСН ("машина") = «МАШИНА». Эта функция понадобится нам, чтобы делать поиск без учета регистра. Ведь ПРОПИСН("машина")=ПРОПИСН("Машина").

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

ДЛСТР("Автоматическая система парковки у новых автомобилей") – ДЛСТР("матическая система парковки у новых мобилей") = 8

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

8 / ДЛСТР ("авто") = 2

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

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

=(ДЛСТР­(текст)-ДЛСТР­(ПОДСТАВИТЬ­(ПРОПИСН­(текст);­ПРОПИСН­(искомый); ")))/­ДЛСТР­(искомый)

КАК НАЙТИ ДУБЛИКАТЫ В СПИСКЕ?

Выделяем список, выбираем на ленте в разделе «Главная»: Условное форматирование → Правила выделения ячеек → Повторяющиеся значения.

В появившемся окне выбираем стиль выделения дубликатов.

Если же дубликаты нужно удалить, а не выделить, то выделите таблицу (в данном примере дубликаты предварительно выделены) и нажмите на кнопку «Удалить дубликаты» в разделе «Данные». В появившемся окне укажите, в каком именно столбце ищем дубликаты:

ОБНОВЛЯЕМЫЕ ДАТА И ВРЕМЯ В EXCEL

Вводим формулы

=СЕГОДНЯ() или =ТДАТА().

Первая — текущая дата, вторая — дата и время. ТДАТА можно отформатировать как время, и будет отображаться только оно:

Значения обновляются при любом действии (вводе данных в любую ячейку).

Для Google Таблиц:

=NOW() — текущие дата и время (отображение будет зависеть от форматирования):

КАК ОПРЕДЕЛИТЬ НОМЕР НЕДЕЛИ ПО ДАТЕ?

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

=НОМНЕДЕЛИ(A1;2)

Второй аргумент, равный 2, означает, что первый день недели — понедельник. Если бы он был равен 1, первым днем считалось бы воскресенье.

Если у вас Excel 2003, воспользуйтесь следующей формулой:

=1+­ЦЕЛОЕ­((A1-ДАТА­(ГОД­(A1+­4-ДЕНЬ­НЕД­(A1+6));­1;5)+ДЕНЬ­НЕД­(ДАТА­(ГОД­(A1+4-ДЕНЬ­НЕД­(A1+6));­1;­3)))/7)

В Google Таблицах тоже есть функция WEEKNUM. Просто укажите в качестве аргумента этой функции ячейку с датой, и получите номер недели:

= WEEKNUM(A1)

КАК ВЫДЕЛИТЬ ДАТЫ, КОТОРЫЕ УЖЕ ПРОШЛИ?

Выделяем ячейки с датами, выбираем «Условное форматирование» → «Правила выделения ячеек» → «Дата»:

Выбираем подходящий вариант, нажимаем ОК и получаем результат:

Как быстро добавить новые данные в диаграмму?

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

Как сделать это максимально быстро?

Очень просто: выделите те данные, которые нужно добавить (в данном случае диапазон A12:B13), скопируйте их (Ctrl + C), выделите диаграмму мышкой и вставьте (Ctrl + V) данные. Несколько секунд — и готово!

ИСПОЛЬЗУЙТЕ «УМНЫЕ ТАБЛИЦЫ» EXCEL

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

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

Что изменится?

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

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

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

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

СЦЕНАРНЫЙ АНАЛИЗ В EXCEL — 1. ТАБЛИЦЫ ДАННЫХ

В старых версиях Excel этот инструмент назывался не «Таблицы данных», а «Таблицы подстановки». Суть же не изменилась.

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

Рассмотрим этот инструмент на примере. Допустим, у нас есть простая модель расчета прибыли от продаж (серым обозначены входящие параметры, а в белых ячейках — расчетные показатели):

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

В этом примере в строке стоят ссылки на ячейки с выручкой, себестоимостью и прибылью от продаж. В столбце — разные варианты по объему производства. После того как данные готовы, необходимо выделить всю таблицу (в данном случае с ячейки «Количество товаров» и до правого нижнего угла), на ленте инструментов выбрать:

Данные → Анализ «Что если» → Таблица данных

и в появившемся диалоговом окне в пункте «Подставлять данные по строкам» (то есть наши варианты по количеству производимых товаров) поставить ссылку на ячейку с количеством товаров в нашей модели — в примере это ячейка B3.

После этого в таблице будут отображены разные сценарии изменения выручки, себестоимости и прибыли от продаж при шести вариантах объема производства:

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

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

Выделяем таблицу и снова вызываем инструмент «Таблица данных». Но теперь в диалоговом окне мы ставим ссылки на две ячейки исходной модели — с удельной себестоимостью и объемом производства:

И получаем результат:

СЦЕНАРНЫЙ АНАЛИЗ — 2. ПОДБОР ПАРАМЕТРА

Еще один полезный встроенный инструмент Excel для проведения анализа «Что если» — «Подбор параметра». Его можно найти там же, где и таблицы данных:

Данные → Анализ «Что если» → Подбор параметра.

Подбор параметра позволяет получить ответ на вопрос:

Каким должен быть входящий параметр, чтобы получить заданный результат?

Или, если рассматривать пример — модель из предыдущего раздела:

Какой должна быть себестоимость единицы товара (при прочих равных), чтобы получить прибыль, равную 58 000 рублей?

Вызовем инструмент «Подбор параметра», чтобы получить ответ:

В первом пункте мы указываем ссылку на ячейку с целевым показателем — в данном случае она была активна и подставилась автоматически (B9). Во втором пункте диалогового окна нужно указать желаемое целевое значение — мы хотим прибыль от продаж на уровне 58 тысяч. Изменять мы будем параметр в ячейке B5 — себестоимость единицы.

Нажимаем ОК и получаем результат. Можно сохранить его в таблице или вернуть исходные значения.

КАК ПОСТРОИТЬ ПРОСТОЙ ПРОГНОЗ В EXCEL

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

Самый простой способ — добавить линию тренда на график с показателем.

Допустим, у вас есть график с динамикой продаж. Щелкните правой кнопкой мыши на ряд данных и нажмите «Добавить линию тренда …»:

В окне «Формат линии тренда» выберите тип тренда (в данном случае может подойти линейный), а также на сколько периодов (в данном случае месяцев) вперед построить прогноз. Можно вывести на график коэффициент детерминации (R²) — чем он ближе к единице, тем точнее тренд описывает реальные данные, — соответственно, если вы будете сравнивать несколько типов линий тренда, то с помощью этого коэффициента сможете выбрать лучшую.

Результат:

Для прогнозирования не на графике, а в диапазоне ячеек можно воспользоваться функцией ПРЕДСКАЗ (FORECAST). У нее следующий синтаксис:

=ПРЕДСКАЗ­(x; известные_­значения_y; известные_­значения_x).

• Известные_значения_y — старые значения показателя, значения которого вы будете прогнозировать;
• известные_значения_x — значения фактора, влияющего на зависимый показатель y (в качестве x могут выступать и периоды — как в нашем примере);
• x — новое значение фактора.

Если прогнозируемый показатель сильно растет и вы ожидаете продолжения этого роста, воспользуйтесь функцией РОСТ (GROWTH). Синтаксис и принцип работы у нее аналогичный — с той лишь разницей, что прогноз строится по экспоненциальному тренду.

Как быть, если у вашего показателя сильные сезонные колебания? Рассмотрим самый простой и доступный способ.

Разделите продажи за каждый месяц (или другой период) на средние продажи за год или на показатели тренда (значения линейного тренда можно рассчитать с помощью функции ТЕНДЕНЦИЯ (TREND) — ее единственным аргументом будут известные значения прогнозируемого показателя).

Это пример с делением на средние продажи:

А это — с делением на тренд:

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

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

Например, для декабря показатель может составить 1,29, то есть в этот месяц высокого сезона показатель (допустим, выручка) на 129% выше «обычного». Для января, наоборот, коэффициент может быть равен 0,86, к примеру.

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

Если вы хотите построить многопараметрическую регрессию для прогнозирования показателя, зависящего от нескольких факторов, вам понадобится функция ЛИНЕЙН (LINEST). Ее описание, к сожалению, выходит за рамки этой книги — см. в конце раздела. Кроме того, вы можете обратиться к сайту , другим порталам или справке Excel.

ГОРЯЧИЕ КЛАВИШИ EXCEL

Чем меньше вы в Excel пользуетесь с мышью, тем быстрее вы работаете.

В Excel очень много горячих клавиш, позволяющих производить привычные рутинные операции мгновенно.

Почти все пользователи знают про стандартные сочетания вроде Ctrl + C и Ctrl + V, но ведь этим возможности программы не ограничиваются.

Очень удобны сочетания клавиш для быстрого выделения и перемещения по таблицам.

• Выделить всю таблицу — Ctrl + A.
• Переместиться в конец таблицы — Ctrl + стрелки.
• Выделить таблицу до конца — Ctrl + Shift + стрелки.
• Конец рабочего листа — Ctrl + End.
• Начало рабочего листа — Ctrl + Home.

Еще несколько полезных сочетаний:

• Установить или убрать фильтр — Ctrl + Shift + L.
• Создать новый лист — Shift + F11.
• Сохранить файл — Shift + F12.
• Переход на предыдущий/следующий лист — Ctrl + PgDown/PgUp.
• Процентный формат ячеек — Ctrl + Shift + 5.
• Денежный формат ячеек — Ctrl + Shift + 4.
• Формат «дата» — Ctrl + Shift + 3.
• Полужирный шрифт — Ctrl + B.
• Курсив — Ctrl + I.
• Подчеркнутый — Ctrl + U.
• Вставить текущую дату — Ctrl + Ж.
• Вставить текущее время — Ctrl + Shift + Ж.
• Повторение последнего действия — F4 (работает далеко не для всех действий. Очень удобно для быстрого многократного повторения вставки строки или столбца).
• Вернуться в активную ячейку — Ctrl + Backspace.
• Режим выделения (в котором можно выбирать несвязные диапазоны, не удерживая Ctrl) — Shift + F8.
• Выделить всю строку Ctrl + пробел.
• Выделить весь столбец Shift + пробел.
• Создание диаграммы из выделенных данных Alt + F1.
• Создание сводной таблицы Alt + D + P.
• Удерживайте Alt при добавлении любой фигуры — и ее границы будут растягиваться строго в соответствии с границами ячеек.

Источники данных:

В книге MrExcel XL — 40 Greatest Excel Tips of All Time есть «периодическая таблица горячих клавиш Excel». Она пригодится вам, если вы используете английскую версию Excel.

Полезную памятку с горячими клавишами русской версии можно скачать на сайте Николая Павлова — .

НАДСТРОЙКА PLEX

Автор сайта  — пожалуй, лучшего ресурса по Excel в российском интернете — создал надстройку PLEX, которая содержит 50 инструментов, отсутствующих в самом Excel. Например, таких:

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

Надстройка на момент написания книги стоит всего 795 рублей, а протестировать (500 запусков) ее можно бесплатно. Могу сказать, что одна функция FindSame окупила стоимость надстройки, сэкономила мне многие часы работы и очень облегчила работу с отчетностью. Она решает важную задачу: позволяет сопоставлять списки элементов, названия в которых совпадают не полностью (пример из моей практики: в одном списке название книги может быть с подзаголовком, в другом — без. Или в одном списке — с точкой, в другом — с двоеточием. Этого достаточно, чтобы для Excel такие названия были разными. Функция FindSame решает эту типичную проблему).

Купить надстройку (или попробовать тестовую версию) можно здесь: .

Отправить