excel_1_mai (800066), страница 16
Текст из файла (страница 16)
Промежуточные итоги разрушают структуру таблицы и не позволяют эффективно использовать такую таблицу в дальнейшем.КонсолидацияЧасто создаются однотипные таблицы, например, отчеты за месяц по продажам товаров вмагазинах одной сети, или расходные таблицы на приобретение товаров на дочерних фирмах втечение года. Сложности могут возникнуть, когда ежемесячные сведения нужно собрать в одинотчет для подведения общих итогов, например, за год.
Процедура объединения нескольких таблиц в одну называется консолидацией и выполняется на вкладке Данные в группе Работа сданными кнопкой Консолидация.Процедура консолидации может быть выполнена двумя способами:1. Объединением по позициям. Выполняется только для таблиц, имеющих одинаковуюструктуру построения. Преимущество этого способа состоит в том, что он применим дажедля таблиц с вычисленными промежуточными результатами.2. Объединением по категориям. При этом способе объединение данных происходит в зависимости от заголовка строки или столбца с последовательным дополнением результирующей таблицы. Промежуточные итоги в таких таблицах вычислять не целесообразно.В результате процедуры объединения таблиц на новом листе составляется новая таблица,которая будет содержать результаты вычислений по формуле консолидации и свернутые группы исходных данных.Проверка формулТрадиционно необходимость проверки формул распадается на две подзадачи.
Первая —это обнаружение и устранение ошибок. Вторая — это способ чтения формул, например, для того, чтобы понять, каким образом построен алгоритм вычислений в таблице.Ошибки в формулах порой появляются в самых неожиданных местах. Часто они бываютрезультатом неверных действий, а иногда могут быть результатом вычислений по функциям.Например, функция МОДА() в случае, если среди заданного набора числовых значений не находит повторяющихся значений, то возвращает сообщение об ошибке #Н/Д (нет доступногозначения). Забытые, распределенные по таблице ошибки, идентифицируются на вкладке Формулы в группе Зависимости формул кнопкой Проверка наличия ошибок.
Диалоговое окноКонтроль ошибок предлагает набор возможных дальнейших действий.В зависимости от сложности формул рано или поздно можно столкнуться с необходимостью проверки порядка вычислений или корректности записи аргументов функций. Безусловно,двойной щелчок на ячейке результата покажет формулу в строке формул и выделит разноцветными рамками ячейки с операндами, но в таких записях достаточно сложно разбираться. Дляаудита неработающих формул, когда нужно понаблюдать за процессом вычисления формулы,или понять, где формула переходит к неверным действиям, можно воспользоваться вкладкойФормулы, группа Зависимости формул, кнопка Вычислить формулу.
Диалоговое окно Вычисление формулы выполнит по шагам любую формулу, и на каждом промежуточном этапепокажет результат выполненного действия.Для того, чтобы посмотреть, в каких ячейках таблицы стоят формулы, существует специальный режим отображения формул вкладка Формулы, группа Зависимости формул, кнопкаПоказать формулы.51Учебное пособиеПрограммирование в электронных таблицах Excel 2010.Базовые функцииExcel обладает еще одним оригинальным инструментом исследования анатомии сложныхформул: трассировкой формул.
Для любой ячейки можно выявить связи с ячейками, влияющими на ее значение, или найти ячейки, зависящие от ее значения. Такие связи можно отобразитьграфически в виде голубых стрелок на рабочем листе, выбрав на вкладке Формулы в группеЗависимости формул кнопку Влияющие ячейки или кнопку Зависимые ячейки.
Убрать следы трассировки формул на листе поможет кнопка Убрать стрелки.Свойства документаПоскольку таблицы составляются не только для отдельных расчетов, но и для обмена информацией, то, полученные в результате обработки данных, документы должны иметь правильную, идентифицирующую их, информацию. Чтобы убедиться, что выходные параметрыфайла указаны правильно, следует на вкладке Файл открыть раздел Свойства и нажать кнопкуПоказать область сведений о документе. При этом откроется панель Свойства документа,которая располагается над рабочим листом. Следует не только заполнять информационные поля панели, но и уточнять другие важные свойства документа, такие как: Общие, Статистикаили Состав в разделе Свойства кнопкой Дополнительные свойства.
Дополнительная информация позволит выявить все и даже скрытые листы в рабочей книге, а также отследить последовательность внесения исправлений в документ.Задание 6.1. Группировка данных и подведение промежуточных итогов.Варианты задания 6.1№Тема1.2.3.4.5.6.7.8.9.10.11.12.ГородскаяАпексБазоваяАспектТехническаяБумажнаяТрекЭркерМодульнаяИзящнаяНачальнаяОткрытаяОперация промежуточных итоговСреднееМаксимумСуммаМинимумСреднееМаксимумСуммаМинимумСреднееМаксимумСуммаМинимумПроверка формулТип защитыЗависимые ячейкиВлияющие ячейкиЗависимые ячейкиВлияющие ячейкиЗависимые ячейкиВлияющие ячейкиЗависимые ячейкиВлияющие ячейкиЗависимые ячейкиВлияющие ячейкиЗависимые ячейкиВлияющие ячейкиПароль на чтениеЦифровая подписьЗашифровать документОграничить разрешенияПароль на измененияПароль на чтениеЦифровая подписьЗашифровать документОграничить разрешенияПароль на измененияЗашифровать документПароль на чтениеТип диаграммыКольцеваяКруговаяКольцеваяКруговаяКольцеваяКруговаяКольцеваяКруговаяКольцеваяКруговаяКольцеваяКруговая1.
Установить имя пользователя.2. Для выполнения этого задания нужно взять шаблон таблицы, оформленный в Задании2.1. Установить горизонтальное расположение текста во всей таблице. В рабочую таблицу ненужно включать последние итоговые строки шаблона. В начало таблицы добавить новыйстолбец Категории и указать категории перечисленных товаров. Оформить таблицу в палитре заданной вариантом темы. Указать имя листа Основной.
Окрасить ярлычок листа.3. В столбце Часто употребляемая цена указать цену на продукты в различных городах.В случае, когда во всех городах указаны уникальные цены, сообщение об ошибке заменитьна информационное сообщение, например, Нет повторяющихся цен. Это действие можновыполнить с помощью функции ЕСЛИОШИБКА(значение; значение при ошибке), которуюследует совместить с функцией МОДА().4.
Для произвольной ячейки таблицы установить трассировку формул, в зависимости отзаданного вариантом способа проверки формул.52Учебное пособиеПрограммирование в электронных таблицах Excel 2010.Базовые функции5. Скопировать исходную таблицу на новый лист. Указать имя листа Группировки. Установить стили ячеек таблицы.6. Сортировать данные таблицы по категориям. Использовать два уровня сортировки так,чтобы продукты внутри определенных категорий также располагались по алфавиту. Операцию удобно выполнить на вкладке Данные в группе Сортировка и фильтр кнопкой Сортировка. В окне Сортировать по указать название столбца, в котором следует выполнитьдействие.
Второй уровень сортировки включить кнопкой Добавить уровень.7. Выделить все категории товаров. Все товары объединить в большую группу: вкладкаДанные, группа Структура, кнопка Группировать, команда Группировать.8. Выделить наибольшую по количеству наименований группу товаров, например, Продукты. Эти товары объединить во вложенную группу: вкладка Данные, группа Структура,кнопка Группировать, команда Группировать.9. Создать вложенную группу в группе Продукты, например из товаров, название которых начинается на букву ″М″. Создать вложенную группу в группе Продукты, например товары, название которых начинается на букву ″С″.10.
Создать группу, которая объединит данные, например, двух первых городов таблицы.Создать группу, которая объединит четыре последних столбца таблицы.11. Создать вложенную группу, которая объединит только столбцы статистики: Максимальная цена, Минимальная цена, Средняя цена. Эта группа будет вложенной, по отношениюк группе из четырех столбцов. Кнопками I + I и I − I произвольно свернуть или развернутьнекоторые группы.12. Скопировать таблицу с листа Основной на новый лист.
Указать имя листа Итоги.Сортировать данные таблицы по категориям товаров.13. Подвести промежуточные и окончательные итоги затрат на покупку групп товаров вкаждом городе по категориям товаров. Для этого следует выделить таблицу. Далее, на вкладке Данные в группе Структура кнопкой Промежуточные итоги установить заданную вариантом операцию. Флажками отметить названия столбцов, в которых следует подводитьпромежуточные итоги.
Для того чтобы итоговая информация размещалась строго в концекаждой группы, следует отметить флажок Итоги под данными. Строки с промежуточнымии окончательными итогами оформить контрастной заливкой. При подведении промежуточных итогов автоматически устанавливается группировка по выбранному критерию.14. Настроить таблицу так, чтобы она работала только с видимыми строками таблицы.Для этого в последней строке таблицы в функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер функции, диапазон) к записанному номеру функции нужно добавить число 100.15.
Скрыть любую группу и посмотреть, как изменяются итоговые значения.16. Скопировать исходную таблицу на лист Структура и выполнить автоматическоеструктурирование: вкладка Данные, группа Структура, кнопка Группировать, командаСоздание структуры. Поскольку все исходные данные участвуют в формировании результатов, то получилась не очень наглядная структура. Скрыть некоторые строки.17. Скрыть некоторые листы книги.
Сохранить книгу.Задание 6.2. Комбинирование данных из нескольких таблиц.Построить таблицу, которая объединяет три таблицы с одинаковой структурой и абсолютно идентичными названиями столбцов, например, отчетные таблицы по покупки товароводного ассортимента за некоторые календарные периоды.1. Для выполнения этого задания нужно скопировать таблицу с листа Основной.
Сделатьтри копии таблицы на последовательных листах. Указать имена задействованных листов, на53Учебное пособиеПрограммирование в электронных таблицах Excel 2010.Базовые функциипример, указать номер года или название месяца, когда, якобы, была создана таблица. В таблицах по своему усмотрению изменить любые исходные данные. Измененные данные следует выделить цветом фона.2. В каждой таблице выполнить двухуровневую сортировку по столбам Категории и Товар. На вкладке Данные в группе Структура кнопкой Промежуточные итоги установитьоперацию Сумма и подвести итоги. Эти три таблицы будут исходными данными для процедуры консолидации. Характерной особенностью этих таблиц является одинаковое расположение данных.3.
Еще одну копию таблицы листа Основной нужно переместить на новый лист Объединение по позициям. На этом листе необходимо сохранить только дизайн и расположение элементов, поэтому можно удалить все цифровые данные. В таблице должны остаться тольконаименования категорий и товаров, названия городов и названия в итоговых строках.4. Выполнить объединение трех таблиц. Для этого выбрать верхний левый угол свободного пространства будущей таблицы на листе Объединение по позициям. На вкладке Данные вгруппе Работа с данными, выбрать кнопку Консолидация. В диалоговом окне Консолидация следует выбрать операцию Сумма для подсчета итогов. Добавить последовательноссылки на три подготовленные таблицы, для чего в каждой из таблиц нужно указать диапазоны данных.
При выделении диапазона очередной таблицы нужно указывать только тучасть, которая содержит данные и формулы, но не названия строк или столбцов. Для того,чтобы данные в объединенной таблице изменялись по мере изменения в таблицахисточниках, следует отметить пункт Создавать связи с исходными данными.5. Слева от таблицы-консолидации располагается поле группы с характерными знакамиплюс I + I или минус I − I .
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
