Рис.6.Создание структуры
Над листом появится дополнительная полоска со значком квадрата 
. Нажатие на этот знак скроет столбцы, над которыми проведена черная линия. Если выделить столбцы под черной линией и вновь выбрать пункт «Группировать», то будет сделана двухуровневая структура. Аналогичным образом можно группировать и строки.
Рис 7. Результат проведения операции группирования
Сводные таблицы Excel
Рис.8. Мастер сводных таблиц –шаг1.
Для обработки списка нужно выбрать первую из предложенных опций и перейти на второй шаг мастера. На втором шаге необходимо выбрать обрабатываемый список и перейти к третьему шагу. На третьем шаге мастера (см. рис.9.) появится конструктор обработки данных списка.
Рис.10.Мастер сводных таблиц – шаг 3.
Для конструирования групповой операции над одним из полей списка, нужно перетащить мышкой название соответствующего поля в область «Данные». Так, чтобы получить суммарный вес, нужно перетащить 
в область «Данные». Области «Строка», «Столбец» и «Страница» позволяют получить кроме суммарного применения групповой операции по всем записям списка еще и частичные групповые операции над записями с одинаковыми значениями полей, выбранных в области. Так, чтобы получить не только суммарный вес всех людей, но и вес людей в каждой группе, нужно перетащить 
в область «Строка». На рис. 11 показан результат описанного перетаскивания.
Рис.11. Конструирование групповой операции по полю «Вес»
Для удаления любого поля из области конструктора нужно просто потянуть за него мышкой и вытащить за пределы данной области.
Нажав на кнопку «Далее» можно перейти на четвертый шаг мастера. На четвертом шаге нужно выбрать лист, в который будет помещена сводная таблица. Здесь можно выбрать создание сводной таблице на новом листе и завершить работу мастера. Полученная сводная таблица отображена на рис.12.
Рис.12.Сводная таблица, полученная в результате работы мастера
Для возврата в мастер сводных таблиц для изменения запроса на групповую операцию служит кнопка 
на панели «Сводные таблицы». Изменим запрос, добавив поле «Фамилия» в область данных. Сводная таблица примет вид, показанный на рис.13.
Рис.13. Сводная таблица с групповой операцией по полю «Фамилия»
Вновь вернемся в мастер сводных таблиц и добавим поле «Пол» в область «Столбец», а поле «Возраст» в область «Страница». Полученная сводная таблица показана на рис 14.
Рис.14.Результирующая сводная таблица
Область «Страница» отличается от областей «Строка» и «Столбец» тем, что дает возможность либо осуществить групповую операцию сразу по всем значениям поля, либо по одному выбранному значению. В приведенном примере было выбрано значение «19».
1. Площадь предприятий, подлежащих реконструкции по районам;
2. Число предриятий, подлежащих реконструкции по видам деятельности;
3. Суммарную площадь и число предприятий по направлениям;
4. Все вышеперечисленное в одной сводной таблице.
Список предприятий, подлежащих реконструкции
Выполнение операций со списками (базами данных) в табличном процессоре Excel
Понятие о списках (базах данных) Excel
Списком называется таблица Excel, представляющая собой базу данных.
Строка списка – это запись базы данных;
Столбец списка – это поле базы данных.
Название столбца при работе с базой данных называется именем поля.
ПРАВИЛА формирования списка (базы данных):
1. Имя поля (название столбца) должно занимать ровно одну ячейку.
2. Имена полей должны занимать верхнюю строку списка.
3. Между именами полей и данными не должно быть пустых строк.
4. Между списком и другими данными (вспомогательными параметрами, диапазонами критериев, другим списком и т.п.) должно быть не менее одной пустой строки и одного пустого столбца.
Сортировка данных списка
Сортировка данных в списке – это расположение данных в определенном порядке. Сортировать данные списка в Excel можно по одному, двум или трем полям.
Рассмотрим базу данных Студенты вида:
3.2.1. Сортировка по одному полю
Щелкнуть мышью в любой ячейке нужного поля (столбца) внутри списка. Щелкнуть мышью по кнопке 
(сортировка по возрастанию) или 
(сортировка по убыванию). Например, если щелкнуть мышью в любой ячейке столбца А (с А1 по А10) и выполнить сортировку по возрастанию, данные будут расположены в алфавитном порядке по фамилиям.
3.2.2. Сортировка по нескольким полям
Щелкнуть мышью в любой ячейке внутри списка. Выбрать пункт меню Данные – Сортировка. В диалоговом окне Сортировка данныхв выпадающем списке Сортировать по выбрать поле (столбец) для первичной сортировки; в выпадающем списке Затем по выбрать поле (столбец) вторичной сортировки, а в выпадающем списке В последнюю очередь по выбрать поле (столбец) следующей сортировки.
Например, если поля для сортировки выбраны так: 1. Номер группы; 2. Год рождения; 3. Фамилия; то данные сначала будут отсортированы по номеру группы; внутри группы по году рождения и, наконец, при одинаковых номере группы и годе рождения, по фамилиям в алфавитном порядке. Результат такой сортировки приведен ниже:
3.3. Фильтрация (выборка) данных списка
Фильтрация данных в списке – это выбор данных из списка по заданному критерию (условию). Существуют два вида фильтра – автофильтр и расширенный фильтр. При фильтрации с помощью автофильтра на данные каждого столбца может быть наложено 1 или 2 условия, связанных между собой логическим ИЛИ либо логическим И. При фильтрации с помощью расширенного фильтра количество накладываемых условий практически не ограничено.
Логическое И означает требование одновременного выполнения всех заданных условий, логическое ИЛИ означает требование выполнения хотя бы одного из условий.
Рассмотрим таблицу базы данных Студенты вида:
3.3.1. Фильтрация данных с помощью автофильтра
Задание 1. Пусть необходимо из списка Студенты п. 3.3 выбрать студентов 12-й группы, получающих стипендию 800 рублей и более. Ход выполнения работы:
Щелкнуть мышью в любой ячейке внутри списка
Выбрать пункт меню Данные – Фильтр – Автофильтр. В заголовках столбцов списка появятся кнопки раскрывающегося списка ( 
).
В столбце Группа щелкнем по такой кнопке и выберем из списка 12.Фильтрация по данному полю сразу будет произведена.
В столбце Стипендия щелкнем по кнопке и выберем из раскрывающегося списка Условие, поскольку необходимо выбрать студентов не с конкретной стипендией, а всех с размером стипендии ≥800. В появившемся диалоговом окне Пользовательский автофильтрв верхней левой строке щелкнем по кнопке и выберем из списка больше или равно, а в правой верхней строке выберем из раскрывающегося списка или введем с клавиатуры 800. Таким образом, сформировано условие «больше или равно 800». Поскольку на столбец Стипендия накладывается только одно условие, нажмем ОК. Фильтрация по заданным условиям будет выполнена.
Примечание. Отменить результаты фильтрации данных в таблице можно, щелкнув и выбрав Показать все.
Задание 2. Пусть из списка Студенты п. 3.3 необходимо выбрать студентов 11-й и 12-й групп 1987-го года рождения.
Предварительно отменив результаты предыдущей фильтрации, выполним следующее:
В столбце Группа щелкнем по кнопке и выберем из спискаУсловие; затем в диалоговом окне Пользовательский автофильтр в верхних строках описанным в Задании 1 способом сформируем условие «равно 11».Далее установим переключатель ИЛИ. В нижних строках сформируем условие «равно 12».
Пояснение: условия – студенты 11-й и 12-й групп – связаны логическим ИЛИ (а не логическим И), т.к. номер группы 11 или 12, а не 11 и 12 одновременно.
В столбце Год рождения щелкнем по кнопке и выберем из раскрывающегося списка 1987.
Фильтр покажет записи, удовлетворяющие таким условиям.
3.3.2. Фильтрация данных с помощью расширенного фильтра
При использовании расширенного фильтра условия, по которым производится выборка из списка данных, записываются в отдельные ячейки, называемые диапазоном (областью) условий.
ПРАВИЛА формирования диапазона условий для расширенного фильтра:
Задание 1. Пусть из базы данных Студенты п. 3.3 необходимо выбрать студентов 11-й и 12-й групп 1987-го года рождения.
Для формирования диапазона условий (правило № 2) в ячейки А12 и В12 копируем заголовки нужных нам столбцов – группаи год рождения соответственно. 11-ю строку оставляем пустой (правило № 1).
Условия для номера группы (11) и года рождения (1987) связаны между собой логическим И, поэтому записываем их в одной строке (правило № 4), в нашем случае 13-ой. В ячейке В13 (под заголовком год рождения) записываем условие =1987или 1987. (Примечание: при записи условий знаки неравенств записывать обязательно, а знак равенства можно опустить). В ячейке А13 (под заголовком группа) записываем условие 11.
Теперь запишем условия для отбора студентов 12-й группы. С условиями для студентов 11-й группы они связаны логическим ИЛИ, поэтому должны быть записаны в другой строке (правило № 4). В 14-й строке записываем условия: для столбцагруппа (ячейка А14) – 12, а для столбца год рождения(ячейка В14 ) – 1987.
 Диапазон условий сформирован: | Поскольку с нашем случае в списке студентов нет групп с номером, большим 12, то диапазон условий может быть записан и следующим образом:  |
Выполняем команду меню Данные – Фильтр – Расширенный фильтр.
В диалоговом окне Расширенный фильтр устанавливаем курсор в строке Исходный диапазон и мышью в окне таблицы выделяем блок ячеек А1:F10, т.е. исходную таблицу базы данных, включая заголовки полей. Выделенный диапазон сразу же отразится в этой строке.
Примечание: если перед выполнением команды меню Данные – Фильтр – Расширенный фильтр курсор поместить в любую ячейку внутри таблицы базы данных, исходный диапазон будет выделен автоматически.
Примечание: и в таблице базы данных, и в диапазоне условий фильтрации нужно выделять все ячейки, включая заголовки столбцов.
Можно установить переключатель скопировать результат в другое место; затем установить курсор в строку Поместить результат в диапазон и мышью в окне таблицы щелкнуть в ячейке, которая станет левой верхней ячейкой результатов фильтрации; или же можно оставить переключатель фильтровать список на месте.
Нажать ОК, и список будет отфильтрован в соответствии с заданными условиями.
Задание 2. Пусть из базы данных Студенты п. 3.3 нужно выбрать студентов 11-й и 12-й групп, получающих стипендию в размере от 500 до 800 рублей включительно.
Для формирования диапазона условий в ячейки А12 и В12 копируем заголовки столбцов группа и стипендия соответственно (правило № 2). 11-ю строку оставляем пустой (правило № 1).
Условия для номера группы (11) и размера стипендии (от 500 до 800) связаны между собой логическим И, поэтому (правило № 4) записываем их в одной строке (в нашем случае 13-й). В ячейке А13 (под заголовком группа) записываем условие 11. Условие для размера стипендии может быть сформулировано так «больше или равно 500, но меньше или равно 800». Эти два условия связаны логическим И, т.к. требуется их одновременное выполнение. Каждое из этих условий должно быть записано в отдельной ячейке (правило № 3), причем ячейки (по правилу № 4)должны располагаться на одной строке (в нашем случае 13-й) и заголовок для ячеек с этими условиями одинаков – стипендия. Поэтому для формирования условия «от 500 до 800» в ячейку С12 копируем еще раз заголовок стипендия.Под любым заголовком стипендия в одной ячейке (например, В12) записываем условие >=500, а в другой (например, С12) условие СРЗНАЧ($F$2:$F$10).Диапазон условий сформирован:
Примечание: после завершения формирования вычисляемого критерия в ячейках будет отображен результат вычисления: ИСТИНА или ЛОЖЬ, а саму формулу критерия можно просмотреть лишь в строке формул.
Выполняем команду меню Данные – Фильтр – Расширенный фильтр,указываем Исходный диапазон(А1:F10)и Диапазон условий(А12:В14). Нажимаем ОК. Фильтрация по заданным условиям будет выполнена.
Работа со списками данных в Excel
Добрый день уважаемый читатель!
Сегодня я хочу поговорить об одной из основных возможностях — это работа со списками данных в Excel. К самим спискам можно отнести практически любые структурированные данные, такие как, номера телефонов, адреса, ФИО, номенклатурные наименования товаров, перечень заведений, поставщики, сотрудники и много-много другой информации, своего рода база данных. Я думаю, с такими данными вы сталкивались, а значится и инструменты для систематизации и анализа таких данных будут очень полезны, особенно при создании дашбордов. По большому счёту от обычной таблицы списки ничем особым не отличаются, за исключением своих размеров, они достаточно велики. При работе со списками используют понятия: для строк – записи, а для столбиков – поля.
Для примера используем список или базу данных сотрудников и их личных данных: 
Для создания правильных списков данных, с которыми эффективно можно работать, необходимо следовать нескольким правилам, а именно:
Для удобства работы и использование списков есть возможность улучшить навигацию по ней, используя закрепление областей или разделения рабочего окна.
При работе с объёмными таблицами любой пользователь сталкивается с проблемой, когда заголовки полей уходят за пределы окна и в связи с этим появляется неудобства пользования. Для избегания этого необходимо закрепить «шапку» нашей таблицы, что бы полоса прокрутки не влияла на отображения первых строк и столбцов ваших списков. Это можно осуществить в панели управления, вкладка «Вид», блок «Окно» и нажать выпадающий список «Закрепить область».
Вариантов закрепить область прокрутки всего три, это:
Также существует возможность разделения рабочей области одновременно на четыре части для независимой работы и прокручивания данных, получая возможность одновременно работать и в начале и в конце списка. Для разделения вам необходимо на панели управления, во вкладке «Вид», в блоке «Окно» нажать кнопку «Разделить», предварительно установив курсор на ячейку, по границам которой и будет происходить разделение. Отключить разделения можно повторно нажав на туже самую кнопку. 
Сами же данные в списках, возможно, отбирать, используя несколько инструментов, выбор которых зависит от ваших целей. Для этих задач можно использовать:
Отбор с помощью фильтра
Excel предоставляет возможность отфильтровать ваши списки по заданным критериям двумя вариантами, с помощью:
Если вы будете использовать автофильтр, то данные, которые не соответствуют указанным условиям, будут спрятаны. В документе отражаться будут только отобранные по критериям записи в одном столбике.
Способностей расширенного фильтра гораздо больше, что позволит делать отбор записей по нескольким условиям и даже переносить их в другой диапазон рабочей книги.
Детально работу с фильтрами, я описал в статье «Автофильтр в Excel» с которой вы можете ознакомиться, перейдя по соответствующей ссылке.
Создаем промежуточные отчёты
Очень часто возникает необходимость группировать данные списков по определенным показателям с расчётами по ним итогов. Так создаются удобные и очень полезные в работе отчёты и анализы, прекрасный инструмент для любого бухгалтера и экономиста. 
Создать такой детализированный список данных в Excel с выделением групп и подбитием итогов по группам и общий по полю, не очень трудно. Всё это можно произвести в несколько шагов, но обязательным условием применения промежуточных итогов к спискам, это сортировка данных по полю для которого создается итог. 
Подробно и в деталях об этом можно узнать, прочитав статью «Промежуточные итоги в Excel», перейдя по ссылке.
Сортируем свои списки
При формировании своих списков, практически всегда информация формируется в произвольном порядке, что затрудняет ее эффективное использование. Для избежания этого и удобства работы, в Excel существует инструмент «Сортировка». Запуск производится в панели инструментов, вкладка «Данные» и кнопка «Сортировка». В появившемся диалоговом окне вы можете выбрать поле сортировки, установить любой приоритет для очередности сортировки, а также выбрать критерии (убывание или возрастание). 
Детально о возможностях этого инструмента можете описано в статье «Сортировка данных в Excel».
Работаем со сводными таблицами
Это улучшенная версия промежуточных итогов и условного форматирования. А более точно, это своеобразное подобие перекрестных запросов по базе данных. Сводные таблицы являют собой динамические структуры, которые умеют выводить данные содержащиеся в БД в разных вариантах детализации при использовании срезов.
Для создания сводных таблиц данные могут отбираться из любых источников: из внешних баз данных, с любого рабочего листа или книги, а также на основе ранее созданной сводной таблицы.
Для создания сводной таблицы необходимо установить курсор на любую ячейку таблицы или базы данных, и на панели управления во вкладке «Вставка» выбрать пункт «Сводная таблица». В диалоговом окне указываем, где размещены данные для анализа (по умолчанию будет указан диапазон таблицы, где стоит курсор) и куда нужно поместить результат. 
Следующим шагом в «Конструкторе сводной таблицы» вы можете из полей и записей вашей БД создать отчёт в таком виде, который вам нужен. 
При внесении изменений в базу данных, автоматических изменений в сводной таблице не происходит. Все изменения стают, доступны только при нажатии кнопки «Обновить данные», через контекстное меню или вкладка «Данные» и кнопка «Обновить всё».
Группируем элементы таблицы
Иногда для удобства навигации по вашей базе данных или для сворачивания некоторых элементов таблицы можно использовать инструменты «Группировка» и «Разгруппировать». Эта возможность позволит свернуть данные, которые в данный момент вам не интересны и отображать их не стоит. Очень полезный инструмент, когда возникает необходимость создать диапазон печати для некоторых данных.
А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное о работе со списками данных в Excel вам пригодилось и было понятным. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!
Технология работы со списками в Ехсеl
Электронная таблица, оформленная в MS Excel в виде списка, т.е. таблицы, строки которой содержат однородную информацию, представляет собой простейшую базу данных.
Программа MS Excel включает набор средств и функций, позволяющих выполнять все основные операции, присущие базам данных.
База данных в MS Excel это просто список, состоящий из одного или более столбцов.
Строки таблицы, оформленной в виде списка, называются записями, а столбцы – полями записей. Столбцам присваиваются уникальные имена полей, которые заносятся в первую строку списка – строку заголовка.
Чтобы содержимое рабочего листа рассматривалось как база данных в MS Excel, необходимо придерживаться строгих правил:
Все операции с базами данных в MS Excel выполняются примерно одинаково: сначала необходимо выбрать любую ячейку в списке, а затем начать нужную операцию. При этом весь диапазон записей базы данных или списка выбирается автоматически.
На рабочем листе, содержащем список, выделяются следующие области:
Существуют следующие способы ввода данных в список:
Электронная таблица, оформленная в MS Excel в виде списка, состоящего из одного или более столбцов, содержащих однородную информацию, представляет собой простейшую базу данных.
MS Excel включает набор средств и функций, позволяющих выполнять все основные операции, присущие базам данных.
Строки таблицы, оформленной в виде списка, называются записями, а столбцы – полями записей. Столбцам присваиваются уникальные имена полей, которые заносятся в первую строку списка – строку заголовка.
Чтобы содержимое рабочего листа рассматривалось как база данных в MS Excel, необходимо придерживаться следующих правил:
Все операции с базами данных в MS Excel имеют общие принципы выполнения: сначала необходимо выбрать любую ячейку в списке, а затем начать нужную операцию. При этом весь диапазон записей базы данных или списка выбирается автоматически.
На рабочем листе, содержащем список, различают следующие области:
К наиболее часто используемым способам ввода данных в список относятся:
а) использование формы данных, которая автоматически создается после определения заголовка списка с помощью команды Данные → Форма;
б) ввод данных в пустые строки списка, т.е. непосредственный ввод данных;
в) использование средства Автоввод и команды Выбрать из списка для ускорения работы.
Работа с подготовленным списком в MS Excel может осуществляться по трем направлениям:
1. Сортировка – выстраивание данных в алфавитном или цифровом порядке по возрастанию и убыванию, выполняется командой Данные → Сортировка; открывается диалоговое окно Сортировка диапазона, в котором задаются ключи сортировки (столбцы или строки) и порядок сортировки. Выбор в списках Сортировать по, Затем по, В последнюю очередь по определяет поля для упорядочивания списка. Пустые клетки всегда помещаются в конце всех данных. В диалоговом окне Параметры сортировки можно задать особый порядок сортировки, например по дням недели.
2. Фильтрация (отбор данных) – извлечение записей данных из списка в соответствии с некоторыми требованиями (критериями). Для поиска и фильтрации данных в MS Excel существует 3 средства: форма данных (кнопка Критерии), автофильтр и расширенный фильтр.
Отбор данных с помощью формы данных производится следующим образом: нужно установить указатель ячейки в любое место внутри списка, выбрать команду Данные → Форма, затем нажать кнопку Критерии; в открывшемся окне в необходимых полях ввести критерии поиска, например первую букву в названии (для перехода к записи, удовлетворяющей критерию, следует использовать кнопки Далее или Назад).
Поиск с помощью автофильтра производится в следующем порядке:
Для отображения имеющихся значений (отмене условия) следует обратиться к фильтру Все.
Поиск с помощью расширенного фильтра выполняется командой Данные → Фильтр → Расширенный фильтр. Критерии расширенной фильтрации списка можно определить, непосредственно задав их на рабочем листе. Преимущество этого способа состоит в том, что пользователь всегда имеет четкое представление о применяемых критериях и при необходимости может их изменять.
Критерии задаются в отдельной области, которая должна содержать заголовки столбцов списка, для чего над списком или после него в первую из добавленных или еще незаполненных строк скопировать строку с заголовками столбцов. В пустые ячейки под соответствующими заголовками столбцов можно вставить критерии.
Чтобы получить точное соответствие отобранных значений заданному образцу, например текст, следует ввести следующую формулу: =»=текст». При задании критериев можно также пользоваться символами подстановки. При вычислениях Microsoft Excel не учитывает регистр букв.
В качестве условия отбора можно также использовать вычисляемое значение, являющееся результатом выполнения формулы. При создании условия отбора с помощью формулы нельзя выбирать заголовок столбца в качестве заголовка столбца условий, нужно оставить условие отбора без заголовка либо использовать заголовок, не являющийся заголовком столбца в списке. Например, следующий диапазон условий отбора отображает строки, которые содержат в столбце C значение, превышающее среднее значение ячеек диапазона C7:C10.
=C7>СРЗНАЧ($C$7:$C$10)
Формула, используемая для создания условия отбора, должна использовать относительную ссылку на заголовок столбца (например, «Реализация») или на соответствующее поле в первой записи. Все остальные ссылки в формуле должны быть абсолютными, а формула должна возвращать результат ИСТИНА или ЛОЖЬ. В данном примере «C7» является ссылкой на поле (столбец C) первой записи (строка 7) списка.
Чтобы объединить критерии с помощью оператора И, их следует указывать в одной строке. Например, следующий диапазон условий отбора возвращает все строки, содержащие значения «Брюки» в столбце «Вид изделия», «Костюмная ткань» в столбце «Вид материала» с объемом реализации более 100 руб.
| Вид изделия | Вид материала | Реализация |
| Брюки | Костюмная ткань | >100 |
Чтобы объединить критерии с помощью оператора ИЛИ, их следует указывать в различных строках. Так, при наличии для одного столбца двух и более условий отбора необходимо ввести эти условия отбора непосредственно друг под другом в отдельные строки. Например, следующий диапазон условий отбора отбирает строки, содержащие в столбце «Вид изделия» значения «Брюки», «Юбка» или «Бриджи».
| Вид изделия |
| Брюки |
| Юбка |
| Бриджи |
Для того чтобы найти данные, отвечающие одному условию, в одном столбце, или отвечающие другому условию, в другом столбце, следует ввести условия отбора в разные строки диапазона условий отбора. Например, следующий диапазон условий отбора отображает все строки, содержащие значение «Брюки» в столбце «Вид изделия», «Костюмная ткань» в столбце «Вид материала», либо объем реализации, превышающий 100 шт.
| Вид изделия | Вид материала | Реализация |
| Брюки | ||
| Костюмная ткань | ||
| >100 |
Для того чтобы найти строки, отвечающие одному из двух наборов условий, каждый из которых содержит условия более чем для одного столбца, нужно ввести эти условия отбора в отдельные строки. Например, следующий диапазон условий отбора отображает строки, содержащие как значение «Брюки» в столбце «Вид изделия», так и объем реализации, превышающий 300 шт., а также строки по виду изделия «Юбка» с объемом реализации более 100 руб.
| Вид изделия | Продажи |
| Брюки | >300 |
| Юбка | >100 |
Для того чтобы найти строки, отвечающие более чем двум наборам условий, нужно включить несколько столбцов с одинаковыми заголовками. Например, следующий диапазон условий отбора возвращает реализацию товаров от 200 до 400 шт., а также реализация менее 50 шт.
