Как мне … Создать сводную сравнительную таблицу в Microsoft Excel

Эта статья также доступна для загрузки TechRepublic.

Статья была первоначально опубликована 10 августа 2004 г.

Сравнение текущих результатов с результатами, зарегистрированными в прошлом, является основой принятия бизнес-решений. Часто лучший способ сообщить о таких сравнениях — это графическое представление. Возможность построения графиков в Microsoft Excel — отличный инструмент для такого рода сценариев. Но заставить этот инструмент построения графиков вести себя так, как мы хотим, можно немного вмешаться. Это была суть вопроса тион-ти в техническом Q&A TechRepublic:

«У меня 9 столбцов в Excel, скажем, столбцы A1, A2. , A3, B1, B2, B3, C1, C2, C — вот сценарий, я хочу создать гистограмму, которая отображает количество A1, A2, A3, но сумма A2 и A3 должна быть суммирована. Это как у меня отдельная полоса для A1 и отдельная полоса для A2 + A3. То же самое касается B1, B2 + B3 и C1, C2 + C3, это должно быть показано только на 1 диаграмме. Прямо сейчас мне трудно это делать, что я выбрал необходимо разделить диаграмму для A1 и A2 + A3 … «

Член TechRepublic gill_potter предложил конкретный информационный слепок, который сделает это возможно в его ответе:

«Быстрый способ — создать новую строку A4, которая представляет собой сумму A2 и A3. Затем выберите просто 1-я строка и ctrl выберите 4-ю строку и щелкните мастер диаграмм. Это создаст столбчатую диаграмму со значением A1 рядом со значением A4, а затем следующий сегмент будет значением B1 по сравнению с значение B4. Если вы хотите сравнить все значения 1 вместе и все 4 значения вместе, просто измените параметр диаграммы на данные в столбцах, а не на данные в строках «.

Ключ к Решением является добавление двух столбцов и создание четвертого столбца для хранения результатов. Часто лучший способ решить любую проблему в Excel — это разбить его по частям и решать биты, пока вы не добьетесь желаемых конечных результатов. Создание дополнительных данных даст введите-те данные, которые он/она хотел бы сравнить на гистограмме (см. рисунок A ).

Рисунок A

Пример первый

Гистограмма на рисунке A была получена из этой простой таблицы данных:

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

Настройте сценарий

Мы создадим простой сценарий для нашего примера. Допустим, мы хотим сообщить о доходах компании в агенство группа акционеров. Мы знаем, что эти акционеры хотят сравнить выручку в 2004 году с выручкой в ​​2003 и 2002 годах. Мы также знаем, что они хотят видеть сравнение за каждый квартал, а также годовые итоги.

Ключевым фактором в этом случае является количество точек данных, которые мы зафиксировали до сих пор в 2004 году. Если это только февраль, то мы сможем сравнить только два месяца; но если сейчас август, мы должны сравнить накопленную выручку за восемь месяцев и накопленную выручку за первые два месяца третьего квартала. Чтобы получить количество доступных на данный момент точек данных о доходах (месяцев) в 2004 г., мы используем функцию COUNT () (оранжевая ячейка G1cell на рисунке B ).

Рисунок B

Пример второй

По подсчитанному количеству точек данных мы можем определить, какой квартал мы сравниваем, используя оператор IF () в сочетании с AND () функция:

= ЕСЛИ (G1 3, (G1) 6, (G1 ) 9, (G1) Эта логическая формула вернет «2», если COUNT ( ) в G1 больше 3 и меньше или равно 6, что означает, что мы исследуем вторую четверть. Соответственно, он вернет «1», «3» или «4» в зависимости от четверти, обозначенной значением G1. (См. Зеленую ячейку J5 на рисунке B.)

Функция COUNT () в сочетании с функцией OFFSET () также используется для накопления общих доходов за каждый год вплоть до текущего месяца. осмотрел. Другими словами, если мы смотрим на результаты за август 2004 г., мы используем эту формулу:

 = SUM (B5: OFFSET (B5, G1-1,0)) 

Результат отображается в синих ячейках F6, G6 и H6 (Рисунок B). Эти значения представляют собой СУММ по строкам с января по август.. Итоговые данные за год важны для определения того, сколько вкладов за каждый квартал внесло в год, что является основной идеей нашего графического представления.

Фиолетовые ячейки на рисунке B — это промежуточные итоги для каждого квартала за год. каждый год. В расчетах также используется функция СМЕЩ () в Excel. Используя оператор IF (), мы можем вычислить значение для каждого квартала, как эта формула для третьего квартала 2004 года:

 = IF (J5 = 3, F6-F8-F9, OFFSET (B1  , 10,0) + OFFSET (B1,11,0) + OFFSET (B1,12,0)) 

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

 = IF (J5 = 1, F6, IF  (J5 = 2, F9, IF (J5 = 3, F10, IF (J5 = 4, F11, F6)))) 

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

Постройте диаграмму

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

Улучшение

В то время как рисунки A и B могут дать вам общее представление о том, как работает рабочий лист Excel, он не может быть таким же полезным, как реальный рабочий лист Excel. Вот почему мы делаем два примера, обсуждаемых в этой статье, доступными для загрузки. Ни один из файлов не является произведением искусства, но они действительно работают, и они должны помочь tion-thee с его Tech Q & Aquestion, или по крайней мере, дайте ему что-нибудь подумать.

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