Сергей Каледин – Технология работы со списками в Ехсеl (страница 1)
Сергей Каледин
Технология работы со списками в Ехсеl
План
1. Общие положения
2. Операции с базами данных в MS Excel
3. Работа с подготовленным списком в MS Excel
4. Анализ данных с помощью MS Excel
5. Сводные таблицы и Консолидация данных
1. Общие положения
Электронная таблица, оформленная в MS Excel в виде списка, т.е. таблицы, строки которой содержат однородную информацию, представляет собой простейшую базу данных.
Программа MS Excel включает набор средств и функций, позволяющих выполнять все основные операции, присущие базам данных.
База данных в MS Excel это просто список, состоящий из одного или более столбцов.
Строки таблицы, оформленной в виде списка, называются записями, а столбцы – полями записей. Столбцам присваиваются уникальные имена полей, которые заносятся в первую строку списка – строку заголовка.
Чтобы содержимое рабочего листа рассматривалось как база данных в MS Excel, необходимо придерживаться строгих правил:
- каждому полю записи соответствует один столбец рабочего листа;
- столбцы базы данных должны идти подряд, без промежутков между ними;
- в первой строке каждого столбца должен быть указан заголовок соответствующего поля;
- заголовок поля должен занимать не более одной ячейки;
- содержимое ячейки заголовка должно быть уникально в пределах рабочего листа;
- записи базы данных должны идти непосредственно ниже строки заголовков;
- пустые строки не допускаются (признак окончания базы данных), т.е. записи должны идти подряд, без промежутков между ними.
2. Операции с базами данных в MS Excel
Все операции с базами данных в MS Excel выполняются примерно одинаково: сначала необходимо выбрать любую ячейку в списке, а затем начать нужную операцию. При этом весь диапазон записей базы данных или списка выбирается автоматически.
На рабочем листе, содержащем список, выделяются следующие области:
- диапазон данных – область, где хранятся данные списка;
- диапазон критериев – область на рабочем листе, в которой задаются критерии для поиска информации. В диапазоне критериев указываются имена полей и отводится область для записи условий отбора;
- диапазон для извлечения – область, в которую MS Excel копирует выбранные данные из списка. Этот диапазон должен быть расположен на том же листе, что и список.
Существуют следующие способы ввода данных в список:
- использование формы данных, которая автоматически создается после определения заголовка списка с помощью команды Данные → Форма;
- ввод данных во вставляемые в список пустые строки, т.е. непосредственно ввод данных;
- использование средства Автоввод и команды Выбрать из списка для ускорения работы;
- использование форм MS Access и дальнейший перенос данных на лист MS Excel;
- использование мастера шаблонов для преобразования рабочего листа в MS Excel в форму;
- применение VBA – соответствующая программа будет предоставлять форму или окно диалога для ввода данных и их последующего помещения в определенные ячейки рабочего листа MS Excel.
Электронная таблица, оформленная в MS Excel в виде списка, состоящего из одного или более столбцов, содержащих однородную информацию, представляет собой простейшую базу данных.
MS Excel включает набор средств и функций, позволяющих выполнять все основные операции, присущие базам данных.
Строки таблицы, оформленной в виде списка, называются записями, а столбцы – полями записей. Столбцам присваиваются уникальные имена полей, которые заносятся в первую строку списка – строку заголовка.
Чтобы содержимое рабочего листа рассматривалось как база данных в MS Excel, необходимо придерживаться следующих правил:
- каждому полю записи соответствует один столбец рабочего листа;
- в первой строке каждого столбца должен быть указан заголовок соответствующего поля, который должен занимать не более одной ячейки;
- поля и записи в базе данных должны идти подряд, без промежутков между собой (пустые строки и столбцы считаются признаком окончания базы данных);
- записи базы данных должны идти непосредственно ниже строки заголовков;
- содержимое ячейки заголовка должно быть уникально в пределах рабочего листа.
Все операции с базами данных в MS Excel имеют общие принципы выполнения: сначала необходимо выбрать любую ячейку в списке, а затем начать нужную операцию. При этом весь диапазон записей базы данных или списка выбирается автоматически.
На рабочем листе, содержащем список, различают следующие области:
- диапазон данных – область, где хранятся данные списка;
- диапазон критериев – область на рабочем листе, в которой задаются критерии для поиска информации (в диапазоне критериев указываются имена полей и отводится область для записи условий отбора);
- диапазон для извлечения – область, в которую MS Excel копирует выбранные данные из списка (этот диапазон должен быть расположен на одном листе со списком.
К наиболее часто используемым способам ввода данных в список относятся:
а) использование формы данных, которая автоматически создается после определения заголовка списка с помощью команды Данные → Форма;
б) ввод данных в пустые строки списка, т.е. непосредственный ввод данных;
в) использование средства Автоввод и команды Выбрать из списка для ускорения работы.
3. Работа с подготовленным списком в MS Excel
Работа с подготовленным списком в MS Excel может осуществляться по трем направлениям:
1. Сортировка – выстраивание данных в алфавитном или цифровом порядке по возрастанию и убыванию, выполняется командой Данные → Сортировка; открывается диалоговое окно Сортировка диапазона, в котором задаются ключи сортировки (столбцы или строки) и порядок сортировки. Выбор в списках Сортировать по, Затем по, В последнюю очередь по определяет поля для упорядочивания списка. Пустые клетки всегда помещаются в конце всех данных. В диалоговом окне Параметры сортировки можно задать особый порядок сортировки, например, по дням недели.
2. Фильтрация (отбор данных) – извлечение записей данных из списка в соответствии с некоторыми требованиями (критериями). Для поиска и фильтрации данных в MS Excel существует 3 средства: форма данных (кнопка Критерии), автофильтр и расширенный фильтр.
Отбор данных с помощью формы данных производится следующим образом: нужно установить указатель ячейки в любое место внутри списка, выбрать команду Данные → Форма, затем нажать кнопку Критерии; в открывшемся окне в необходимых полях ввести критерии поиска, например, первую букву в названии (для перехода к записи, удовлетворяющей критерию, следует использовать кнопки Далее или Назад). Задавая критерии можно пользоваться символами подстановки: * - для замены любого количества символов (например, Ж* может соответствовать таким наименованиям товаров как жакет и жилет), ? – для замены одного символа.
Поиск с помощью автофильтра производится в следующем порядке:
1 Установление указателя ячейки в список данных.
2. Выполнение команды Данные → Фильтр → Автофильтр. Возле каждого поля строки заголовка появятся раскрывающиеся списки в виде кнопки с треугольником.
3. Переход к нужному полю.
4. Выбор необходимого критерия поиска или использование пользовательского автофильтра Условие (больше или равно, не равно и т.д.), который позволяет также задать комплексный критерий, объединяя его компоненты с помощью логического оператора И либо ИЛИ.
Для отображения имеющихся значений (отмене условия) следует обратиться к фильтру Все.
Поиск с помощью расширенного фильтра выполняется командой Данные → Фильтр → Расширенный фильтр. Критерии расширенной фильтрации списка можно определить, непосредственно задав их на рабочем листе. Преимущество этого способа состоит в том, что пользователь всегда имеет четкое представление о применяемых критериях и при необходимости может их изменять. Критерии задаются в отдельной области, которая должна содержать заголовки столбцов списка, для чего над списком или после него в первую из добавленных или еще незаполненных строк скопировать строку с заголовками столбцов. В пустые ячейки под соответствующими заголовками столбцов можно вставить критерии.
Чтобы получить точное соответствие отобранных значений заданному образцу, например, текст, следует ввести следующую формулу: ="=текст". При задании критериев можно также пользоваться символами подстановки. При вычислениях Microsoft Excel не учитывает регистр букв.
В качестве условия отбора можно также использовать вычисляемое значение, являющееся результатом выполнения формулы. При создании условия отбора с помощью формулы нельзя выбирать заголовок столбца в качестве заголовка столбца условий, нужно оставить условие отбора без заголовка либо использовать заголовок, не являющийся заголовком столбца в списке. Например, следующий диапазон условий отбора отображает строки, которые содержат в столбце C значение, превышающее среднее значение ячеек диапазона C7:C10.