Управление данными в Excel

(Занятие № 1)

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

Создание списка клиентов

Создание рабочей книги От­кройте новую книгу и сохраните ее под именем Заказы. Для создания списка будет использоваться обычный рабочий лист. Присвойте первому рабочему листу выразительное имя Клиенты. Чтобы назначить рабочему листу имя, выполните двойной щелчок на его ярлычке. В результате появится диалоговое окно с текущим именем листа, которое можно изменить. Следующие рабочие листы будут содержать данные о предлагае­мых товарах, заказах, а также анализ всех данных.

Ввод заголовков столбцов После открытия окна активной является ячейка А1. С этой ячейки можно начать ввод данных. Введите в ячейку А1 заголовок первого столбца — Название фирмы. Чтобы ввести заголовок второго столбца, следует переместить указатель ячейки в первую ячейку второго столбца. Для этого нужно нажать клавишу [Стрелка вправо]. Введите в ячейки А1 – G1 следующие заголовки: Название фирмы, Код, Контактная персона, Индекс, Город, Улица, Телефакс, Телефон, Скидка (%).

Заголовок первого столбца вряд ли вызовет вопросы. Во втором столбце указываются коды, которые присваиваются клиентам с целью автоматизации некоторых операций. Такой код может иметь какой угодно вид, например: 101, 102, 103, R-O1, RIO1 и т.д, однако он должен быть уникальным. В следующих столбцах указываются: представитель фирмы, ответственный за контакты с клиентами, адрес фирмы, телефакс и телефон. В последнем столбце нашего списка указывается скидка (поскольку с некото­рыми постоянными клиентами у нас сложились особые отноше­ния, мы готовы пойти даже на предоставление им определенной скидки).

Ввод записей Электронная таблица имеет структуру, которая делает создание списка в Excel максимально простым. Данные можно вводить непосредственно в ячейки таблицы, однако лучше воспользо­ваться специальным диалоговым окном (Формой данных), в котором можно выполнять все операции с записями: ввод, редактирование, поиск. Чтобы открыть диалоговое окно фор­мы данных, необходимо выбрать в меню Данные команду Форма. Введите в соответствующие поля данные о клиентах, завершая ввод каждой записи нажатием кнопки Доба­вить. Для перехода из одного поля ввода в другое помимо мыши можно использовать клавишу [Tab]. После ввода последней запи­си щелкните на кнопке Закрыть.

Диалоговое окно формы данных можно использовать и для обработки записей. Так, кнопки Назад и Далее позволяют про­сматривать все записи. С помощью кнопки Удалить ненужная запись может быть удалена. После редактирования записи стано­виться доступной кнопка Вернуть. Ее нажатие приводит к восста­новлению состояния записи до изменения.

Поиск записей С помощью диалогового окна Формы данных можно выполнить поиск нужных записей, задавая различные критерии поиска. После нажатия кнопки Критерии на экране появляется неза­полненная форма с заголовками столбцов. Задайте в соответ­ствующем поле ввода критерий и начните поиск нажатием кнопки Далее. Например, чтобы найти записи с данными о клиентах в Одессе, выберите в поле Город значение Одесса. Вскоре на экране появится первая найденная запись, соответствующая заданному критерию. У Вас есть возможность выбора направле­ния поиска, которая реализуется посредством кнопок Далее и Назад. При задании критерия можно использовать символы под­становки. Символ звездочка (*) применяется для обозначения любого количества неизвестных символов, а символ вопроси­тельный знак (?) — для обозначения одного символа.

Форматирование таблицы После ввода всех данных следует отформатировать таблицу, а также задать для ячеек I2—I14 процентный стиль отображения данных (для этого необходимо выделить ячейки I2—I4 и акти­визировать кнопку Процентный формат в панели инструментов Форматирование ). В процессе форматирования таблицы может понадобиться увели­чить ширину некоторых столбцов, чтобы данные в них полностью отображались. Эту операцию можно выполнить, используя мышь. Например, если необходимо увеличить ширину первого столбца, переместите указатель мыши в область заголовков, установите его на разделительной линии между столбцами А и В (указатель мыши примет вид двунаправленной стрелки) и выполните двойной щелчок. Программа приведет ширину первого столбца в соответ­ствие с длиной записей. Изменить ширину столбца можно также путем перемещения разделительной линии между заголовками столбцов. Число слева от строки формул показывает среднее количество символов установленного по умолчанию шрифта, необходимое для заполнения активной ячейки. Чтобы не тратить время на прямое форматирование, используйте Автоформаты, например Список 2. Для этого выделите ячейки, подлежащие форматированию (выполните щелчок на первой ячейке и, удерживая нажатой левую кнопку мыши, переместите указатель манипулятора по подлежащим выделению ячейкам таблицы), активизируйте команду Автоформат меню Формат и выберите в списке Список форматов формат Список 2.

Сортировка данных Записи в нашем списке расположены хаотически, что значитель­но усложняет поиск нужных данных. Поэтому целесообразной является сортировка данных. Для этого предназначена команда Сортировка меню Данные. В результате ее выбора на экране появляется диалоговое окно, изображенное на рисунке.

В этом окне можно задать вид сортировки: по алфавиту, по возрас­танию, по убыванию или по датам. Если столбец содержит буквен­но-цифровые данные, сначала выполняется сортировка по цифрам. Если первая строка списка содержит заголовки столбцов, она может не использоваться при сортировке. Для этого должна быть активи­зирована опция Подписям (первая строка диапазона) в группе Иден­тифицировать поля по. Если же в списке нет заголовков столбцов, включить данные первой строки в сортировку можно с помощью опции Обозначениям столбцов листа. В нашем списке целесообразно рассортировать данные по критериям Город и Контактная персона. После добавления новых записей сортировку можно выполнить повторно.

Нажав кнопку Параметры, можно открыть диалоговое окно, в котором Вам предоставляется возможность задать дополнитель­ные параметры сортировки. Если при сортировке необходимо учитывать различие между строчными и прописными буквами, следует активизировать опцию Учитывать регистр. По умолчанию Excel выполняет сортировку строк (активна опция Строки диапазона).

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

Автофильтр Наиболее простой и удобный способ фильтрации заключается в использовании автофильтра. В случае применения автофильтра на экране отображаются только те записи, которые соответствуют критериям отбора. Кроме того, как следует из названия, программа автоматически выполняет определенные операции. Чтобы убедиться в справедливости сказанного, попытайтесь применить автофильтр при обработке нашего списка. Для этого воспользуйтесь командой Фильтр/Автофильтр меню Данные. После щелчка на имени этой команды в первой строке рядом с заголовком каждого столбца появляется кнопка со стрелкой. С ее помощью можно открыть список, содержащий все значения полей в столбце. Выберите в списке нужное значение. В результате на экране будут видны только те записи, которые соответствуют заданному критерию отбора. Кроме значений полей, каждый список содержит еще три эле­мента: (Все), (Первые 10...) и (Условие...). Элемент (Все) предна­значен для восстановления отображения на экране всех записей после применения фильтра. Элемент (Первые 10...) обеспечивает автоматическое представление на экране десяти первых записей списка. Это одна из немногих новинок в седьмой версии про­граммы Excel. Если Вы занимаетесь составлением всевозможных рейтингов, главная задача которых состоит в определении лучшей десятки, воспользуйтесь этой новой функцией. Десятка записей может быть составлена на основе данных, представленных в списке в отдельном столбце. Последний элемент — (Условие...) — используется для формиро­вания более сложного критерия отбора, в котором можно приме­нять условные операторы И и ИЛИ. В нашем примере мы присвоили клиентам, специализирующимся на продаже принте­ров, код, начиная с цифры 3. Предположим, мы хотим просмот­реть записи о распространителях принтеров, чтобы разослать им информацию о поступлении новых моделей и о выгодных усло­виях продажи. Чтобы исключить ненужные в данном случае записи, выберите элемент (Условие...) из списка в поле Код. В результате откроется диалоговое окно Пользовательский Авто­фильтр. Укажите в этом окне критерии, которым должны соот­ветствовать записи. Выберите в первом списке оператор сравнения >== и введите в поле ввода значение 3000. Затем установите опцию И, во втором списке выберите оператор срав­нения < и укажите в поле ввода значение 4000. После нажатия кнопки ОК в списке будут представлены только значения, соот­ветствующие заданному критерию. Списки, полученные вследствие фильтрации, а также столбцы, в которых был применен автофильтр, выделяются: номера ото­бранных записей (строк) и стрелка на кнопке окрашиваются в синий цвет.

Использование имен диапазонов Довольно большое значение в нашей последующей работе будут иметь имена, присваиваемые диапазонам ячеек. Необходимость назначения имен обусловлена тем, что при использовании функ­ций задавать аргументы с помощью имен диапазонов гораздо проще, кроме того, не будет ограничений на количество введен­ных данных. Выделите столбец А, щелкнув на его заголовке, и в поле имени строки формул введите имя Фирма. Затем присвойте столбцу В имя Код, а столбцу I — имя Скидка. Если Вы ввели одно из имен неправильно и хотели бы удалить его, вызовите команду Имя/Присвоить меню Вставка. В появившемся диалоговом окне укажите имя и нажмите кнопку Удалить.

Контрольные практические вопросы

1. Создание рабочей книги;

2. Ввод записей;

3. Поиск записей;

4. Форматирование таблиц;

5. Сортировка данных;

6. Создание фильтра, пользовательского автофильтра.

Управление данными в Excel

(Занятие № 2)

Создание списка товаров

Второй список будет содержать данные о предлагаемых нами товарах. Каждому товару следует присвоить номер, что в даль­нейшем позволит автоматизировать выполнение некоторых операций. Перейдите на второй лист рабочей книги, щелкнув на ярлычке Лист2, и присвойте ему имя Товары. Список, создаваемый на рабочем листе Товары, должен состоять из трех столбцов: Номер, Наименование товара и Цена. Введите указан­ные заголовки в ячейки А1—С1 и присвойте имена ячейкам столбцов А, В, и С: Номер, Товар и Цена. Теперь можно приступить к вводу данных. Желательно, чтобы номера были расположены по возрастанию. В противном случае после ввода данных упорядочите элементы списка по номерам товаров. Вы можете ввести собственные данные или воспользоваться дан­ными, представленными на рисунке. Аббревиатуры после на­званий принтеров означают: Ц— цветной, ЧБ— черно-белый.

Создание списка заказов

Цветные документы выглядят лучше черно-белых. Поэтому мно­гие примеры составлены так, чтобы Вы научились работать с цветами. В дальнейшем ячейки, которые предназначены для ввода исход­ных данных, будем выделять голубым цветом, а ячейки, содержи­мое которых вычисляется автоматически на основе формул, — желтым цветом. Итак, сначала определим основные функции создаваемого спи­ска. Список должен служить подспорьем при управлении данны­ми о заказах, выполненных в текущем году. Мы попытаемся максимально автоматизировать процесс ввода данных. Впослед­ствии представленные в списке данные будут подвергнуты ана­лизу с помощью Мастера сводных таблиц.

Начнем с определения структуры списка. В ячейки A 1— L1 вве­дите заголовки столбцов: Месяц, Дата, Номер заказа, Номер товара, Наименование товара, Количество, Цена за ед., Код заказчика, Название фирмы, Сумма заказа, Скидка, Уплачено

Выделите строку с заголовками столбцов, установите параметры шрифта, выровняйте заголовки столбцов по центру, щелкнув на кнопке По центру в панели инструментов Форматирование, и разрешите перенос слов в пределах одной ячейки (для этого вызовите команду Ячейки меню Формат и в открывшемся диало­говом окне в панели Выравнивание активизируйте опцию Пере­носить по словам). Как и на предыдущих рабочих листах, мы присвоим ячейкам некоторых столбцов имена. Выделите по очереди столбцы В, С, D, Е, F, G, Н, I, J, К, L и введите в поле имени имена Дата, Заказ, Номер2, Товар2, Количество, Цена2, Код2, Фирма2, Сумма, Скидка2 и Оплата. Возможно, Вам покажется чрезмерным количество введенных имен, однако смеем заверить, что в дальнейшем они помогут сэкономить время. Вводить значения мы пока не будем. На данном этапе мы установим параметры и определим, какие значения, в каких полях должны быть указаны.

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

Заданне формул Каждая ячейка рабочего листа может содержать не только текст или числовое значение, которое можно использовать при вычислениях, но и формулу. Результат в ячейке с формулой зависит от содержи­мого тех ячеек, на которые в данной формуле есть ссылки. Доступ к данным, хранящимся в ячейке таблицы, обеспечивается путем задания ссылки на ячейку. Ссылка содержит адрес ячейки. Путем указания адреса можно использовать при вычислениях содержимое любой ячейки рабочего листа. Формула может содержать функции и математические операторы, порядок выполнения которых соответствует принятому в матема­тике. Результатом вычисления формул, включающих арифмети­ческие операторы, являются числовые значения. Например, в результате ввода в ячейку В7 формулы =(В2+ВЗ+В4)/3 Вы полу­чите среднее арифметическое значение чисел, находящихся в ячейках В2, ВЗ и В4.

Использование функций В вычислениях могут использоваться разнообразные функции, например, для определения синуса или среднего арифметическо­го. При этом в качестве аргумента может выступать константа ссылка на ячейку или имя диапазона ячеек. Excel 7.0 предостав­ляет в распоряжение пользователей множество специальных функ­ций, в которые эти формулы уже встроены. Указание значений, к которым должна быть применена та или иная функция, проис­ходит путем задания аргументов. Способ задания всех функций один и тот же, различие заклю­чается только в количестве аргументов:

=ИМЯ ФУНКЦИИ (Аргументы)

Так, для вычисления суммы предназначна функция с вырази­тельным названием СУММ. Если Вы запомнили название функ­ции, то можете смело вводить его в ячейку с клавиатуры. Аргументы функции должны быть указаны после ее названия в круглых скобках (например, СУММ(А1;С4)). Введите открываю­щую круглую скобку и выделите с помощью мыши ячейки, содержимое которых должно использоваться в качестве аргумен­тов. Адрес выделенного диапазона ячеек сразу отобразится в позиции курсора ввода. Завершите задание функции вводом закрывающей скобки и нажатием клавиши [Enter]. Ячейки, содержимое которых используется в качестве аргумен­тов, могут принадлежать как к смежным, так и к несмежным диапазонам. Адрес диапазона смежных ячеек задается путем указания адресов первой и последней ячеек (адреса двух ячеек разделяются двоеточием). Адреса несмежных ячеек разделяются точкой с запятой. Символы разделения вставляются в формулу автоматически при указании диапазона ячеек с помощью мыши.

Мастер функций С функциями наиболее удобно работать, используя Мастер функ­ций. В этом случае аргументы задаются в диалоговом окне (при вводе функции с клавиатуры можно допустить ошибку, например, пропустить обязательный аргумент). При работе с Мастером функций сначала указывается функция, а затем — ее аргументы. Для запуска Мастера функций предна­значены команда Функция из меню Вставка и комбинация клавиш [Shift+РЗ].

В программе Мастер функций функции разбиты на категории. Полный список категорий находится в поле Категория. К катего­рии Полный алфавитный перечень относятся все доступные в программе функции, а к категории 10 недавно использовавшихся функций, применявшихся последними.

Чтобы выбрать функцию, выделите имя категории, к которой она принадлежит. Если категория неизвестна, воспользуйтесь катего­рией Полный алфавитный перечень. При выделении любой функ­ции под полем Категория появляется краткая информация о ней.

Если краткой информации недостаточно, нажмите в диалоговом окне кнопку Справка (или воспользуйтесь клавишей [FI]). В резуль­тате на экране отобразится страница справочной подсистемы с информацией о выделенной функции.

После выбора функции выполните щелчок на кнопке Далее для перехода в следующее диалоговое окно Мастера функций, в котором должны быть заданы аргументы. В этом диалоговом окне программа информирует пользователя о том, какие аргументы должны быть указаны обязательно (обязательные аргументы), а какие — нет. По завершении ввода аргументов следует нажать кнопку Готово, и результат вычисления будет представлен в соответствующей ячейке. Теперь перейдем от теории к практике. В столбце Е должно быть указано наименование товара. Вряд ли Вы откажетесь от автома­тической вставки данных с помощью формулы. Для этого введите в ячейку Е2 формулу:

= ЕСЛИ($D2=""; ""; ПРОСМОТР ($D2; Номер; Товар)

Думаем, что приведенная формула требует небольших пояснений. Функция ЕСЛИ обеспечивает проверку содержимого ячейки D2. Если в ячейке D2 данные отсутствуют, то ячейка Е2 также останется незаполненной. Если ячейка D2 содержит номер това­ра, произойдет поиск номера товара в диапазоне Номер, а в ячейку D2 будет занесено соответствующее значение диапазона Товар. Для этого мы и используем функцию ПРОСМОТР. Вставьте данную формулу в ячейку Е2 с помощью Мастера функций (для активизации Мастера функций воспользуйтесь одноименной кнопкой в панели инструментов Стандартная), если Вы не уверены, что аргументы в ячейке указаны правильно. Обратите внимание, что для ячейки D2 задана комбинированная ссылка, благодаря которой при копировании формулы будет изменен номер строки.

Столбец F предназначен для указания количества товара, зака­занного клиентом, а столбец G (поле Цена за ед.) — для указания цены единицы товара. Значения цен можно вставить с помощью формулы, аналогичной формуле в ячейке Е2. Формула в ячейке G2 должна иметь такой вид:

= ЕСЛИ($D2=""; ""; ПРОСМОТР ($D2; Номер; Цена)

Целесообразно скопировать формулу из ячейки Е2 в ячейку G2 и затем только изменить имя диапазона. Значение (пробел), пред­ставленное в ячейке как результат применения формулы, убедит Вас в ее правильности. Наверное, Вы уже заметили, что иногда при указании адреса ячейки в ссылке используется символ $. О том, в каких случаях следует применять данный символ, читайте в следующих строках.

Копирование формул. При копировании формул содержащиеся в них ссылки автомати­чески заменяются новыми только в том случае, если они являются относительными. По умолчанию в Excel применяются относи­тельные ссылки. Они используются в формулах для указания адреса ячейки относительно позиции ячейки, содержащей фор­мулу. При изменении позиции формулы относительные ссылки будут содержать адреса других ячеек. Если при перемещении или копировании формулы адреса ячеек не должны изменяться, следует использовать абсолютные ссылки. Абсолютную ссылку можно установить, задав имя или вставив символ $ в адрес ячейки (например $А$1). Можно создать ком­бинацию абсолютной и относительной ссылок, например $А1. В этом случае при копировании формулы в ссылке изменяется только строка, а столбец все время остается тем же. Итак, продолжим выполнять наш пример. В ячейке Н2 следует указать код фирмы-заказчика. Код придется ввести с клавиатуры, поскольку невозможно предвидеть, какой будет очередность за­казов, и задать автоматическое (с помощью одной из формул Excel) заполнение ячеек этого столбца. Автоматически мы запол­ним ячейки столбцов Название фирмы и Скидка. Теперь анализи­руемой ячейкой будет ячейка Н2. Введите в ячейку I2 формулу:

= ЕСЛИ($Н2=""; ""; ПРОСМОТР ($Н2; Код; Фирма)

В столбце Сумма заказа следует указать общую стоимость заказа без учета скидок. Для этого умножьте значения в полях Количество и Цена. С помощью логической функции ЕСЛИ можно задать отмену заполнения ячеек в том случае, если запись не введена, что позволит избежать появления значений ошибки. Таким об­разом, формула в ячейке J2 должна иметь следующий вид:

= ЕСЛИ(F2=""; ""; F2*G2)

Если в ячейке F2 указано количество единиц заказываемого товара, то ячейка J2 должна содержать произведение значений ячеек F2 и G2. В противном случае она должна остаться незапол­ненной.

Величину скидки (столбец Скидка) также можно определять автоматически. Для этого достаточно ввести в ячейку К2 формулу:

= ЕСЛИ($Н2=""; ""; ПРОСМОТР ($Н2; Код; Скидка)

В завершение осталось определить сумму, подлежащую выплате. Для этого введите в ячейку L2 следующую формулу:

= ЕСЛИ($J2=""; ""; J2-J2*K2)

Значительная часть работы по заполнению таблицы уже сделана. Осталось выполнить форматирование: центрировать значения, назначить стили (денежный и процентный), а также заполнить ячейки, в которые должны быть введены значения, голубым цветом, а ячейки, в которых значения будут определены на основе формул, — желтым. Выделите ячейки В2— L2 и выберите в меню Правка команду Заполнить/Вниз. Таким образом мы задали копирование значений ячеек сроки 2 в остальные ячейки. Теперь все готово к вводу записей. Введите несколько записей и проверьте правильность определения значений. После ввода нескольких записей для первых трех месяцев (дней, недель, лет) может возникнуть необходимость подвергнуть дан­ные списка фильтрации, например, чтобы определить, какой клиент пользовался услугами фирмы наиболее часто или насколь­ко популярным был тот или иной товар. Используйте для этого функцию автоматической фильтрации. После вставки автофильт­ра выбор нужных записей не составит труда.