Использование имен диапазонов

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

КОНТРОЛЬНЫЕ ПРАКТИЧЕСКИЕ ВОПРОСЫ

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

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

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

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

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

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

Управление данными в Excel (Занятие № 2)

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

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

Рисунок 2.1 – Таблица списка товаров

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

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

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

Форматирование таблицы

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

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

Заданне формул

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

=(В2+ВЗ+В4)/3 [1]

Вы полу­чите среднее арифметическое значение чисел, находящихся в ячейках В2, ВЗ и В4.

Использование функций

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

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

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

Мастер функций

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

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

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

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

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

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

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

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

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

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

Копирование формул

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

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

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

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

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

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

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

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

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

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

Рисунок 2.2 – Таблица списка заказов

КОНТРОЛЬНЫЕ ПРАКТИЧЕСКИЕ ВОПРОСЫ

1. Форматирование ячеек таблицы;

2. Задание формул;

3. Задание функций;

4. Мастер функций;

5. Функции СУММ, ПРОСМОТР, ЕСЛИ