Использование списков в Excel.

 

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

Список. Это упорядоченные строки данных, например имена клиентов и названия товаров, а также квартальные цифры продаж.

Если вы уже работали с программой Excel, то вам знакомы некоторые возможности, описываемые в этом курсе (например, стрелки автофильтра), однако здесь рассматривается новый и весьма удобный способ применения этих функций для работы с данными.

Чтобы получить дополнительные сведения о данном курсе, прочитайте краткий обзор в центре страницы или оглавление в левом столбце.

Работа со списками

Списком называют таблицу, строки которой содержат однородную информацию. Наиболее частые операции со списками - сортировка и поиск информации. Список характеризует не содержимое таблицы, а способ ее организации. Только однородность информации в столбцах таблицы дает возможность дает возможность применять сортировку или фильтры.

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

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

Сортировка списков

Необходимость сортировки записей в списках возникает, обычно, для последующего быстрого поиска информации в списке. Существуют два способа сортировки: по возрастанию и по убыванию признака сортировки, которым является один из столбцов списка. Для простой сортировки строк следует активизировать любую ячейку внутри списка и щелкнуть по одному из значков (по возрастанию или по убыванию) на панели инструментов. Excel автоматически определяет границы списка и сортирует строки целиком. Если пользователь сомневается в правильности определения границ списка, то целесообразно выделить сортируемый диапазон и выполнить Данные/Сортировка. В окне " Сортировка диапазона" следует задать признак сортировки(заголовок столбца), а также как сортировать - по возрастанию или по убыванию.

Если в столбце, являющемся признаком сортировки, много повторяющейся информации, то возможна дополнительная сортировка по вторичному признаку. Максимальное количество признаков, по которым можно сортировать таблицу - 3.

Консолидация данных в Excel

Методы консолидации данных в табличном редакторе microsoftexcel предусмотрено несколько способов консолидации:

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

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

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

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

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

Консолидация данных с использованием трехмерных ссылок

Для консолидации данных с использованием трехмерных ссылок необходимо проделать следующие шаги:

1. На листе консолидации скопируйте или задайте надписи для данных консолидации.

2. Укажите ячейку, в которую следует поместить данные консолидации.

3. Введите формулу. Она должна включать ссылки на исходные ячейки каждого листа, содержащего данные, для которых будет выполняться консолидация.

4. Повторите шаги 2 и 3 для каждой ячейки, в которой требуется вывести результаты обработки данных.

 

Использование трехмерных ссылок для объединения данных

 

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

 

 

Добавление данных для объединения

 

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

 

Консолидация данных по расположению

Для консолидации данных по их расположению необходимо проделать следующие шаги:

1. Укажите верхнюю левую ячейку области назначения консолидируемых данных.

2. Выберите команду " консолидация" в меню " данные".

3. Выберите из раскрывающегося списка " функция" функцию, которую следует использовать для обработки данных.

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

5. Повторите шаг 4 для всех консолидируемых исходных областей.

6. Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, установите флажок " создавать связи с исходными данными". Связи нельзя использовать, если исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные области и изменять исходные области, уже участвующие в консолидации.

Консолидация данных по категориям

Для консолидации данных по категориям необходимо для начала указать верхнюю левую ячейку конечной области консолидируемых данных, а после этого выбрать команду " консолидация" в меню " данные" после чего необходимо следовать следующим пунктам:

1. Выберите из раскрывающегося списка " функция" функцию, которую следует использовать для обработки данных.

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

3. В наборе флажков " использовать в качестве имен" установите флажки, соответствующие расположению в исходной области заголовков: в верхней строке, в левом столбце или в верхней строке и в левом столбце одновременно.

4. Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, установите флажок " создавать связи с исходными данными".

 

Связи нельзя использовать, если исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные области и изменять исходные области, уже входящие в консолидацию.

 

 

Промежуточные итоги.

 

Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя команду Промежуточный итог в группе Структура на вкладке Данные. Но если список с промежуточными итогами уже создан, его можно модифицировать, отредактировав формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Синтаксис

ПРОМЕЖУТОЧНЫЕ.ИТОГИ ( номер_функции; ссылка1; ссылка2;...)

Номер_функции — число от 1 до 11 (с включением скрытых значений) или от 101 до 111 (с исключением скрытых значений), которое указывает, какую функцию использовать при вычислении итогов внутри списка.

Ссылка1; cсылка2 — от 1 до 254 интервалов или ссылок, для которых подводятся промежуточные итоги.

Замечания

· Если уже имеются формулы подведения итогов внутри аргументов «ссылка1; ссылка2;...» (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.

· Для диапазона констант " номер_функции" от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ включает значения строк, скрытых при помощи команды Скрыть строки (меню Формат, подменю Скрыть/Показать ) в группе Ячейки на вкладке Лист. Эти константы используются для получения промежуточных итогов для скрытых и не скрытых чисел списка. Для диапазона констант " номер_функции" от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает значения строк, скрытых при помощи команды Скрыть строки. Эти константы используются для получения промежуточных итогов только для не скрытых чисел списка.

· Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все строки, не включенные в результат фильтра, независимо от используемого значения константы «номер_функции».

· Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы «номер_функции» от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109; B2: G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.

· Если среди ссылок есть трехмерные ссылки, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает значение ошибки #ЗНАЧ!.

 

 

Сводные таблицы

Сводные таблицы предназначены для обобщения, анализа, изучения и представления итоговых данных. Сводная таблица представляет собой интерактивный способ быстрого обобщения больших объёмов данных.

Для создания таблицы нужно перейти на вкладку вставка, таблицы, сводные таблицы.

Используя фильтр – список, который находится в левом верхнем углу страницы, можем отобразить обобщенные данные по отдельно компоненту таблицы.

 

⇐ Предыдущая1234567Следующая ⇒

Поделиться: