Структурирование таблиц 1
Что такое Структурирование? При работе с большими таблицами часто приходится временно закрывать или открывать вложенные друг в друга части таблицы на разных иерархических уровнях. Для этих целей выполняется структурирование таблицы — группирование строк и столбцов. !!! Прежде чем структурировать таблицу, необходимо произвести сортировку записей, тем самым косвенно выделяя необходимые группы. Структурирование выполняется с помощью команды меню Данные-Группировать, а затем выбирается конкретный способ — ручной (далее Группировать) или автоматический (далее Создание структуры). 2
Ручной способ структурирования При ручном способе структурирования необходимо предварительно выделить область — смежные строки или столбцы. n Ввести команду Данные-Группировать, которая вызывает окно «Группирование» для указания варианта группировки — по строкам или столбцам. n В результате создается структура таблицы со следующими элементами слева и/или сверху на служебном поле: n 3
Структура таблицы 1 4 2 линии уровней структуры, показывающие соответствующие группы иерархического уровня; 2. кнопка <плюс>—для раскрытия групп структурированной таблицы; 3. кнопка <минус> — для 3 скрытия групп структурированной таблицы; 4. кнопки <номера уровней 1, 2, 3, 4, 5> — для открытия или скрытия соответствующего уровня. 1. 4
Пример структурированной таблицы Для открытия (закрытия) иерархической ветви нажимаются кнопки плюс или, минус Для закрытия определенного уровня иерархии необходимо щелкнуть на номере уровня кнопки с номерами 1, 2, 3 и т. д. Для открытия определенного уровня иерархии необходимо щелкнуть на номере уровня кнопки +1. 5
Создание вложенного уровня Если внутри структурной части выделить группу и выполнить команду Данные-Группировать, будет создан вложенный структурный элемент нижнего иерархического уровня. При выделении группы, охватывающей другие структурные части таблицы, и выполнении команды Данные-Группировать создается структурный элемент верхнего иерархического уровня. Максимальное число уровней— 8. 6
Отмена структурного компонента n n Для отмены одного структурного компонента производится выделение области и выполняется команда Данные-Разгруппировать. Для отмены всех структурных компонентов таблицы — команда Данные- Разгруппировать-Удалить структуру. 7
Автоструктурирование n n n Автоструктурирование выполняется для таблиц, содержащих формулы, которые ссылаются на ячейки, расположенные выше и (или) левее результирующих ячеек, образуя с ними смежную сплошную область. После ввода в таблицу исходных данных и формул курсор устанавливается в произвольную ячейку списка и выполняется команда Данные- Создать структуру. Все структурные части таблицы создаются автоматически. 8
Пример n n n По каждому работнику по формулам рассчитывается выручка. Кроме того, в строке Итого рассчитывается с помощью функции СУММ общая сумма. Порядок следования исходных данных и результатов (итогов) — слева направо, сверху вниз, что позволяет применить автоструктурирование таблицы. 9
Выявление зависимостей (отображение связей между формулами и ячейками) n n Иногда, если формула использует влияющие или зависимые ячейки, при проверке формулы на точность или при поиске источника ошибки возникают трудности. Влияющие ячейки представляют собой ячейки, ссылающиеся на формулу в другой ячейке. Например, если в ячейке D 10 хранится формула =B 5, ячейка B 5 является влияющей на ячейку D 10. Зависимые ячейки содержат формулы, ссылающиеся на другие ячейки. Например, если ячейка D 10 содержит формулу =B 5, ячейка D 10 является зависимой от ячейки B 5. Для проверки формул можно использовать команды Влияющие ячейки и Зависимые ячейки, которые позволяют графически отобразить или отследить связи между этими ячейками и формулами с помощью стрелок зависимостей. n Графическое отображение зависимостей (трассировка) доступно через меню Офис -Параметры Excel-Дополнительно. Показать параметры для следующей книги-флажок Все - в разделе Для объектов показывать, которое открывает меню управления индикацией зависимостей 10
Отслеживание ячеек, обеспечивающих формулу данными (влияющих ячеек) Укажите ячейку, содержащую формулу, для которой следует найти влияющие ячейки. n Для отображения стрелки зависимости к каждой ячейке, непосредственно обеспечивающей данными активную ячейку, на вкладке Формулы в группе Зависимости формул выберите пункт Влияющие ячейки. Синие стрелки показывают ячейки, не вызывающие ошибок. Красные стрелки показывают ячейки, вызывающие ошибки. Если на выбранную ячейку имеется ссылка из другого рабочего листа или книги, путь от выбранной ячейки к значку рабочего листа будет обозначен черной стрелкой. Другая книга должна быть открыта до того, как Excel будет отслеживать эти зависимости. n Чтобы продолжить поиск следующего уровня влияющих ячеек, снова нажмите кнопку Влияющие ячейки. n Для удаления за раз единичного уровня стрелок зависимости, начиная с влияющей ячейки, наиболее удаленной от активной ячейки, на вкладке Формулы в группе Зависимости формул щелкните стрелку рядом с командой Убрать стрелки, а затем выберите пункт Убрать стрелки к влияющим ячейкам. Для удаления другого уровня стрелок зависимости вновь нажмите эту кнопку. n 11
Трассировка формул, ссылающихся на зависимые ячейки n n Укажите ячейку, для которой следует найти зависимые ячейки. Для отображения стрелки зависимостей к каждой зависимой ячейке в таблице Формулы группы Зависимости формул нажмите кнопку Зависимые ячейки. Синие стрелки показывают ячейки, не вызывающие ошибок. Красные стрелки показывают ячейки, вызывающие ошибки. Если на выбранную ячейку имеется ссылка из другого рабочего листа или книги, путь от выбранной ячейки к значку рабочего листа будет обозначен черной стрелкой. Другая книга должна быть открыта до того, как Excel будет отслеживать эти зависимости. Чтобы продолжить поиск следующего уровня зависимых ячеек, снова нажмите кнопку Зависимые ячейки. Для удаления по единичному уровню стрелок зависимости за один раз, начиная с зависимой ячейки, наиболее удаленной от активной ячейки, на вкладке Формулы в группе Зависимости формул щелкните стрелку рядом с командой Убрать стрелки, а затем выберите пункт Убрать стрелки к зависимым ячейкам. Для удаления другого уровня стрелок зависимости вновь нажмите эту кнопку. 12
Просмотр всех зависимостей на листе n n n n n В пустой ячейке введите = (знак равенства). Нажмите кнопку Выбрать все. Выберите ячейку и на вкладке Формулы в группе Зависимости формул нажмите кнопку Влияющие ячейки. При возникновении проблемы Microsoft Excel подает звуковой сигнал при выборе команды Зависимые ячейки или Влияющие ячейки. Если Microsoft Excel подает звуковой сигнал при выборе команды Зависимые ячейки или Влияющие ячейки , то либо Excel отследил все уровни формулы, либо предпринимается попытка отследить элемент, недоступный для отслеживания. Ниже приведен список элементов листов, на которые могут ссылаться формулы, но которые не могут быть отслежены при помощи инструментов аудита. Ссылки на текстовые поля, внедренные диаграммы и рисунки на листах Отчеты сводных таблиц Ссылки на именованные константы. Формулы, находящиеся в другой закрытой книге, содержащие ссылку на активную ячейку. Чтобы удалить все стрелки зависимости с рабочего листа, на вкладке Формулы в группе Зависимости формул нажмите кнопку Убрать стрелки 13
Наблюдение за формулой и ее результатом при помощи окна контрольного значения n n Если ячейки в листе невидимы, существует возможность просматривать эти ячейки и их формулы. Окно контрольного значения делает более удобными проверку, аудит и подтверждение вычислений по формулам и их результатов в больших листах. При использовании Окна контрольного значения отпадает необходимость в постоянной прокрутке или переходе к разным частям листа Эта панель инструментов, как и любая другая, может быть перемещаемой или закрепленной. Например, можно закрепить ее у нижнего края окна. В этой панели инструментов отслеживаются следующие свойства ячейки: книга, лист, имя, ячейка, значение и формула. Примечание. Можно иметь только одно контрольное значение на ячейку. Предполагаемое 14
Панель зависимостей 15
Скачать презентацию Структурирование таблиц 1 Что такое Структурирование При
Structur.pptx