MS Excel. Консолідація даних

Консолідація в MS Excel передбачає узагальнення однорідних даних з різних джерел. При консолідації на основі значень декількох комірок формується значення результуючої комірки. В MS Excel є два основних методи консолідації даних – консолідація за фізичним розміщенням комірок і консолідація за заголовками.

При консолідації за фізичним розміщенням вказуються тільки адреси комірок, що містять вихідні дані. В цьому випадку у всіх вихідних діапазонах дані повинні бути розміщені в однаковому порядку.

Для консолідації за фізичним розміщенням потрібно виділити діапазон, в який повинні бути поміщені консолідовані дані, і виконати команду Data→Consolidate / Данные→Консолидация (Дані→ Консолідація). В діалоговому вікні Consolidate / Консолидация (Консолідація) у списку Function / Функция (Функція) вибирається функція, за допомогою якої буде проводитися консолідація. В полі Reference / Ссылка (Посилання) цього ж діалогового вікна поступово вводяться вихідні діапазони. При цьому введення посилання на кожний діапазон потрібно підтверджувати натисканням на кнопку Add / Добавить (Додати). Всі введені посилання з’являться у списку All references / Список диапазонов (Всі посилання).

В MS Excel для консолідації використовуються наступні функції: Sum / Сумма, Count / Кол-во значений, Average / Среднее, Max / Максимум, Min / Минимум, Product / Произведение, Count Nums / Количество чисел, StdDev / Смещённое отклонение, StdDevp / Несмещённое отклонение, Var / Смещённая дисперсия, Varp / Несмещённая дисперсия. Опис цих функцій можна знайти у діалоговому вікні Paste function / Мастер функций (Конструктор функцій), що викликається командою Insert→ Function / Вставка→Функция (Вставка→Функція).

При консолідації за заголовками вихідні комірки ідентифікуються не за номером, а за заголовками. Тому вони можуть бути розміщені по-різному в різних вихідних листах. Консолідація за заголовками проводиться подібно як консолідація за фізичним розміщенням за допомогою команди Data→Consolidate / Данные →Консолидация (Дані→Консолідація). В полі Reference / Ссылка (Посилання) діалогового вікна Consolidate / Консолидация (Консолідація)послідовно вводяться посилання на вихідні діапазони, що повинні містити заголовки, у групі Use labels in / Использовать в качестве имён (Використати в ролі імен) потрібно вказати, де розмішені заголовки. Для використання в ролі заголовків комірок верхнього рядка діапазону встановлюється опція Top row / Подписи верхней строки (Підписи верхнього рядка). Для використання в ролі заголовків комірок лівого стовпця встановлюється опція Left column / Значения левого столбца (Значення лівого стовпчика).

При виконанні консолідації за допомогою команди Data→, Consolidate / Данные→Консолидация (Дані→Консолідація) діапазон консолідації зразу заповнюється підсумковими значеннями. Обчислення проводяться тільки один раз, після чого консолідовані дані ніяк не залежать від вихідних. Для того щоб консолідовані дані були зв’язані з вихідними даними і обновлювалися при зміні останніх, в діалоговому вікні Consolidate / Консолидация (Консолідація) потрібно встановити опцію Create links to source data / Создавать связи с исходными данными (Створювати зв’язки з вихідними даними). При цьому на робочому листі буде створена структура, в якій детальними даними будуть вихідні дані, а підсумковими – результати консолідації. Приховування всіх вихідних даних здійснюється натисканням на кнопку з номером 1 в лівому верхньому куті робочого поля.

Натискання на кнопку з номером 2 приводить до відображення всіх вихідних даних. Кнопки зі знаками “+” і”–“ призначені відповідно для показу і приховування окремих вихідних даних.

Структуру можна також створити за допомогою команди Data→Group and Outline→Group / Данные→Группа и структура →Группировать (Дані→Група і структура→Групувати). Цю команду потрібно виконувати кожний раз після виділення рядків (стовпців), які потрібно забрати з екрана.

29. MS Excel. Групування та підбір параметру

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

ПОДБОР ПАРАМЕТРА

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

КАК ИСПОЛЬЗОВАТЬ ПОДБОР ПАРАМЕТРА (ПРИМЕР 1):

Представьте, что Вы поступаете в определенное учебное заведение. На данный момент Вами набрано 65 баллов, а необходимо минимум 70 баллов, чтобы пройти отбор. К счастью, есть последнее задание, которое способно повысить количество Ваших баллов. В данной ситуации можно воспользоваться Подбором параметра, чтобы выяснить, какой балл необходимо получить за последнее задание, чтобы поступить в учебное заведение.

На изображении ниже видно, что Ваши баллы за первые два задания (тест и письменная работа) составляют 58, 70, 72 и 60. Несмотря на то, что мы не знаем, каким будет балл за последнее задание (тестирование 3), мы можем написать формулу, которая вычислит средний балл сразу за все задания. Все, что нам необходимо, это вычислить среднее арифметическое для всех пяти оценок. Для этого введите выражение =СРЗНАЧ(B2:B6) в ячейку B7. После того как Вы примените Подбор параметра к решению этой задачи, в ячейке B6 отобразится минимальный балл, который необходимо получить, чтобы поступить в учебное заведение.

1. Выберите ячейку, значение которой необходимо получить. Каждый раз при использовании инструмента Подбор параметра, Вам необходимо выбирать ячейку, которая уже содержит формулу или функцию. В нашем случае мы выберем ячейку B7, поскольку она содержит формулу =СРЗНАЧ(B2:B6).

2. На вкладке Данные выберите команду Анализ "что если", а затем в выпадающем меню нажмите Подбор параметра.

3. Появится диалоговое окно с тремя полями:

o Установить в ячейке - ячейка, которая содержит требуемый результат. В нашем случае это ячейка B7 и мы уже выделили ее.

o Значение - требуемый результат, т.е. результат, который должен получиться в ячейке B7. В нашем примере мы введем 70, поскольку нужно набрать минимум 70 баллов, чтобы поступить.

o Изменяя значение ячейки - ячейка, куда Excel выведет результат. В нашем случае мы выберем ячейку B6, поскольку хотим узнать оценку, которую требуется получить на последнем задании.

4. Выполнив все шаги, нажмите ОК.

5. Excel вычислит результат и в диалоговом окне Результат подбора параметра сообщит решение, если оно есть. Нажмите ОК.

6. Результат появится в указанной ячейке. В нашем примере Подбор параметра установил, что требуется получить минимум 90 баллов за последнее задание, чтобы пройти дальше.

КАК ИСПОЛЬЗОВАТЬ ПОДБОР ПАРАМЕТРА (ПРИМЕР 2):

Давайте представим, что Вы планируете событие и хотите пригласить такое количество гостей, чтобы не превысить бюджет в $500. Можно воспользоваться Подбором параметра, чтобы вычислить число гостей, которое можно пригласить. В следующем примере ячейка B4 содержит формулу =B1+B2*B3, которая суммирует общую стоимость аренды помещения и стоимость приема всех гостей (цена за 1 гостя умножается на их количество).

1. Выделите ячейку, значение которой необходимо изменить. В нашем случае мы выделим ячейку B4.

1. На вкладке Данные выберите команду Анализ "что если", а затем в выпадающем меню нажмите Подбор параметра.

3. Появится диалоговое окно с тремя полями:

o Установить в ячейке - ячейка, которая содержит требуемый результат. В нашем примере ячейка B4 уже выделена.

o Значение - требуемый результат. Мы введем 500, поскольку допустимо потратить $500.

o Изменяя значение ячейки - ячейка, куда Excel выведет результат. Мы выделим ячейку B3, поскольку требуется вычислить количество гостей, которое можно пригласить, не превысив бюджет в $500.

4. Выполнив все пункты, нажмите ОК.

5. Диалоговое окно Результат подбора параметра сообщит, удалось ли найти решение. Нажмите OK.

6. Результат появится в указанной ячейке. В нашем случае Подбор параметра вычислил результат 18,62. Поскольку мы считаем количество гостей, то наш окончательный ответ должен быть целым числом. Мы можем округлить результат в большую или меньшую сторону. Округлив количество гостей в большую сторону, мы превысим заданный бюджет, значит, остановимся на 18-ти гостях.

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

ДРУГИЕ ТИПЫ АНАЛИЗА "ЧТО ЕСЛИ"

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

• Диспетчер сценариев позволяет подставлять значения сразу в несколько ячеек (до 32). Вы можете создать несколько сценариев, а затем сравнить их, не изменяя значений вручную. В следующем примере мы используем сценарии, чтобы сравнить несколько различных мест для проведения мероприятия.

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

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

• Группируем по значению
• Группируем по дате
• Группируем данные вручную
• Исправляем ошибки
• Детализация групп
• Группировка и несколько диапазонов консолидации

ГРУППИРУЕМ ПО ЗНАЧЕНИЮ

Представьте себе такую ситуацию: Вы анализируете подборку счетов, собранных в сводной таблице. Вы можете обобщать счета, объединяя их в группы по 5 – 10 или даже 100 счетов. Давайте рассмотрим пример, где идентификатор строки OrderID – это числовое поле. В данный момент каждому счёту соответствует отдельная строка, и результат получается довольно громоздким. Мы можем сгруппировать эти данные, чтобы упростить таблицу.

В этой сводной таблице каждый идентификатор OrderID представлен в отдельной строке. Такие данные можно считать обобщёнными, но нам этого недостаточно.

Для этого кликните правой кнопкой мыши по одному из OrderID данной сводной таблицы и в появившемся меню выберите Group (Группировать). Так как OrderID это числовое поле, то диалоговое окно уже будет подготовлено для численной группировки с полями Starting At (Начиная с), Ending At (По) и By (С шагом). В данной ситуации Excel предлагает Вам группировать элементы по 10 штук.

По умолчанию Excel предлагает в качестве значения Starting At (Начиная с) первый элемент OrderID: 10248. Тем не менее, Вы можете настроить точнее или создать другую группировку, установив собственное значение параметра Starting At (Начиная с). Чтобы начать с элемента 10240, введите это значение в поле Starting At (Начиная с) – Вы можете установить любое стартовое значение, даже такое, которого нет в данных.

Мы настраиваем параметры группировки данных – они будут сгруппированы по полю OrderID по десять последовательно пронумерованных заказов.

Итак, мы настроили значение Starting At (Начиная с), оставили значение Ending At (По) таким, как предложил Excel, и оставили значение By (С шагом) равным 10, поскольку оно нам подходит. Вы можете делать группы меньше или больше, изменяя это значение – например: 5, 20, 50 или 100. Теперь жмём ОК. Данные будут сгруппированы по значению OrderID в более удобные для обработки группы.

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

ГРУППИРУЕМ ПО ДАТЕ

Если данные, с которыми Вы работаете, являются датами, то Вы сможете использовать похожий приём. Чтобы сгруппировать информацию по датам, кликните правой кнопкой мыши по дате в столбце или строке Вашей сводной таблицы и выберите Group (Группировать). Вы можете выбрать шаг группировки – Seconds (Секунды), Minutes (Минуты), Hours (Часы), Days (Дни), Months (Месяцы), Quarters (Кварталы) или Years (Годы), и установить начальный и конечный момент времени. Для группировки по годам, кварталам и месяцам интервал зафиксирован равным 1, а для группировки по дням Вы можете установить собственный интервал, например, равный 7 дням, чтобы сгруппировать данные по неделям. Для этого выберите Days (Дни) как шаг группировки, установите количество дней равным 7, укажите для параметра Starting At (Начиная с) дату, которая выпадает на начало недели, и нажмите ОК. Информация будет сгруппирована по неделям.

Большой объём данных будет сгруппирован по неделям (по 7 дней), и первым днем выступит воскресенье – день недели, соответствующий дате, указанной в параметре Starting At (Начиная с).

ГРУППИРУЕМ ДАННЫЕ ВРУЧНУЮ

Не всегда набор записей можно разделить по группам с такой же лёгкостью, как последовательность чисел или дат. Иногда группа становится группой просто потому, что Вы так решили. В этом случае можно создать свою собственную группировку. Откройте лист со сводной таблицей, кликните по первому элементу данных, который нужно включить в группу и далее, с нажатой клавишей Ctrl, последовательно щелкните по всем элементам, которые также нужно включить в эту группу. Затем на вкладке PivotTable / Options (Работа со сводными таблицами / Анализ) нажмите Group Selection (Группировка по выделенному) и элементы будут добавлены в новую группу. Таким же образом Вы можете выделить другие элементы и создать еще одну группу.

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

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

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

Вы можете развернуть или свернуть группу, нажав символы плюс или минус (+/-) слева от имени группы.