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

Построение диаграммы Парето без каких-либо дополнительных действий возможно в Excel 2016:

• Выделить таблицу с исходными данными.
• На вкладке Вставка [Insert] в группе Диаграммы [Charts] раскрыть список Вставка статистической диаграммы и в группе Гистограмма выбрать тип Парето.

Как же строить диаграмму Парето в предыдущих версиях?

Сперва нужно подготовить таблицу с исходными данными:

• Рассчитать суммарные значение по каждому пункту (производитель, критерий, проблема и т.д.)
• Выполнить сортировку по убыванию для рассчитанных сумм
• Вычислить накопительный процент каждого пункта от общей суммы: =СУММ($C$3:C3)/СУММ($C$3:$C$18)  или=SUM($C$3:C3)/SUM($C$3:$C$18)
• Создать столбец с порогом в 80% — для отображения линии на графике, а не подрисовки ее с помощью автофигур 🙂

По полученным данным строится диаграмма:

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

ТРАНСП(Массив) [TRANSPOSE(Array)]– преобразует вертикальный диапазон в горизонтальный, или наоборот.
Массив [Array] – диапазон ячеек на листе или массив значений, который нужно транспонировать.

1. Выделить диапазон ячеек для размещения транспонированной таблицы (ячейки G2:K5).
2. Ввести с клавиатуры знак =.
3. Выбрать функцию ТРАНСП, выделить исходную таблицу (ячейки B2:E9).
4. Нажать Ctrl+Shift+Enter.

{=ТРАНСП(B2:E6)} [{=TRANSPOSE(B2:E6)}]- транспонирует диапазон ячеек В2:Е6 в выделенные ячейки.

Каждая третья задача пользователя — это получение данных из табличного массива при заданных условиях:

Наиболее часто для решения подобных задач используют функции для работы с табличными данными, например: ПОИСКПОЗ, ИНДЕКС, ДВССЫЛ, СМЕЩ и т.д.

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

1) Необходимо присвоить диапазонам имена — выделить таблицу так, чтобы в левом столбце и верхней строке были критерии, нажать клавиши CTRL+SHIFT+F3, чтобы создать имена по выделенным данным:

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

2) создать простую формулу пересечения диапазонов: ИмяДиапазона1(ПРОБЕЛ)ИмяДиапазона2

Однако, такая формула не имеет гибкости, т.к. вводить имена в этом случае нужно с клавиатуры снова и снова.

Усовершенствуем!

1) Создадим в 2-х ячейках Н4 и H5 списки для выбора критериев.

2) Напишем формулу, ссылаясь на эти ячейки: =H4 H5

Но результата в этом случае не будет. т.к. пересечение 2-х текстовых значений не может давать никакого результата. Значит, необходимо сделать так, чтобы значение, которое присутствует в ячейке Н4 (H5) становилось именем. С подобной задачей отлично справляется функция ДВССЫЛ [INDIRECT].

Таким образом, конечная формула будет: =ДВССЫЛ(H4) ДВССЫЛ(H5) или =INDIRECT(H4) INDIRECT(H5)

Теперь можно легко выбирать месяца и города в списках и результат не заставит себя ждать!