Сортировка данных в сводной таблице или сводной диаграмме
Сортировка данных полезна при наличии больших объемов данных в сводной таблице или сводной диаграмме. Вы можете выполнить сортировку в алфавитном порядке, в порядке убывания или в порядке возрастания. Сортировка — один из способов организации данных, который упрощает поиск данных для тщательного изучения.
Перед сортировкой
При сортировке данных учитывайте приведенные ниже сведения.
- Порядок сортировки зависит от выбранного языка. Убедитесь в том, что на панели управления в разделе «Региональные параметры» или «Язык и региональные стандарты» задан правильный языковой стандарт. Сведения о том, как изменить языковой стандарт, см. в справке Windows.
- Данные могут содержать начальные пробелы, которые влияют на результаты сортировки. Для получения оптимальных результатов следует удалить такие пробелы перед сортировкой.
- Нельзя сортировать текстовые значения с учетом регистра символов.
- Нельзя сортировать данные по определенному признаку форматирования, такому как цвет ячеек или шрифта. Также нельзя сортировать данные по индикаторам условного форматирования, например наборы значков.
Сортировка меток строк или столбцов
- В сводной таблице щелкните маленькую стрелку рядом со списком Названия строк или Названия столбцов.
- Щелкните поле в строке или столбце, которые требуется отсортировать.
- Щелкните стрелку в списке Названия строк или Названия столбцов, а затем выберите нужный параметр.
Чтобы отсортировать данные в порядке возрастания или убывания, нажмите кнопки Сортировка от А до Я или Сортировка от Я до А.
Текстовые элементы будут сортироваться в алфавитном порядке, числа — от минимального к максимальному или наоборот, а значения даты и времени — от старых к новым или от новых к старым.
Сортировка по столбцу, который не имеет кнопки со стрелкой
Можно сортировать по индивидуальным значениям или промежуточным итогам, выбрав команду Сортировать и способ сортировки. Порядок сортировки применяется ко всем ячейкам одного уровня в столбце, содержащем ячейку.
В приведенном ниже примере данные на уровне категории (шлем, дорожная сумка) сортируются в алфавитном порядке от А до Я.
Чтобы просмотреть общие итоги для продуктов, отсортированных по убыванию, выберите любое число в столбце Общий итог и отсортируйте по нему.
Совет: Чтобы быстро найти нужные данные в сводной таблице или сводной диаграмме, можно сгруппировать и отфильтровать их, а также применить условное форматирование.
Установка собственных параметров сортировки
Чтобы отсортировать элементы вручную или изменить порядок сортировки, можно задать собственные параметры сортировки:
- Щелкните ячейку в строке или столбце, которые требуется отсортировать.
- Щелкните стрелку на вкладке Метки строк или Метки столбцов, а затем выберите Дополнительные параметры сортировки.
В диалоговом окне Сортировка выберите необходимый тип сортировки:
- Чтобы изменить порядок элементов перетаскиванием, щелкните Вручную. Наведите указатель мыши на границу элемента, чтобы увидеть четырехконечную стрелку, затем перетащите ее.
- Элементы, которые отображаются в области значений списка полей сводной таблицы, нельзя перетаскивать.
- Щелкните По возрастанию (от А до Я) по полю или По убыванию (от Я до А) по полю и выберите поле для сортировки.
- Чтобы увидеть другие параметры сортировки, нажмите щелкните на пункте Дополнительные параметры, затем в диалоговом окне Дополнительные параметры сортировки выберите подходящий вариант:
Примечание: Сортировка по настраиваемым спискам не сохраняется после обновления данных в сводной таблице.
Совет: Чтобы восстановить исходный порядок элементов, выберите вариант Как в источнике данных. Он доступен только для источника данных OLAP.
Перед сортировкой
При сортировке данных учитывайте приведенные ниже сведения.
- Порядок сортировки зависит от выбранного языка. Убедитесь в том, что на панели управления в разделе «Региональные параметры» или «Язык и региональные стандарты» задан правильный языковой стандарт. Сведения о том, как изменить языковой стандарт, см. в справке Windows.
- Данные могут содержать начальные пробелы, которые влияют на результаты сортировки. Для получения оптимальных результатов следует удалить такие пробелы перед сортировкой.
- Нельзя сортировать текстовые значения с учетом регистра символов.
- Нельзя сортировать данные по определенному признаку форматирования, такому как цвет ячеек или шрифта. Также нельзя сортировать данные по индикаторам условного форматирования, например наборы значков.
Сортировка меток строк или столбцов по возрастанию или убыванию
- Щелкните ячейку в строке или столбце, которые требуется отсортировать.
- Щелкните стрелку на списке Названия строк или Названия столбцов, а затем выберите нужный параметр сортировки.
Текстовые элементы будут сортироваться в алфавитном порядке, числа — от минимального к максимальному или наоборот, а значения даты и времени — от старых к новым или от новых к старым.
Сортировка меток строк или столбцов вручную
Помимо сортировки по возрастанию или убыванию, можно вручную отсортировать каждую из подписей строк или столбцов.
- Наведите указатель мыши на границу элемента, пока не увидите курсор перемещения, а затем перетащите его.
- Вы также можете щелкнуть правой кнопкой мыши метки строк или столбцов и выбрать любой из параметров Переместить:
Примечание: Элементы, которые отображаются в области значений списка полей сводной таблицы, нельзя перетаскивать.
Сортировка по определенному значению
- Щелкните стрелку на списке Названия строк и выберите пункт Сортировка по значению.
Перед сортировкой
При сортировке данных учитывайте приведенные ниже сведения.
- Порядок сортировки зависит от выбранного языка. Убедитесь в том, что на панели управления в разделе «Региональные параметры» или «Язык и региональные стандарты» задан правильный языковой стандарт. Сведения о том, как изменить языковой стандарт, см. в справке Windows.
- Данные могут содержать начальные пробелы, которые влияют на результаты сортировки. Для получения оптимальных результатов следует удалить такие пробелы перед сортировкой.
- Нельзя сортировать текстовые значения с учетом регистра символов.
- Нельзя сортировать данные по определенному признаку форматирования, такому как цвет ячеек или шрифта. Также нельзя сортировать данные по индикаторам условного форматирования, например наборы значков.
Сортировка меток строк или столбцов
- В сводной таблице щелкните маленькую стрелку рядом со списком Названия строк или Названия столбцов.
- Щелкните поле в строке или столбце, которые требуется отсортировать.
- Щелкните стрелку в списке Названия строк или Названия столбцов, а затем выберите нужный параметр.
Чтобы отсортировать данные в порядке возрастания или убывания, нажмите кнопки Сортировка от А до Я или Сортировка от Я до А.
Текстовые элементы будут сортироваться в алфавитном порядке, числа — от минимального к максимальному или наоборот, а значения даты и времени — от старых к новым или от новых к старым.
Сортировка по столбцу, который не имеет кнопки со стрелкой
Можно сортировать по индивидуальным значениям или промежуточным итогам, выбрав команду Сортировать и способ сортировки. Порядок сортировки применяется ко всем ячейкам одного уровня в столбце, содержащем ячейку.
В приведенном ниже примере данные на уровне категории (шлем, дорожная сумка) сортируются в алфавитном порядке от А до Я.
Чтобы просмотреть общие итоги для продуктов, отсортированных по убыванию, выберите любое число в столбце Общий итог и отсортируйте по нему.
Совет: Чтобы быстро найти нужные данные в сводной таблице или сводной диаграмме, можно сгруппировать и отфильтровать их, а также применить условное форматирование.
См. также
- Создание сводной таблицы для анализа данных на листе
- Создание сводной таблицы для анализа внешних данных
- Создание сводной таблицы для анализа данных в нескольких таблицах
- Фильтрация данных в сводной таблице
- Группировка и отмена группировки данных в отчете сводной таблицы
- Применение условного форматирования в сводной таблице
Условное форматирование в сводных таблицах Excel
Элен Брэдли объясняет некоторые различия в форматировании сводных таблиц в Excel 2010.
- Как работает условное форматирование
- Как применить условное форматирование к сводной таблице
- Другие параметры
- Перемещаем данные
- Правила форматирования с ограничениями
С выходом Excel 2007, компания Microsoft добавила некоторые дополнительные возможности условного форматирования, такие как гистограммы и наборы значков, которые позволяют наглядно показать относительные величины в этих ячейках.
Microsoft внесла изменения в порядок применения условного форматирования к сводным таблицам. Теперь у Вас появилось больше возможностей и больше гибкости при использовании условных форматов. В этой статье я покажу Вам, как применять условное форматирование к сводным таблицам и как оседлать всю мощь новых возможностей.
Как работает условное форматирование
В Excel 2007 и 2010, когда условное форматирование применяется к сводной таблице, оно применяется в большей степени к структуре сводной таблицы, а не к самим ячейкам. Поэтому, когда Вы работаете со сводной таблицей, допустим, передвигаете поля с места на место или отображаете данные различными способами, форматирование обновляется вслед за Вашими действиями. Все это, в сочетании с новыми форматами, делает условное форматирование очень удобным инструментом для использования вместе со сводными таблицами.
Как применить условное форматирование к сводной таблице
Мы рассмотрим условное форматирование на примере сводной таблицы, в которой представлен график приема 4-х ветеринаров за год. В таблице отображается число клиентов с разбивкой по кварталам и по месту приема (ферма или хирургия).
Чтобы сделать данные более наглядными, я выбираю значения Farm (Ферма) и Surgery (Хирургия) первого ветеринара с фамилией Browning, т.е. ячейки от B6 до E7. Выделив этот диапазон, я захожу на вкладку Home (Главная), нажимаю Conditional Formatting > Data Bars (Условное форматирование > Гистограммы) и выбираю, какой использовать цвет. Эти действия форматируют выбранный диапазон таким образом, что в каждой ячейке появляется гистограмма, которая показывает относительное количество клиентов в каждом квартале и для каждого места приема.
На следующем рисунке мы применяем формат Data Bars (Гистограмма) к первому диапазону данных:
Чтобы применить это же форматирование к аналогичным данным для остальных ветеринаров, нужно выделить ранее отформатированный диапазон, кликнуть иконку Formatting Options (Настройки форматирования), которая появляется в правом нижнем углу диапазона, и выбрать третий из предложенных вариантов (см. рисунок ниже).
Таким образом, правило будет применено к аналогичным данным всех остальных ветеринаров в сводной таблице, без необходимости применять это правило к каждому диапазону по отдельности. Эти же опции Вы сможете найти, если решите создать новое правило форматирования в диалоговом окне New Formatting Rule (Создание правила форматирования).
На этом рисунке показано, как применить одинаковое условное форматирование ко всем данным одного типа в нашей сводной таблице:
Теперь всегда, когда в процессе работы со сводной таблицей Вы будете скрывать или отображать данные, гистограммы в ячейках будут изменяться, чтобы отразить относительную величину значения в каждой видимой ячейке по сравнению со всеми остальными видимыми ячейками того же формата.
Гистограммы изменяются, когда меняются данные в сводной таблице, длина гистограммы зависит от данных во всех видимых ячейках:
Мы можем пойти еще дальше и сравнить итоговые значения, используя другое условие для форматирования. В данном случае я хочу сравнить итоги по каждому ветеринару с итогами остальных ветеринаров, поэтому выбираю ячейки от B5 до E5 – общее количество клиентов (по кварталам) ветеринара Browning. Создав в этом диапазоне гистограммы другого цвета, я могу сравнивать итоговые данные о клиентах, которых осматривал ветеринар Browning за эти четыре квартала.
Как и в прошлый раз появляется иконка Formatting Options (Параметры форматирования), при помощи которой мы сможем применить это же условное форматирование к итоговым данным по клиентам каждого ветеринара.
Здесь Вы видите гистограммы другого цвета в ячейках с итоговыми значениями, которые можно наглядно сравнить друг с другом:
Другие параметры
В некоторых случаях имеет смысл разделить условное форматирование, как было сделано в прошлом примере, применяя его только к ячейкам, содержащим данные одного уровня, чтобы разделить итоги и общие итоги (Grand Total). Но не всегда имеет смысл это делать.
Данные в следующей сводной таблице показывают среднее число клиентов, а не суммарное количество клиентов, поэтому Вы можете применить одинаковое условное форматирование ко всем ячейкам таблицы.
Для этого выделим диапазон B6:E6, перейдем Conditional Formatting > Data Bars (Условное форматирование > Гистограммы) и выберем зелёный цвет для гистограмм. Далее в параметрах форматирования выберем второй вариант (см. рисунок ниже). Теперь условное форматирование охватывает, как итоговые, так и общие итоговые (Grand Total) значения, которые, как и все данные в сводной таблице, являются средними значениями. Поэтому не будет нарушением сравнить их одинаковым образом.
Здесь все ячейки содержат средние значения, поэтому допустимо применить одно правило условного форматирования:
Перемещаем данные
Вернёмся к нашей исходной сводной таблице и начнем перемещать данные. Делая это, Вы заметите, что форматирование сохраняется на нужных местах. Мы передвинули поле Office (Место приема) в область Report Filter (Фильтры) и перенесли поле Quarter (Квартал) в область Row Labels (Строки), и при этом все фиолетовые гистограммы остались на своих местах.
Даже если структура таблицы изменена и поля передвинуты, условное форматирование сохраняет свое положение:
Правила форматирования с ограничениями
Конечно, бывают ситуации, когда не нужно применять условное форматирование ко всем диапазонам, а вместо этого требуется сравнить данные в более узком интервале. В нашем примере, мы хотим увидеть, в каком квартале каждый из наших ветеринаров показал наилучшие результаты, не зависимо от места приема.
Мы создадим отдельное правило условного форматирования для квартальных итогов каждого ветеринара, то есть нам нужно выделить и применить форматирование к ячейкам D7:D10 (потом D12:D15, потом D17:D19 и так далее). Затем используем правило Conditional Formatting > Top 10 Items (Условное форматирование > Первые 10 элементов), устанавливаем условие для 1 ячейки, формат оставляем предложенный по умолчанию. Вы можете либо скопировать это правило в соседний диапазон, либо создать для каждого индивидуально.
Чтобы скопировать форматирование, выделите одну или несколько ячеек с нужным форматом и нажмите Copy (Копировать). Затем выделите диапазон, в который нужно скопировать форматирование, и на вкладке Home (Главная) выберите Paste Special > Formats (Специальная вставка > Форматы).
В некоторых случаях может потребоваться сравнить данные внутри небольшой области, а не применять правило условного форматирования на всю группу несмежных диапазонов:
Условное форматирование в Excel в сочетании с мощью сводных таблиц позволяет очень тонко настроить форматирование и точно определить, какие данные нужно сравнивать. Вы можете сравнить похожее с похожим, как по всей сводной таблице, так и в рамках одного поля, которое Вас интересует. Знание возможных вариантов и умение пользоваться ими поможет Вам нагляднее сравнить значения и получить желаемый результат.
Условное форматирование сводной таблицы в Excel
Сводная таблица — один из самых крутых инструментов для анализа и обобщения данных. Вы можете создавать мгновенные отчеты, используя сводные таблицы. Чтобы прокачать отчет и сделать его более привлекательным, можно применить условное форматирование к сводной таблице.
Ниже пример, где я применил условное форматирование в сводной таблице, чтобы выделить ячейки.
Excel выделяет город с наибольшими и наименьшими продажами, причем правило форматирования сохраняется при смене фильтра с помощью среза.
Сегодня в этой статье вы узнаете, как применять условное форматирование к сводной таблице, и все возможные способы ее использования.
Пошаговое применение условного форматирования к сводной таблице
Применение условного форматирования в сводной таблице на первый взгляд кажется сложным, но на самом деле это проще простого.
Задача: В приведенной ниже сводной таблице необходимо применить «3-цветную шкалу» к значениям «Месяц».
- Прежде всего, выберите любую из ячеек, которые имеют значение месяца.
- Затем перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
- Вы увидите диалоговое окно для применения условного форматирования к сводной таблице.
- В этом всплывающем окне у вас есть три различных варианта применения условного форматирования в сводной таблице.
- К выделенным ячейкам: используйте этот параметр, чтобы применить условное форматирование только к выбранной ячейке.
- Ко всем ячейкам, содержащим значения «Сумма»: все ячейки, которые имеют значения суммы.
- Ко всем ячейкам, содержащим «Сумма» для «Месяц»: все ячейки, значения которых имеют сумму, но только за месяцы.
- Выберите «Ко всем ячейкам, содержащим значения «Сумма» для «Месяц».
- В «Измените описание правила» выберите 3-х цветную шкалу.
- Измените тип минимума, среднего значения и максимума на проценты. После этого выберите цвет для всех трех.
- В конце нажмите ОК.
В приведенной выше сводной таблице мы применили трехцветное форматирование к ячейкам, имеющим месячную сумму. Используя это форматирование, вы можете легко проанализировать, какой месяц приносит больше дохода, а какой меньше.
Примеры применения условного форматирования в сводной таблице.
Использование условного форматирования в сводной таблице — разумный шаг, и вы можете использовать его по-разному.
Вот список примеров, которые вы можете сразу же применять в своей работе.
1. Динамическое условное форматирование с фильтрами
Когда вы используете условное форматирование в своей сводной, оно работает как динамическое форматирование. Всякий раз, когда вы фильтруете значения или изменяете данные, они автоматически обновляются с этим изменением.
В примере выше мы применили условное форматирование, чтобы выделить ячейку с наибольшим значением. Каждый раз, когда вы фильтруете вашу сводную таблицу, она автоматически учитывает текущие значения и применяет к ней правило форматирования.
2. Применение условного форматирования к отдельной строке в сводной таблице
В приведенной ниже сводной таблице представлены данные о продажах по продуктам и месяцам за 2019 год. Задача выделить ячейку, которая имеет наибольшие продажи для конкретного продукта.
- Выберите любую из ячеек.
- Перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
- Применить правило: третий вариант.
- Из типов правила выберите «Форматировать только первые или последние значения».
- В описании правила введите 1 в поле ввода и в раскрывающемся меню выберите «Каждой группы столбцов».
- Примените форматирование, которое вы хотите.
- Нажмите ОК.
При выборе «каждой группы столбцов» условное форматирование будет сравнивать значения только из строки. Если вы хотите применить это правило к столбцам вместо строк, вы можете выбрать «каждой группы строк» в описании правила.
Если же вы хотите применить правило ко всем ячейкам, просто выберите «всех значений» из выпадающего списка.
3. Условное форматирование в сводной таблице на основе другой ячейки
В приведенной ниже сводной таблице необходимо применить условное форматирование — гистограмма. Но вот в чем задача: у нас есть целевое значение в другой ячейке, и нам нужно применить условное форматирование по сравнению с этим целевым значением.
Для реализации нужно сделать следующие шаги:
- Выберите ячейку и перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
- Затем выберите третий вариант из «Применить правило к» и выберите «Форматировать все ячейки на основании их значений» из типа правила.
- Теперь в описании правила выберите «Гистограмма», в раскрывающемся меню «Тип» выберите число для максимального и минимального значений.
- В поле минимального значения введите 0, а в поле максимального значения введите ссылку на ячейку D4.
- В конце укажите цвета заливки и нажмите кнопку «ОК».
4. Применение условного форматирования к промежуточным итогам сводной таблицы.
В приведенной ниже сводной таблице указана сумма продаж за разные месяцы, кварталы и промежуточные итоги за каждый квартал. Задача: нужно применить условное форматирование к промежуточным итогам вместо значений месяца.
Действуем по шагам:
- Выберите ячейку и перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
- Затем выберите третий вариант из «Применить правило к» и выберите «Форматировать все ячейки на основании их значений» из типа правила.
- В описании правила выберите «Наборы значков» и стиль значков.
- Теперь в правиле отображения значков используйте процент для применения условного форматирования к промежуточным итогам.
- В конце нажмите ОК.
Когда вы применяете эти наборы значков для промежуточных итогов, они будут рассматривать самое высокое значение как 100%. Ячейки со значениями выше 67% получат зеленый значок, ячейки со значениями от 33% до 67% получат желтый значок, а другие ячейки ниже — красный значок.
Вы можете изменить значки и процентные значения в соответствии с вашими потребностями.
5. Применение условное форматирование к пустым ячейкам в сводной таблице
Лучший способ выделить пустые ячейки — использовать условное форматирование, используя его в сводной таблице.
В приведенной ниже сводной таблице есть пустые ячейки, и нам необходимо применить условное форматирование к тем ячейкам, где нет продажи.
- Нажмите на «Создать правило» в условном форматировании и выберите третий вариант в «Применить правило к».
- Выберите тип правила «Форматировать только ячейки, которые содержат» и выберите «Пустые» в описании правила.
- Определите цвет ячейки для форматирования и нажмите ОК.
Условное форматирование из сводной таблицы Excel
В данном примере мы будем использовать данные из сводной таблицы в качестве критерия для условного форматирования значений Excel. Эта статья является продолжением для примера: Форматирование сводной таблицы Excel для планирования продаж.
Подготовка плана продаж для условного форматирования
Создадим лист под названием «Шаблон анализа» как указано ниже на рисунке:
Важно чтобы в диапазоне A2:A5 находились имена тех самых клиентов, а в диапазоне B1:F1 – наименование тех самых товаров. В диапазон ячеек B6:G6 вводим формулы: =СУММ(B2:B5). А диапазон G2:G5 заполняется формулами: =СУММ(B2:F2).
Примечание. Для быстрого заполнения указанных выше диапазонов ячеек формулами удобно использовать комбинацию клавиш CTRL+Enter. Сначала выделяем диапазон B12:F12 так чтобы активной осталась ячейка B12 (то есть с нее начинаем выделять диапазон). Нажимаем клавишу F2 вводим формулу =СУММ(B2:B11) и нажимаем комбинацию горячих клавиш CTRL+Enter. Благодаря этому выделенный диапазон сам автоматически заполняется формулами и подставляет нужные ссылки в параметрах функции СУММ. Аналогично выполняем эти действия для автоматического заполнения формулами диапазона G2:G11.
Таблица для планирования продаж будет рассчитана на рост +5% по отношению к предыдущему году. Поэтому ее значения будут увеличены на 5%. Для того чтобы комфортно корректировать план следует записать предполагаемый уровень роста в отельную ячейку и присвоить ей имя. Ссылаясь в формулах на имя этой ячейки, мы можем изменять параметры плана с 5% на любой другой уровень роста. Для этого в ячейку H1 введем текст «Уровень роста», а в ячейку H2 вводим 5%. После чего не убирая курсор с ячейки H2 выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя» и в поле «Имя:» введите значение «урРост» (или просто введите слово «урРост» в поле имен при активной ячейке H2).
Так как данный лист будет содержат дополнительные анализы, то желательно убрать сетку с листа. Для этого снимите галочку с опции: «ВИД»-«Показ»-«Сетка»:
Шаблон для аналитического планирования продаж – готов.
Загрузка данных для шаблона анализа
Теперь подготовим данные:
- На новый лист скопируйте данные из таблицы ниже во фрейме:
- На основе этих исходных данных создадим сводную таблицу: «ВСТАВКА»-«Сводная таблица»
- Сразу после создания нового листа со сводной таблицей присваиваем новые имена и для листа, и для сводной таблицы. Чтобы переименовать лист щелкните правой кнопкой мышки по ярлычку нового листа и выберите из контекстного меню опцию «Переименовать». Потом введите новое имя «Сводный отчет» и нажмите клавишу Enter для подтверждения переименования листа.
- Чтобы наша сводная таблица отображала нужные нам данные в соответственном порядке, упорядочиваем значения в полях:
Параметры полей значений в сводной таблице:
- в полю СТРОКИ – значения Клиент и Год;
- в полю КОЛОННЫ – значения Товар;
- в полю ЗНАЧЕНИЯ – Количество.
Автоматически созданная сводная таблица имеет весьма нечитабельный вид и очень плохо воспринимается для визуального анализа данных. При желании ее можно отформатировать сводную таблицу. В результате получаем примерно такую картинку:
Теперь, когда данные подготовлены возвращаемся на шаблон анализа. Автоматически заполним табличную часть, сложной формулой которая ссылается на имя «Рост» и сводную таблицу на листе «Сводный отчет». Предварительно выделите диапазон табличной части B2:F11 так чтобы активной была ячейка B2. Потом нажмите клавишу F2 и введите формулу:
После ввода формулы нажмите комбинацию клавиш CTRL+Enter.
Данная формула использует функцию =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(). Название данной функции говорит само за себя. В первом аргументе указывается поле для исходных данных (КОЛИЧЕСВТО). Во втором аргументе следует указать любой диапазон сводной таблицы (‘Сводный отчет’!$A$3). Третий аргумент содержит в себе пару поле/элемент которая описывает данные запроса (например, «Год»;2014). В данном примере год товара и клиента должен быть тот-же 2014. Выбор клиента и товара будет постоянно изменяться – соответственно. Поэтому следует их параметризировать смешанными ссылками: $A2 – для клиента и B$1– для товара.
В данном шаблоне изначально было запроектировано, что бюджетирование будет учитывать рост продаж для следующего года +5%. Поэтому в формуле мы умножаем функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() на (1+урРост). Ведь рост — это имя ячейки, которая в данный момент содержит значение 5%. Вся эта формула помещается внутрь функции =ЕСЛИОШИБКА(). Благодаря ей все ошибочные результаты вычисления будут заменятся на значение 0. Например, если определенный клиент не покупал определенный товар в 2014 году, тогда формула возвращает значение ошибки #ЗНАЧ!. Но благодаря функции ЕСЛИОШИБКА мы просто получим 0.
Шаблон анализа с условным форматированием
На последнем этапе создания таблицы планирования бюджета продаж мы сконструируем механизм, который проинформирует пользователя о:
- Изменено значение продаж в соответствии с настройками (рост продаж +5% или больше).
- Изменено значение продаж на меньше установленного роста 5%.
Для первого условия формат значений будет изменен на зеленый шрифт, а для второго – красный. В обоих случаях будем применять условное форматирование чтобы экспонировать цветом изменения. Для этого:
- Выделите диапазон табличной части B2:F11 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
- В появившемся окне «Создание правила форматирования» активируйте опцию «Использовать формулу для определения форматируемых ячеек».
- В поле ввода вводим формулу: ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Количество»;’Сводный отчет’!$A$3;»Клиент»;$A5;»Товар»;E$1;»Год»;2014)*(1+урРост)’ >
- Нажмите на кнопку «Формат», чтобы задать зеленый цвет для шрифта значений ячеек.
Теперь создаем второе правило для этого же диапазона табличной части. Выполняем все те же действия, только в третьем пункте вводим другую формулу:
А в четвертом пункте указываем красный цвет шрифта формата ячеек. Для предварительной проверки откройте «Диспетчере правил». Для этого выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»
В результате получаем эффективный конструктор для планирования бюджета продаж. Теперь мы видим, что в ячейке B2 слишком маленькое значение и в ячейке C2 соответственно:
В ячейке H2 можем изменять значения (например, вместо 5% вводим 7%) и после нажатия клавиши Enter сразу получаем новый отчет для анализа.
Все описанные таблицы и формулы можно рассмотреть более детально в готовом примере скачав ниже по ссылке файл Excel.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры