Конспект лекций по Excell. 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12. Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно
 Скачать 3.95 Mb.
|
|
Данные → Группа и структура → Удалить структуру будут удалены только элементы структуризации, без удаления промежуточ- ных и окончательных итогов. Щелчок же по кнопке Убрать все в окне диало- га Промежуточные итоги удаляет не только элементы структуризации, но и добавленные ранее итоги. На рисунке, приведенном далее, представлена таб- лица, в которой в начале, по команде Данные → Итоги, были подведены ито- ги по полю 1-й Квартал, при каждом изменении поля Поставщик, и в соответ- ствии с ними построена 3-х уровневая иерархическая структура зависимости ее строк, а затем, по команде Данные → Группа и структура → Создание структуры, была построена 2-х уровневая структура ее столбцов. 158 7.2. Консолидация данных Под консолидацией данных в Microsoft Excel понимается обобщение однородных данных из различных источников. Типичный пример консолидации – это получение обобщенных данных о работе компании на основании данных о работе ее филиалов. Ячейки, содержащие результаты консолидации, могут быть связаны илине связаны с исходными данными. В первом случае они автоматически обновляются при всяком изменении содержания исходных ячеек, и поэтому всегда отражают текущее положение дел. Во втором случае содержимое итоговых ячеек больше не зависят от ис- ходных, а лишь фиксируют положение дел на момент консолидации. В Microsoft Excel имеется две группы методов консолидации данных: 1. Консолидация по физическому расположению ячеек. 2. Консолидация по заголовкам. Эти методы различаются способом указания связи исходных ячеек с итоговыми. При консолидации по физическому расположению указываются координаты ячеек, в которых содержатся исходные и результирующие дан- ные, а при консолидации по заголовкам – используются заголовка строк и столбцов. При консолидации данных по физическому расположению ячейки во всех исходных и результирующем диапазоне данных должны быть располо- жены в одинаковом порядке. И если хотя бы в одном из них порядок ячеек бу- дет нарушен, консолидация данных по их физическому расположению даст неверные результаты. Консолидация данных по физическому расположению может быть вы- полнена несколькими различными способами: 1. С помощью непосредственного указания адресов ячеек в формулах. 2. С помощью окна диалога Специальная вставка. 3. По команде Данные → Консолидация. Консолидация данных по физическому расположению Консолидация данных по физическому расположению ячеек с помощью непосредственного указания ссылок в формулах выполняется следующим образом: 1. Активизировать одну из ячеек диапазона, в который будут записаны консо- лидированные данные, желательно, угловую. 159 2. Ввести в нее любым известным способом формулу, согласно которой на основе исходных данных будут подсчитаны консолидированные данные. 3. «Растиражировать» (например, при помощи Автозаполнения) содержимое ячейки с формулой на весь результирующий диапазон. Адреса в формуле, согласно которой вычисляются консолидированные данные, должны быть относительными, поскольку, в дальнейшем, при копи- ровании на высь результирующий диапазон они должны быть корректно пре- образованы. Ссылки в формулы можно вводить как непосредственно с клавиатуры, так и с помощью мыши – методом прямого указания адресов на рабочих листах. Поскольку в большинстве случаев исходные диапазоны данных располо- жены на рабочих листах в одних и тех же местах, то в формулах иногда очень удобно указывать «трехмерные» диапазоны. «Трехмерный» диапазон – это диа- пазон, имеющий одни и те же координаты на нескольких, подряд идущих, ра- бочих листах. Он обозначается, например, так: 'Филиал 1:Филиал 2'!C4 – адрес ячейки, расположенной в столбце C строке 4 на листах Филиал 1 и Филиал 2. «Трехмерный» диапазон методом прямого указания при помощи мыши можно обозначить следующим образом: 1. Щелчком мыши по ярлычку выделить первый рабочий лист «трехмерного» диапазона. 2. При нажатой клавише Shift щелкнуть по ярлычку последнего листа диапазона. 3. Любым известным способом, на любом из листов «трехмерного» диапазона выделить нужную ячейку, или блок ячеек. Рабочие листы, входящие в «трехмерный» диапазон обязательно долж- ны идти подряд, без пропусков. Если же листы идут не подряд, то использу- ется список – перечисление отдельных диапазонов через символ-разделитель элементов списка. Консолидация данных с помощью окна диалога Специальная вставка может выполняться только над двумя исходными диапазонами, при этом вто- рой, выходной, диапазон будет замещен результатом консолидации. Консо- лидация исходных данных в этом случае может быть выполнена только с помощью одной из следующих операций: • сложить, • вычесть, • умножить и • разделить. 160 Консолидация данных с помощью окна диалога Специальная вставка выполняется по такому алгоритму: 1. Любым известным способом скопировать в буфер обмена одни из исход- ных диапазонов. 2. Выделить второй, результирующий, диапазон. 3. Активизировать диалоговое окно Специальная вставка по одноименной команде а) из контекстного или б) основного меню Правка. 4. В раскрывшемся окне диалога Специальная вставка установить парамет- ры консолидации следующим образом: а) переключатель Вставить – в положение значение, б) переключатель Операция – в положение, соответствующее требуемой операции, в) флажок пропускать пустые ячейки – если необходимо исключить из процесса консолидации пустые ячейки, г) флажок транспонировать – если находящийся в буфере обмена диапа- зон перед консолидацией необходимо транспонировать, д) нажать кнопку OK. В результате на месте второго диапазона будут записаны результаты консоли- дации данных. Консолидация данных по физическому расположению ячеек с помощью команды Данные → Консолидация выполняется по такому алгоритму: 1. Выделить левую верхнюю ячейку диапазона, в который будут записаны ре- зультаты консолидации. Можно, конечно, выделить и весь результирую- щий диапазон. 2. Выполнить команду Данные → Консолидация. 3. В раскрывшемся окне диалога Консолидация установить параметры кон- солидации данных следующим образом: 161 Рис. 7.3 – Консолидация данных а) В раскрывающемся списке Функция – указать одну из приведенных да- лее функций, в соответствии с которой будет производиться консолидация: - Сумма, - Количество, - Среднее, - Максимум, - Минимум, - Произведение, - Количество чисел, - Смещенное отклонение, - Несмещенное отклонение, - Смещенная дисперсия, - Несмещенная дисперсия. Состав элементов этого списка точно такой же, как и приведенного вы- ше списка Операция окна диалога Промежуточные итоги, а самую полную информацию о каждой из функций можно получить в разделе «Итоговые функции для анализа данных» справочной системы MExcel. б) В поле Ссылка – указать адрес первого непрерывного исходного диапа- зона ячеек. Значение можно ввести либо непосредственно с клавиатуры, либо с помощью мыши – методом прямого указания. Для указания ссыл- ки на ячейки, расположенные в другой закрытой рабочей книге, необ- ходимо нажать кнопку Обзор, и в раскрывшемся одноименном окне диалога, аналогичному окну диалога открытия документа, указать рабо- чую книгу, содержащую необходимые данные. При этом в поле Ссылка появится лишь полное имя файла рабочей книги, имя же рабочего листа, и адреса ячеек – необходимо добавить вручную. 162 в) Нажать кнопку Добавить. В результате выбранный в предыдущем пункте исходный диапазон, будет добавлен в Список диапазонов. г) Повторить два предыдущих пункта б) и в) для всех диапазонов источни- ков данных. При этом, если некоторый диапазон был введен неправиль- но, или его больше ненужно учитывать в обобщенных данных, то такой диапазон можно удалить из Списка диапазонов по нажатию кнопки Удалить, предварительно выделив последний. д) В группе Использовать в качестве имен сбросить все флажки, чтобы консолидация выполнялась не по заголовкам строк и столбцов, а по фи- зическому расположению ячеек. е) Нажать кнопку OK. В результате в указанном месте рабочего листа появятся консолидиро- ванные данные. На рисунке показан пример консолидации данных по их фи- зическому расположению для некоторой компании по трем ее подразделениям. С целью упрощения все показатели по всем трем филиалам компании одина- ковы, поскольку были получены методом обыкновенного копирования, а ре- зультат консолидации – простое умножение на 3 любого из этих показателей. В этом способе консолидации как результирующий, так и исходные, диа- пазоны ячеек, могут иметь произвольные размеры. При этом если результи- рующий диапазон был указан вполне определенных размеров, то консолидиро- ваны будут ячейки только в пределах этого диапазона, в не зависимости от размеров исходных диапазонов. Если же была указана только единственная ячейка из этого диапазона, то количество строк и столбцов в нем будет рав- но наибольшему их количеству среди всех исходных диапазонов. Консолидация данных по заголовкам строк и столбцов Консолидация данных по заголовкам строк и столбцов более удобна, чем по физическому расположению. В этом случае исходные ячейки иденти- фицируются не по их адресам, а по заголовкам, и поэтому они могут быть 163 расположены по-разному в разных исходных диапазонах, т.е. можно исполь- зовать разный порядок строк и столбцов. Некоторые исходные диапазоны при этом могут даже содержать столбцы или строки, которые отсутствуют в других диапазонах. Консолидация данных по заголовкам строк и столбцов осуществляется следующим образом: 1. Для каждой ячейки из результирующего диапазона консолидации опреде- ляются названия строки и столбца, в которых она расположена. 2. В каждом исходном диапазоне ищется ячейка, находящаяся в строке и столбце с такими же названиями. Отобранные таким образом ячейки ста- новятся исходными для результирующей ячейки. 3. Если названия строк не определены, но определены названия столбцов, то сами столбцы консолидируются по именам, а ячейки в столбцах – по их расположению. Аналогичным образом выполняется консолидация столб- цов при наличии названий строк и отсутствии названий столбцов. Пошаговый алгоритм консолидации данных по заголовкам строк и столбцов практически полностью совпадает с алгоритмом консолидация данных по их физическому расположению, который реализуется с помощью команды Данные → Консолидация. Разницу при этом составляют лишь два момента: 1. Диапазоны данных должны обязательно включать заголовки строк и столбцов. При этом если результирующий диапазон указан лишь одной ячейкой, то в него будут включены все строки и столбцы с названиями, имеющимися в каждом исходном диапазоне. Если же результирующий диа- пазон включает названия строк и столбцов, то в консолидации из исходных диапазонов будут участвовать только строки и столбцы с соответствующи- ми названиями. 2. В окне диалога Консолидация (рис. 7.3) в группе Использовать в качестве имен должны быть выставлены флажки, которые указывают где расположены заголовки: • подписи верхней строки – чтобы использовать в качестве заголовка верхнюю строку каждого диапазона. • значения левого столбца – чтобы использовать в качестве заголовка левый столбец каждого диапазона. Если выставлены оба флажка одновременно, тогда диапазон будет воспри- ниматься как таблица, в которой именованы и строки и столбцы. 164 7.3. Связывание консолидированных данных с исходными В результате выполнении консолидации данных с помощью команды Данные → Консолидация результирующий диапазон сразу заполняется ито- говыми данными. При этом вычисления по умолчанию проводятся только один раз – в момент выполнения команды, и далее консолидированные дан- ные никак не зависят от исходных. Однако иногда возникают задачи, которые требуют, чтобы результаты консолидации были связаны с исходными данны- ми, и синхронно изменялись с ними. В Microsoft Excel имеется несколько способов выполнения консолида- ции таким образом, чтобы результирующие данные были связаны с исходными, и обновлялись при каждом изменении последних: 1. Один из них состоит в непосредственном указании адресов исходных диа- пазонов данных в формулах, которые записаны в ячейках результирующего диапазона. 2. Другой способ заключается в выставлении флажка Создать связи с исходными данными в окне диалога Консолидация (рис. 7.3). При этом в результате выполнения консолидации, как по физическому расположению ячеек, так и по заголовкам, на рабочем листе, где будут рас- полагаться консолидированные данные, создается структура, в которой в качестве подробных данных будут присутствовать исходные данные, а в качестве итогов – результаты консолидации. На рис. 7.4 показан результат такой консолидации данных по трем под- разделениям некоторой условной компании. Рис. 7.4 – Консолидация связанных данных 165 ЛЕКЦИЯ № 8. СВОДНЫЕ ТАБЛИЦЫ Обычные таблицы Microsoft Excel могут быть только плоскими – дву- мерными. Поэтому некоторые элементы данных в них, наверняка, будут по- вторяться. Например, в приведенной на рис. 8.1 таблице о продажах товаров в различных филиалах, разными продавцами, некоторой условной торговой компании значительная часть информации дублируется. Рис. 8.1 – Исходная таблица Microsoft Excel содержит широкий набор средств анализа и обобщения результатов обработки данных. Для простого анализа очень хорошо подходят, например, различные методы подведения промежуточных итогов, использова- ние фильтров. Однако их становится недостаточно, если необходимо проана- лизировать большое количество данных или использовать более сложные средства анализа. Для этих целей используются сводные таблицы, которые являются в Microsoft Excel основным средством анализа. Сводная таблица представляет собой интерактивную таблицу, предна- значенную для анализа и трансформации данных Microsoft Excel. С ее помо- щью можно отобразить практически любой «срез» данных и получить практи- чески любые итоги. Она во многом объединяет возможности таких обобщаю- щих инструментов, как Сортировка, Фильтрация, Группировка, Итоги и Консолидация. Сводная таблица всегда связана с одним, или несколькими источниками данных. Сама же она предназначена только для отображения информации, а изменения необходимо вносить в исходные данные. При этом можно произ- вольным образом изменять форматирование сводной таблицы, а также выби- рать различные параметры вычисления итоговых и промежуточных данных. Анализ данных с помощью сводной таблицы выполняется в соответст- вии со структурной схемой, представленной на рис. 8.2. 166 Рис. 8.2 – Структурная схема анализа данных при помощи сводных таблиц При этом вся информация, подлежащая анализу с помощью сводной таблицы, вне зависимости от источника данных, вначале обязательно полно- стью копируется в так называемую кэш-память – участок служебной памяти Microsoft Excel. Сводная же таблица отображает лишь содержимое кэш- памяти, а не исходного источника данных. Поэтому при изменении инфор- мации в источнике данных содержимое сводной таблицы изменяться не будет – для ее обновления необходимо обновить содержание кэш-память. То есть сводная таблице не является динамической таблицей, которая автомати- чески обновляется при всяком изменении исходных данных. Организация же самой сводной таблицы приведена на рис. 8.3. Вдоль каждой из ее осей располагаются области строк, столбцов и страниц, соответ- ственно. То есть сводная таблица может быть 3-х мерной, и в этом смысле ее можно сравнить со стопкой листов – всегда виден только верхний лист, но в любой момент наверх можно вытащить любой из них. Рис. 8.3 – Организация сводной таблицы Область строк служит для группировки информации сводной таблицы по строкам, а область столбцов – для группировки по столбцам. Область страниц позволяет добавить в таблицу еще одно (третье) измерение, т.е. соз- Ось страниц О с ь с тр о к Ось столбцов Область данных Кэш - память Список или база данных Microsoft Excel Внешний источник дан - ных Несколько диапазонов консолидации Другая сводная таблица или сводная диаграмма Источники данных Область дан - ных Область столбцов О б л а с ть с тр о к Область страниц Сводная таблица 167 дать из плоских исходных данных таблицу, состоящую из нескольких страниц. В области данных отображаются, собственно, сводные (обобщенные) данные. При расположении нескольких полей вдоль одной любой из ее осей они образуют некоторую иерархическую структуру – т.е. поле, находящееся ближе к началу осей, является «старшим», по отношению к следующим – т.е. одному значению «старшего» поля будут соответствовать все значения «младших» полей. Вся гибкость и мощь сводных таблиц состоит в том, что с целью дости- жения максимальной наглядности представления данных в процессе их анали- за, положение любого элемента можно легко менять самым произвольным об- разом, перемещая их из одной области в другую. Итак, сводная таблица может быть создана на основе исходных данных, находящихся в любом из перечисленных ниже источников: •••• В списке, таблице или базе данных Microsoft Excel. •••• Во внешней базе данных. •••• В интервалах консолидации Microsoft Excel. •••• В другой сводной таблице этой же рабочей книги. |