Использование команды ИТОГИ для организации списков.

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

В первую очередь отсортируйте список по тому столбцу, по которому хотите подвести итоги

Затем выделите любую ячейку в списке и выберите команду "Итоги" в меню "Данные"

В появившемся окне диалога "Промежуточные итоги" выберите заголовок столбца

В списке "Операция:" выберите требуемую функцию, которая может быть использована для вычисления промежуточных итогов, например функцию СУММ()

Далее в списке "Добавить итоги по" установите флажок перед заголовком столбца, по которому необходимо выполнить расчет промежуточного итога (например, стоимость)

Нажмите кнопку ОК для того, чтобы выполнить команду промежуточные итоги

В результате выполнения команды "Данные" - "Итоги" список отображается на экране в виде структуры

Структурирование таблиц.

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

=СУММ(ОтделПродаж[ОбъемПродаж])    чем эту ссылку =СУММ(C2:C7)

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

Для того, чтобы запустить консолидиацию, сначала заранее откройте файлы John.xls, Rita.xls и Stiven.xls. Затем создайте новую пустую книгу, установите в нее активную ячейку и выберите в меню Данные - Консолидация (Data - Consolidate). Откроется окно:

Установите курсор в строку Ссылка (Reference) и, переключившись в файл John.xls через меню Окно (Window), выделите таблицу Джона, затем нажмите кнопку Добавить (Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.

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

Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен... (Use labels) Флаг Создавать связи с исходными данными (Create links to source data) позволит в производить пересчет консолидированного отчета автоматически.ок.

Наши файлы проконсолидировались по крайнему левому столбцу и верхней строке выделенных областей в каждом файле. Причем, если развернуть группы (значками плюс слева от таблицы), то можно увидеть из какого именно файла какие данные попали в отчет и ссылки на исходные файлы:

Фильтрация (выборка) данных.

Фильтрация (выборка) данных в таблице позволяет отображать только те строки, содержимое ячеек которых отвечает заданному условию или нескольким условиям. В отличие от сортировки данные при фильтрации не переупорядочиваются, а лишь скрываются те записи, которые не отвечают заданным критериям выборки.

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

 Для использования автофильтра нужно:

установить курсор внутри таблицы;

выбрать команду Данные - Фильтр - Автофильтр;

раскрыть список столбца, по которому будет производиться выборка;

выбрать значение или условие и задать критерий выборки в диалоговом окне Пользовательский автофильтр.

Для восстановления всех строк исходной таблицы нужно выбрать строку все в раскрывающемся списке фильтра или выбрать команду Данные - Фильтр - Отобразить все.

Для отмены режима фильтрации нужно установить курсор внутри таблицы и повторно выбрать команду меню Данные - Фильтр - Автофильтр (снять флажок).

Расширенный фильтр позволяет формировать множественные критерии выборки и осуществлять более сложную фильтрацию данных электронной таблицы с заданием набора условий отбора по нескольким столбцам. Фильтрация записей с использованием расширенного фильтра выполняется с помощью команды меню Данные - Фильтр - Расширенный фильтр.

Автофильтр.

В меню Сервис - Настройка, вкладка Команды, в категории Данные есть две кнопки: Автофильтр (выглядит как воронка со знаком равно) и Отобразить все (без значка). Перетащите их, удерживая левую кнопку мыши, к себе на панель инструментов - рядом с другими кнопками. Кнопку Отобразить все можно "довести до ума", щелкнув по ней правой кнопкой мыши и выбрав в контекстом меню значок для кнопки (там, например, есть подходящий значок "глаз") и Основной стиль (чтобы был только значок без подписи). Теперь можно смело закрыть окно Настройка и опробовать приобретение. Кнопка Автофильтр (воронка со знаком равно) на самом деле работает как фильтр по выделению, т.е. фильтрует в списке только те строки, где есть содержимое текущей ячейки. Кнопка Отобразить все одним щелчком сбрасывает список в исходное (полное) состояние.

Расширенный фильтр.

В отличие от Автофильтра и Пользовательского автофильтра - Расширенный фильтр практически не имеет ограничений на количество условий, налагаемых на список. Но (бесплатных пирожных не бывает!) требует некоторых подготовительных операций. А именно:

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

Затем, ниже скопированных заголовков в пустые ячейки вводятся условия для фильтрации. Причем, условия введенные в ячейки одной строки Excel будет связывать логическим "И", а в ячейки разных строк - логическим "ИЛИ".

Для того, чтобы отфильтровать данные по критериям из нашей таблицы, выделите любую ячейку исходного списка сотрудников и выберите в меню Данные - Фильтр - Расширенный фильтр (Data - Filter - Advanced Filter). Откроется окно Расширенного фильтра, в которое необходимо ввести адрес исходного диапазона, адрес диапазона условий и указать место, куда поместить результаты фильтрации:

Флажок Только уникальные записи служит для отбора неповторяющихся данных