Электронные таблицы EXCEL-97. Сборник лабораторных работ

Microsoft Excel 97. Работа № 1 Знакомство с электронными таблицами Excel. Цель работы: Знакомство с основными приемами работы в Excel и встроенными функциями этой программы на примере таблицы выручки от продаж в различных магазинах (рис. 1). Электронная таблица – компьютерный эквивалент обычной таблицы, в клетках (ячейках) которой записаны данные различных типов: тексты, числа, даты, формулы. Главное достоинство электронной таблицы – это возможность мгновенного пересчета всех данных, связанных формульными зависимостями при изменении значения любой исходной ячейки. Отличительной чертой Excel является то, что рабочее поле имеет вид таблицы, где имена столбцов – латинские буквы, а строки имеют нумерацию. Также курсор мыши имеет совершенно иной вид – , чем в текстовом редакторе Word, где он выглядит мигающей чертой – |. На рис. 1 приведена только часть окна Excel. Проанализируем содержание таблицы. Таблица находится в диапазоне ячеек А1:F7. В строках 1 и 2 находятся заголовки таблицы, выровненные по центру всех соответствующих столбцов таблицы. Столбец А расширен, так как записи в столбце А превышают стандартную ширину столбца (т.е. ширину столбца по умолчанию). В таблице применено обрамление снизу в строках 3, 6 (тонкая линия) и 7 (полужирная линия). В строках 4, 5 и 6 по столбцам B, C, D, E и F вводятся значения выручки, полученной от каждого магазина. В строке 7 отображается сумма выручки, полученная магазинами в каждом месяце.

Рис. 1. Таблица "Выручка от реализации книжной продукции" 1. Запустите программу Excel (Пуск–Программы–Microsoft Excel). 2. Ввод текста. В Excel существует несколько различных способов подтверждения ввода текста, формул и т.п. и очистки содержимого ячейки. Установите курсор мыши на ячейку А1 и щелкните левой кнопкой: ячейка будет окружена темной рамкой с точкой в правом нижнем углу, а в поле имени отображается адрес этой ячейки – А1. Ячейка А1 стала активной. 3

Введите с клавиатуры текст: Выручка от реализации книжной продукции

! В строке формул появляется вводимый текст с мигающим курсором – |. Перед текстом появились три кнопки: кнопка с крестиком – "отмена", эквивалентна клавише Esc кнопка с "галочкой" – "Ввод", эквивалентна клавише Enter кнопка со знаком = – изменить формулу строка формул – отражает содержимое активной ячейки

Для сохранения внесенного текста в текущей ячейке рабочего листа необходимо дать подтверждение. Это можно сделать разными способами: ¾ нажать клавишу Enter; ¾ нажать кнопку с галочкой слева от строки формул; ¾ щелкнуть мышью на другой ячейке; ¾ покинуть ячейку с помощью клавиш управления курсором (стрелки). Для удаления текста нужно сделать активной ячейку с текстом или формулой, затем: ¾ нажать на клавиатуре клавишу Delete; ¾ выбрать команду Правка–Очистить–Все; ¾ нажать правую кнопку мыши, из контекстного меню выбрать команду Очистить содержимое.

! Если

вы случайно удалили содержимое ячейки, выберите команду Правка–Отменить очистку либо нажмите левой кнопкой мыши на панели инструментов "Стандартная" кнопку

.

Задание. Примените различные способы подтверждения и очистки содержимого ячейки с текстом. 3. Центрирование заголовка по столбцам таблицы.

! Введенный текст по длине превышает ширину столбца. Так как это заголовок, то нам следует объединить ячейки от А1 до F1 включительно и выровнять текст по центру. Сделайте активной ячейку А1. Курсор мыши должен находиться на этой ячейке. Нажмите левую кнопку мыши и, удерживая ее, протащите выделение по первой строке до ячейки F1. Нажмите на панели инструментов Форматирование кнопку Объединить и поместить в центре

4

4. Введите текст в ячейки столбца А.

! Обратите внимание, что после каждого подтверждения ввода (Enter), активной становится следующая ячейка. А2: Осень 1994 А3: (пропустить) А4: Книжный мир А5: Книга А6: Магазин №1 А7: Всего

! Длина текста в А4 больше ширины столбца и закрывает ячейку B4. Рассмотрим способы изменения ширины столбца: 5. Изменение ширины столбца. 5.1.

5.2. 5.3. 5.4.

5.5.

Выберите столбец, ширину которого необходимо изменить (щелкните на названии столбца либо сделайте активной ячейку в этом столбце). Для изменения ширины нескольких столбцов необходимо щелкнуть мышью на названии столбца и удерживая кнопку мыши, протянуть до нужного (столбцы будут выделены цветом) либо выделите несколько ячеек в этих столбцах. Выберите команду Формат–Столбец–Ширина и задайте числовое значение ширины столбца (в мм), затем нажмите OK. Чтобы установить ширину столбца, соответствующую его содержимому, достаточно выбрать команду Формат–Столбец–Автоподбор ширины. Если выделено несколько ячеек, то данная команда будет выполнена для всех этих ячеек. Можно изменить ширину столбцов при помощи перетаскивания границы столбца. Для этого поместите курсор мыши в строку имен столбцов на границу между столбцами. Курсор изменится и примет вид . Нажмите левую кнопку мыши и удерживая её перетащите границу. (В Excel это действие аналогично действию при работе с таблицей в Word). Для изменения ширины нескольких столбцов одновременно, используя мышь, необходимо выделить несколько столбцов (п.5.1.), затем выполнить действия описанные в п. 5.4.

! Обратите внимание, что при изменении ширины нескольких столбцов одновременно, их ширина становится одинаковой. Задание.

Измените ширину столбца, используя различные способы. Измените ширину нескольких столбцов одновременно.

6. Форматирование текста в ячейках. Названия месяцев надо выровнять по центру. Для этого можно поступить следующим образом: 1-й способ. 1.1. Щелкнуть на имени строки 3. Строка будет выделена цветом. 1.2. Выбрать на панели инструментов Форматирование кнопку нужного типа выравнивания – По центру. При таком способе текст будет выровнен одинаково во всех ячейках строки 3. 5

2-й способ. 2.1. Выделить нужный диапазон ячеек A3:F3. Для этого поместите курсор в ячейку A3, нажмите кнопку мыши и, удерживая ее, протащите курсор до ячейки F3. Нужный диапазон ячеек будет выделен. 2.2. Поступите аналогично п. 1.2. При этом способе текст будет выровнен одинаково только в данном диапазоне ячеек, остальные ячейки останутся без изменений. 7. Простые вычисления. Ввод формулы. Сумма. Автосуммирование. На примере суммирования выручки по каждому месяцу рассмотрим способы суммирования. Ввод формулы сложения. Сумма. Поместите курсор в ячейку В7. Наберите знак равенства =

! Формула в ячейке всегда должна начинаться со знака =. В ячейке В7 должна быть задана операция – формула сложения нескольких данных: = содержимое ячейки В4 + содержимое ячейки В5 + содержимое ячейки В6. Эту операцию можно выполнить двумя способами. 1-й способ: Ввод формулы. 1.1. За знаком равенства надо непосредственно напечатать В4+В5+В6 в строке формул появится запись: =В4+В5+В6. Для подтверждения нажмите Enter.

! Обратите внимание на каком языке включена клавиатура, должен быть английский, т.к. названия столбцов являются латинскими буквами, а не русскими. 1.2.

После того, как Вы ввели знак равенства, щелкните левой кнопкой мыши на ячейке В4, напечатайте +, затем щелкните на ячейке В6, еще раз напечатайте + и щелкните на ячейке В7. Если Вы подтвердите формулу, нажав Enter, то в ячейке В7 появится значение суммы.

! Если Вы не ввели знак равенства, то в ячейке появится не результат, а текст: В4+В5+В6. 2-й способ: Автосуммирование. Для того, чтобы сэкономить время при работе с таблицей, в Excel есть функция автоматической суммы – автосуммирование. Кнопка с изображением знака суммы находится на панели инструментов Стандартная. Поместите курсор в ячейку С7, и нажмите кнопку автосуммирования.

!

–

Обратите внимание: в ячейке С7 и в строке формул автоматически появилась формула =СУММ(С4:С6), где СУММ – функция суммирования; С4:С6 – диапазон суммируемых ячеек (обозначен пунктирным мерцающим прямоугольником). Подтвердите формулу, в ячейке отразится результат суммирования. Задание.

Любым способом найдите сумму выручки за Сентябрь и Октябрь. 6

!!

Если Вы используете функцию автосуммирования при подсчете выручки за Октябрь, то Вам будет предложена сумма по 7-й строке – =СУММ(В7:Е7), а пунктирный прямоугольник будет охватывать соответствующие ячейки этой строки. Что нужно сделать? 1-й вариант: Вручную с клавиатуры ввести правильную формулу, т.е. набрать адреса первой и последней ячеек через двоеточие: =СУММ(F4:F6). 2-й вариант. После нажатия кнопки Автосуммирование измените диапазон суммируемых ячеек мышью: левой кнопкой мыши щелкните на ячейку F4 и удерживая ее расширьте диапазон до ячейки F6. В скобках формулы будет автоматически проставлен правильный диапазон складываемых ячеек, а пунктирный мерцающий прямоугольник покажет этот диапазон на экране. 8. Копирование содержимого ячеек. В Excel копирование содержимого ячеек можно произвести несколькими способами, рассмотрим некоторые из них: 1-й способ: ¾ Щелкните мышью на ячейке С7. Появится обрамление ячейки в виде прямоугольника с небольшим квадратом в правом нижнем углу. ¾ Затем в меню Правка выберите команду Копировать (рамка ячейки станет пунктирной мигающей линией). ¾ Перейдите на ячейку D7. Затем в меню Правка выберите команду Вставить (или сразу нажмите клавишу Enter). ¾ Для того чтобы снять команду копирования с ячейки С7 нажмите клавишу Esc. 2-й способ: ¾ Щелкните мышью на ячейке С7. Появится обрамление ячейки в виде прямоугольника с небольшим квадратом в правом нижнем углу. ¾ Затем правой кнопкой мыши вызовите контекстное меню выберите команду Копировать (рамка ячейки станет пунктирной мигающей линией). ¾ Перейдите на ячейку D7. Затем правой кнопкой мыши вызовите контекстное меню выберите команду Вставить. 3-й способ: Автозаполнение ячеек. Щелкните в ячейку С7. Появится обрамление с небольшим квадратом в правом нижнем углу. Подведите курсор мыши к квадратику в правом нижнем углу, курсор изменит вид на Нажмите левую кнопку мыши и удерживая ее переместите квадратик на ячейку D7.

! Обратите внимание на то, что в ячейку D7 скопировалось не просто числовое значение, а формула суммы, при этом автоматически изменился диапазон суммируемых ячеек. Формула стала иметь следующий вид: =СУММ(D4:D6). Задание.

Попробуйте различные способы копирования. Перед каждым способом очистите содержимое соседних ячеек по строке 7. Что происходит с формулой при копировании ячейки С7 в ячейку F7?

7

9. Оформление таблицы - Форматирование. Внешний вид таблицы можно изменить с помощью многочисленных вспомогательных форматирующих функций, некоторые из них легко вызываются с помощью кнопок на панели инструментов Форматирование. Форматирование шрифта: тип шрифта, размер, написание (полужирный, курсив, подчеркивание), выравнивание осуществляется в ячейке или в выделенных ячейках аналогично операциям форматирования в редакторе Word. Остановимся подробнее на обрамлении таблицы и заполнении цветом. Обрамление. Выделите нужный диапазон ячеек: A3:F3. 1-й вариант: На панели форматирования нажмите справа от кнопки Границы треугольник и выберите нужный тип обрамления: линия снизу. Выделенные ячейки будут подчеркнуты снизу. 2-й вариант: Выберите команду Формат–Ячейки–Граница. Из диалогового окна выберите нужный тип границы (обрамления), укажите к какой части ячейки или всего диапазона ячеек его надо применить и нажмите ОК. Заполнение цветом. Выделите нужный диапазон ячеек. 1-й вариант. Нажмите справа от кнопки Цвет заливки кнопку с треугольником, выберите нужный цвет. Выделенные ячейки будут залиты выбранным цветом. 2-й вариант. Выберите команду Формат–Ячейки–Вид. В диалоговом окне выберите нужный цвет и нажмите ОК. 10. Сохранение файла. Эта операция ничем не отличается от аналогичной операции в текстовом редакторе Word. Обратите внимание, что файлы рабочих книг в Excel имеют расширение *.xls. (В текстовом редакторе Word – файлы имеют расширение *.doc). При сохранении файла внесите в поле "Имя файла" свое название файла с клавиатуры (нужно щелкнуть мышью внутри этого поля и стереть предложенное программой название). Имя Вашего файла должно отражать его суть, поэтому назовите его так: Выручка от книг затем добавьте свою фамилию.

! Проверьте в диалоговом Окне сохранения все параметры сохраняемого файла: ¾ Папку в которой будет сохранен файл; ¾ Имя файла; ¾ Расширение файла (должно быть Книга Microsoft Excel (*.xls))

8

11. Совместное использование приложений Windows. Как Вы уже знаете, в ОС Windows имеется возможность одновременной работы с несколькими приложениями. Не закрывая приложение Excel, запустите другое приложение – текстовый редактор Word. (Пуск–Программы– Microsoft Word). Откройте файл Таблицы для работы № 1 Excel.doc, который находится в папке: С:\ Мои документы \ Заготовки для Word. Сразу же сохраните этот файл в свою папку под новым именем – "Панели инструментов в Excel" и добавьте свою фамилию. Задание.

Заполните (впечатайте) в графы таблиц 1 и 2 напротив изображений кнопок панелей инструментов Excel Стандартная и Форматирование их назначение, при этом используйте всплывающие подсказки у каждой кнопки на этих панелях инструментов в приложении Excel или Справочной системой Excel.

Указание. Для быстрого переключения между приложениями можно использовать комбинацию клавиш Alt+Tab, либо на панели задач мышью поочередно выбирать необходимое Приложение.

9

Образец к заданию п. 11 Таблица 1. Назначение кнопок панели инструментов Стандартная Заполните таблицу по образцу, используя информацию из контекстной подсказки и текста Помощи. Команда Создать

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

Открыть

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

Масштаб

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

10

Образец к заданию п. 11 Таблица 2. Назначение кнопок панели инструментов Форматирование Заполните таблицу, используя информацию из контекстной подсказки и текста Помощи. Команда

Расшифровка команды

11

Microsoft Excel 97. Работа № 2 Форматирование ячеек. Рабочие листы Excel. Цель работы: Изучение свойств и форматов ячеек, определение форматов чисел, времени и даты. Основные приемы работы с рабочими листами в Excel: переименование, добавление, удаление и перемещение листов. 1. Форматы ячеек. В Excel каждая ячейка обладает многими свойствами, в том числе форматами отображения информации на экране и выводе ее на печать, которые можно изменить или оставить их по умолчанию, т.е. в том формате, который использует и предлагает сама программа (рис. 1).

Рис. 1. Диалоговое окно Формат ячеек Основные свойства диалогового окна Формат ячеек: Число – закладка для выбора числового формата, определения количества десятичных

знаков, выбора обозначения введенного числа; Выравнивание – закладка для определения местоположения и ориентации информации

(числа или текста) внутри ячейки, а также задание требования по переносу слов в ячейке ниже и др. Шрифт – закладка для выбора шрифта, его начертания, размера, а также определение цвета, способа подчеркивания и специальных эффектов (зачеркнутый, верхний индекс, нижний индекс); Граница – закладка для выбора типа линии ее цвета и места расположения на сторонах ячейки (возможно проведение диагоналей в ячейке); Вид – закладка для выбора фонового цвета и типа узора при заливке ячейки; Защита – закладка для установки защиты на одну или несколько ячеек от несанкционированного изменения или удаления данных. ВНИМАНИЕ !!! В случае задания пароля на защищаемую ячейку – ПОМНИТЕ ЕГО!!! Иначе придется удалить весь рабочий лист или весь файл !!! !!! Совет начинающим: не используйте пароли в начале освоения Excel, да и потом с большой осторожностью. 12

По умолчанию все числа представляются в общем формате. В этом формате число может состоять максимум из 11 цифр, если позволяет ширина ячейки. Числа отображаются как целые (например, 21947 или 12387), или с десятичной точкой (запятой) (например, 12,57 или 3+14159), или в научной (экспоненциальной) форме (например, 9,43Е+7 или 21, 212Е-5). При вводе числа Excel пытается подобрать подходящий формат. Если числа не очень большие и не очень маленькие (до 8 знаков), то Excel присваивает им общий формат. Если число содержит более 8 знаков, то Excel выводит число в научном (экспоненциальном) формате. Например, число 123456789012 запишется в виде 1,23E+11, что означает 1, 23 е11, где е=2,7. Помимо того, что числа в ячейках могут отображаться в различных числовых форматах многие форматы имеют дополнительную возможность изменения количества десятичных знаков, а некоторые даже позволяют выбрать обозначение введенного числа (например 1,23 р.) В таблице 1 приведена дополнительная информация о представлении чисел при использовании различных форматов ячеек, представлении даты и времени, а также дополнительные форматы в Excel. Таблица 1. Образец числа 13,5 13,50 13,500 13,50 р. $

13,50

135,13 р. 1 Январь, 1998 12:00 1350,00% 13 ½ 13 50/100 1,35E+01 13,5 000014 00000-0014 -14 0014

Числовые форматы ячеек Общий Числовой, число десятичных знаков = 2 Числовой, число десятичных знаков = 3 Денежный, обозначение валюты – р. Финансовый, число десятичных знаков = 2, обозначение валюты – $ Английский США Финансовый, число десятичных знаков = 2, обозначение валюты – нет Дата Время Процентный, число десятичных знаков = 2 Дробный с половинными долями Дробный с сотыми долями Экспоненциальный Текстовый (число воспринимается как текст и не участвует в расчетах при обращении к нему формулы) Дополнительные форматы: почтовый индекс индекс + 4 номер телефона табельный номер (Все форматы):

Раздел содержит все типы перечисленных числовых форматов по порядку

13

!!! Прежде чем нажимать на кнопки, сначала прочитайте задание и указания к его выполнению. Задание.

Для изучения форматов представления чисел заполните таблицу, показанную на рис. 2. В ячейки столбцов, форматы которых приведены в заголовках таблицы, поразрядно вводятся целые числа от 1 до 12345678901 – см. числа в столбце С.

Указания: 1) Для оформления общего заголовка таблицы "Форматы чисел" – воспользуйтесь кнопкой "Объединить и поместить в центре" (работа № 1). Аналогично оформите заголовок таблицы в столбцах В и С. 2) Для размещения заголовков таблицы во всех столбцах следует выделить всю 2-ю строку, затем щелкнуть на ней правой кнопкой мыши, из контекстного меню выберите команду Формат ячеек. 3) В диалоговом окне Формат ячеек откройте закладку Выравнивание, установите флажок в поле Переносить по словам, выберите в поле По вертикали – По центру, По горизонтали – По центру, затем нажмите OK. 4) Внимательно изучите содержимое рисунка 2. 5) Введите числа в ячейки столбца А, основываясь на содержимом столбца С. 6) Чтобы не тратить много времени на заполнение всех ячеек таблицы, скопируйте ячейки с числами из столбца А в остальные столбцы, кроме G, т.к. целые числа не могут быть отражены в дробном виде.

Рис. 2. Таблица для изучения форматов чисел 7) После заполнения таблицы задайте формат ячеек в каждом столбце. Для этого нужно выделить столбец (щелкнуть на имени столбца), затем правой кнопкой мыши вызвать контекстное меню и выбрать Формат ячеек. В диалоговом окне в закладке Число, следует выбрать из списка "Числовые форматы" нужный формат числа (название формата чисел написано в заголовке таблицы этого столбца), затем установите количество десятичных знаков (если необходимо, выберите "Обозначение"). 14

Примечание: Вы, можете задать формат каждой ячейке в отдельности или сразу выделенному диапазону нескольких ячеек. Ширину столбца С установите такой, чтобы число в 13-й строке умещалось полностью.

!!! Если в ячейке вместо числа появляется решетка ( ######## ), то это означает, что число в данном формате не умещается в этой ячейке. Задание.

Проследите за отображением чисел различных форматов при изменении ширины столбца. В столбец с форматом дробный введите дробные числа (из школьного курса математики известно, что 1,4 = 1 2/5).

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

текущий лист кнопки прокрутки листов Переименование рабочего листа. Для систематизации и облегчения поиска информации в рабочей книге удобнее присваивать названиям листов собственные имена, отражающие их смысловое содержание, например: "Реализация", "Цена", "Выручка" и т.п. Существует два способа выполнения данной операции. 1-й способ: ¾ Щелкните правой кнопкой мыши на ярлычке Лист1 из контекстного меню выберите команду Переименовать прежнее название листа закрасится черным цветом. ¾ Введите новое имя поверх старого и нажмите Enter. 2-й способ: ¾ Выберите двойным нажатием левой кнопки мыши нужный ярлычок листа. ¾ Введите новое имя поверх старого и нажмите Enter. Задание.

Переименуйте лист на котором находится таблица Форматы чисел с прежнего названия "Лист1" на новое "Форматы чисел". 15

Добавление рабочих листов. При работе в Excel часто требуется добавить один или несколько новых рабочих листов. Существует два способа добавления рабочих листов в рабочую книгу Excel. 1-й способ: ¾ В меню Вставка выберите команду Лист; ¾ Чтобы вставить несколько листов, выберите необходимое количество листов, удерживая нажатой клавишу SHIFT, затем из меню Вставка выполните команду Лист. (В этом случае добавится столько листов, сколько было выбрано первоначально). 2-й способ: ¾ Щелкните правой кнопкой мыши на ярлычке листа из контекстного меню и выберите команду Добавить...–Лист–OK; ¾ Чтобы вставить несколько листов, выберите необходимое количество листов, удерживая нажатой клавишу SHIFT, затем правой кнопкой мыши вызовите контекстное меню и выберите команду Добавить–Лист–OK. (В этом случае добавится столько листов, сколько было выбрано первоначально). Примечание: Обратите внимание, куда вставились новые листы. Какие у них имена? Удаление рабочих листов. При удалении одного или нескольких листов в Excel необходимо знать, что после их удаления отменить данную операцию невозможно ! При удалении рабочих листов программа Excel выдает предупреждение о невозможности отмены данной операции и два варианта выхода из этой ситуации: кнопка OK – процедура удаления будет продолжена и листы удалятся; кнопка Отмена – процедура удаления будет остановлена (отменена), Существует два способа удаления рабочих листов. 1-й способ: ¾ Активизируйте лист; ¾ В меню Правка выберите команду Удалить лист; ¾ Чтобы удалить несколько листов, выберите необходимое количество листов, удерживая нажатой клавишу SHIFT, затем из меню Правка выберите команду Удалить лист. (В этом случае удалится столько листов, сколько было выбрано первоначально). 2-й способ: ¾ Активизируйте лист; ¾ Щелкните правой кнопкой мыши на ярлычке листа из контекстного меню и выберите команду Удалить; ¾ Для удаления нескольких листов, выберите необходимое количество листов, удерживая нажатой клавишу SHIFT, затем правой кнопкой мыши на ярлычке листа вызовите контекстное меню и выберите команду Удалить (В этом случае удалится столько листов, сколько было выбрано первоначально). 16

Перемещение рабочих листов. В Excel существует три способа перемещения рабочих листов. 1-й способ: ¾ Активизируйте лист; ¾ В меню Правка выберите команду Переместить/Скопировать лист; ¾ В диалоговом окне левой кнопкой мыши выберите, перед каким листом должен расположиться выбранный рабочий лист, затем нажмите OK; 2-й способ: ¾ Активизируйте лист; ¾ Правой кнопкой мыши вызовите контекстное меню и выберите команду Переместить/Скопировать...; ¾ В диалоговом окне левой кнопкой мыши выберите, перед каким листом должен расположиться выбранный рабочий лист, затем нажмите OK; 3-й способ: ¾ Левой кнопкой мыши "захватите" ярлычок листа и перенесите его в другое место. Примечание:

Если при перемещении листа держать нажатой клавишу Ctrl, произойдет перемещение копии листа и к его имени добавится цифра (2), в этом случае исходный лист останется на прежнем месте.

Microsoft Excel 97. Работа № 3 Относительные и абсолютные адреса ячеек. Графическое представление числовых данных. Цель работы:

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

1. Относительные и абсолютные ссылки Ссылкой однозначно определяется ячейка или группа ячеек листа, а также упрощается поиск значений или данных, используемых в формуле. С помощью ссылок можно использовать в формуле данные, находящиеся в различных местах листа, а также использовать значение одной и той же ячейки в нескольких формулах. Можно ссылаться на ячейки, находящиеся на других листах книги или в другой книге, или на данные другого приложения. Ссылки на ячейки других книг называются внешними ссылками. Ссылки на данные других приложений называются удаленными ссылками. По умолчанию в Excel используются ссылки типа A1, в которых столбцы обозначаются буквами от A до IV (256 столбцов максимально), а строки числами — от 1 до 65 536). Например, D50 является ссылкой на ячейку, расположенную в пересечении столбца D с 50-й строкой. Чтобы сослаться на диапазон ячеек, введите ссылку на верхний левый угол диапазона, поставьте двоеточие (:), а затем – ссылку на правый нижний угол диапазона. В следующей таблице представлены варианты возможных ссылок. 17

Нужно ввести:

Чтобы сослаться на: Ячейку столбца A и 10-й строки Диапазон ячеек столбца A с 10-й строки по 20-ю Диапазон ячеек в 15-й строке со столбца B по столбец E Все ячейки в 5-й строке Все ячейки между 5-й и 10-й строками включительно Все ячейки в столбце H Все ячейки между столбцами H и J включительно

A10 A10:A20 B15:E15 5:5 5:10 H:H H:J

В зависимости от поставленной задачи можно использовать: относительные ссылки, которые при копировании или перемещении ячейки с формулой ссылаются на другие ячейки относительно позиции ячейки с формулой; абсолютные ссылки, которые при копировании или перемещении ячейки с формулой всегда ссылаются на одну и ту же ячейку (ячейки). Различия между относительными и абсолютными ссылками. При создании формулы ссылки обычно изменяются относительно расположения ячейки, содержащей формулу. Например, ячейка B6 содержит формулу =A5; искомое значение находится на одну ячейку выше и левее ячейки B6. При копировании формулы, содержащей относительные ссылки, и вставке ее в другое место, ссылки будут указывать на другие ячейки. Например, если формула из ячейки B6 копируется в ячейку B7, то в ячейке B7 будет содержаться формула =A6, которая ссылается на одну ячейку выше и левее ячейки B7. Другой пример, если имеется формула, умножающая содержимое ячейки A5 на ячейку C1 (=A5*C1), то при копировании формулы в любую другую ячейку изменятся обе ссылки и названия столбцов, и номера строк, т.к. изначально формула содержит относительные ссылки на ячейки А5 и С1. Такой тип ссылок называется относительным. Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, нужно использовать абсолютные ссылки. Абсолютная ссылка бывает двух типов: частичная и полная. Частичная абсолютная ссылка при копировании или перемещении ячейки не изменяет тот параметр адреса ячейки, перед которым стоит знак $ – это либо столбец, либо строка. Полная абсолютная ссылка при копировании или перемещении ячейки не изменяет весь адрес ячейки, т.к. знак $ стоит и перед именем столбца, и перед номером строки. Чтобы создать полную абсолютную ссылку на ячейку C1, впечатайте знак доллара перед именем столбца и перед номером строки в адресе ячейки, например, =A5*$C$1. Задание.

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

18

Создание таблицы. Создайте таблицу по образцу (рис. 1). Эта операция аналогична заданию в Работе № 1 Ячейки в столбце F имеют формат Процентный, а количество десятичных знаков = 0. В ячейке F5 формула должна содержать абсолютную ссылку на ячейку Е9: =Е5/$E$9. В этом случае при копировании ячейки F5 в ячейки F6, F7, F8, абсолютная ссылка $E$9 останется неизменной, а будет меняться только относительная ссылка по столбцу Е (Е6, Е7, Е8). Для оформления таблицы используйте команду Формат–Автоформат. Выберите стиль оформления Простой.

Рис. 1. Таблица данных с использованием относительных и абсолютных ссылок. 2. Графическое представление числовых данных. Диаграммы и графики. Диаграммы и графики используются в современных документах для графического представления числовых данных. С их помощью можно легко и наглядно проанализировать состояние и проследить динамику изменения данных, кроме того, и это немаловажно, они украшают и "оживляют" отчеты. Очень интересно применение диаграмм и графиков в естественных, технических и экономических дисциплинах, где они наглядно демонстрируют различные показатели, а также динамику развития ситуации. В Excel имеется множество различных видов диаграмм и графиков, в том числе трехмерных. Кроме того, все графики имеют цветовую градацию, позволяющую визуально отличать их между собой. Возможность редактирования графиков и диаграмм открывает большие возможности при оформлении различных документов. На рис. 2 показаны два типа диаграмм: столбчатая гистограмма и круговая диаграмма. 23%

35% 30% 25% 20% 15% 10% 5% 0% 1

2

3

4

28%

30%

19%

Рис. 2. Представление одного набора данных в виде: а) гистограммы б) круговой диаграммы 19

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

Постройте внедренную диаграмму по данным ячеек F5, F6, F7, F8, как сказано ниже.

Создание диаграммы. В Excel построение диаграмм происходит по шагам, где на каждом этапе выбираются или задаются необходимые параметры для оформления диаграммы. Выделите в таблице ячейки F5:F8. на панели инструментов Стандартная или Нажмите кнопку Мастер диаграмм выберите команду Вставка – Диаграмма. В открывшемся окне Мастер диаграмм Тип диаграммы выберите Простую гистограмму и нажмите Далее >. В следующем окне: Источник данных диаграммы диапазон данных и показан образец диаграммы. Нажмите Далее >. В следующем шаге: Параметры диаграммы Вы можете ввести Заголовки, Оси, Линии сетки, Легенду, Подписи данных и Таблицу данных. На этом этапе Вы можете поэкспериментировать с оформлением диаграммы и затем нажать Далее >. В последнем окне: Размещение диаграммы предлагается поместить диаграмму на имеющемся листе, поэтому нажмите Готово (так же возможно размещение диаграммы на отдельном листе). Наконец, Вы можете переместить диаграмму, как любой графический объект, на нужное место рабочего листа.

20

Рис. 3 Таблица данных и внедренная диаграмма. Задание.

Измените табличные данные о продажах мороженого в округах в каком-либо месяце и обратите внимание на изменения в итоговых ячейках таблицы и в построенной диаграмме. Создайте аналогичную диаграмму и поместите ее на отдельном листе.

Редактирование диаграммы. Для изменения вида диаграммы или внесения дополнений (названия диаграммы, определения ряда для подписей по оси Х и т.п.) необходимо активизировать область диаграммы и вызвать Мастер диаграмм. Затем по шагам внести изменения в соответствующие поля различных закладок после завершения изменений нажать кнопку Готово. Указания. Обратите внимание, что если поместить указатель в различные области диаграммы, то появляется название элемента диаграммы. При нажатии правой кнопки мыши, вызывается контекстное меню выбранного элемента диаграммы, из которого можно выбрать соответствующую команду для изменения диаграммы. В этом задании Вам предоставляется возможность экспериментирования. Задание 3. Отредактируйте диаграмму: – – – – –

внесите название диаграммы Продажа мороженного за лето 1999 года; задайте подписи оси Х, используя данные диапазона ячеек А5:А8; задайте Объемный вариант Круговой диаграммы используя те же данные таблицы; измените имя текущего листа на новое – Мороженое. сравните полученный результат с рис. 4

21

Рис 4. Общий вид рабочего листа Мороженое с таблицей данных и двумя видами диаграмм. Расчет арифметической прогрессии и построение диаграммы. Формула для вычисления n-го члена арифметической прогрессии имеет вид:

an = a1 + d · (n–1). Сумма n первых членов – Sn арифметической прогрессии равна где

Sn = (a1 + an) · n/2,

a1 – первый член прогрессии, an – n-й член прогрессии, d – разность арифметической прогрессии.

! Следующие задания нужно выполнять на разных рабочих листах в одной рабочей книге. Задание.

Составить таблицу, вычисляющую n-й член и сумму арифметической прогрессии. Первый член прогрессии n1 равен –2, разность d равна 0,725. Построить точечную диаграмму для 10 членов и 10 сумм арифметической прогрессии как показано на рис. 5.

22

Рис. 5. Таблица с вычислениями n-го члена и суммы арифметической прогрессии с точечной диаграммой. Этапы выполнения задания. Создание таблицы. В ячейку А1 ввести заголовок "Вычисление n-го члена и суммы арифметической прогрессии". Оформите строку заголовка. При оформлении строки заголовка в ячейках C2 и D2 потребуется использовать нижний индекс. В строке формул выделите нужный символ и задайте ему способ отображения, для этого выберите команду Формат–Ячейки–Шрифт–Нижний индекс. Заголовки должны быть выровнены по центру, шрифт полужирный. Размер шрифта равен 12 (см. рис. 5). Разместите последовательность чисел от 1 до 10. Для этого введите в А3 число 1 и затем в А4 ячейку число 2. Выделите обе ячейки А3 и А4. В правом нижнем углу прямоугольника выделения находится небольшой квадратик – маркер автозаполнения. Поместите указатель мыши на маркер заполнения (при этом указатель примет вид тонкого креста), нажмите левую кнопку мыши и протащите указатель до ячейки А12.

!

При таком способе выделения ячеек для последующего автозаполнения (копирования) Excel "запоминает" принцип, по которому нужно заполнять ячейки, а справа появляется подсказка со значением числа в соответствующей ячейке. Введите в ячейку В4 значение разности арифметической прогрессии d: 0,725. Зацепите маркер автозаполнения и заполните нижние ячейки. В ячейку С3 введите значение первого члена арифметической прогрессии: –2. В ячейку С4 запишите формулу: =С3+В3, (первый член прогрессии+разность прогрессии). Выделите С4 и скопируйте формулу на оставшиеся ячейки. Обратите внимание, что имена ячеек изменяются, т.к. формула содержит относительную ссылку. 23

В ячейку D3 введите формулу, вычисляющую сумму n первых членов арифметической прогрессии, которая для записи в Excel имеет вид: =(-2+C3)*A3/2 или =($C$3+C3)*A3/2, что соответствует формуле нахождения суммы арифметической прогрессии. Скопируйте эту формулу на оставшиеся ячейки. Обратите внимание, что имя одной ячейки не изменяется, так как была задана абсолютная ссылка. Создание диаграммы. Выделите диапазон ячеек С3:D12 со значениями членов арифметической прогрессии и суммы членов арифметической прогрессии. Вызовите Мастер диаграмм и выберите тип График, затем График с накоплением, на котором отдельные значения помечены маркерами. После выполнения задания измените в ячейке С3 значение первого члена прогрессии на -3 и после этого придется в ячейках D3:D12 изменить формулы расчета суммы прогрессии, если была использована формула =(-2+C3)*A3/2, если же использовалась формула =($C$3+C3)*A3/2 изменения вносить не потребуется, т.к. она не содержит числового значения первого члена прогрессии. Обратите внимание на изменения точечной диаграммы. В завершении переименуйте Лист2 на Арифм. прогрессия. z Составленная таблица не очень удобна для анализа различных арифметических прогрессий, т.к. при изменении одного параметра прогрессии изменения приходится вносить во все соответствующие ячейки. Задание.

На Листе3 составьте универсальную таблицу для расчета арифметической прогрессии и анализа ее с помощью диаграмм – рис. 6.

Рис. 6 Универсальная таблица для анализа арифметической прогрессии 24

Указания. Первый член и разность арифметической прогрессии находятся в ячейках А3 и А4, соответственно. Формулы для вычисления значений членов арифметической прогрессии: ячейка В7: =А3; ячейка В8: = В7+$А$4 (используется формула с абсолютной ссылкой на ячейку А4). Эта формула копируется на оставшиеся ячейки столбца В. Обратите внимание на изменение относительных ссылок при копировании формулы. ячейка С7: =($А$3+В7)*А7/2 (содержит абсолютную ссылку на ячейку А3). Переименуйте Лист3 и дайте ему новое имя – Универс.таб.арифм.прогрес. Не забудьте построить диаграмму! После выполнения задания убедитесь в том, что полученная таблица "работает" при различных значениях исходных данных арифметической прогрессии.

Microsoft Excel 97. Работа № 4 Связанные таблицы. Цель работы: Связывание ячеек и таблиц одного рабочего листа. Связывание различных рабочих листов. Автоматизация расчетов данных на примере работы мебельного салона. Основные принципы формирования рабочей книги. Для правильной организации работы в электронных таблицах Excel сформируйте макет своей будущей рабочей книги, для этого продумайте состав рабочей книги (количество, имена и порядок расположения рабочих листов, их предполагаемое содержание, наличие графиков, диаграмм и т.п.). При создании таблиц определите подчиненность их между собой, а также общий вид, наличие заголовков и других составляющих (наименование шапки, наличие нумерации граф и т.д.). Данные делятся на две категории: исходные и расчетные. Исходные данные вводятся непосредственно с клавиатуры, формируя исходную базу данных. Расчетные являются результатом формульной зависимости связанных ячеек непосредственно с ячейками исходных (первоначальных) данных, либо с ячейками промежуточных расчетов, либо и тех и других.

25

Формирование рабочей книги. В лабораторной работе № 2 Вы уже познакомились с приемами переименования рабочих листов. Название рабочего листа должно отражать его смысловое содержание. Для облегчения дальнейших действий с таблицами на разных рабочих листах, их необходимо переименовать. Задание. Переименуйте рабочие листы: Лист1 – Закупка, Лист2 – Реализация, Лист3 – Цена, Лист4 – Выручка, Лист5 – Доход. Для этого, нажмите правой кнопкой мыши на ярлычке рабочего листа и выберите в контекстно-зависимом меню команду Переименовать, (см. рис. 1) наберите с клавиатуры новое имя листа – Закупка, после окончания ввода нового имени нажмите клавишу ENTER.

Рис. 1. Переименование рабочего листа Аналогичным образом переименуйте остальные листы рабочей книги. Указание. Если в новой рабочей книге недостаточно рабочих листов добавьте новые рабочие листы, для этого воспользуйтесь одним из способов описанных в лабораторной работе № 2. После выполнения этих операции рабочая книга примет вид:

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

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

На рабочем листе "Закупка" создайте таблицу и внесите данные, как показано на рис. 3. 26

Указание. ! Для оформления общего заголовка таблицы воспользуйтесь кнопкой "Объединить и поместить в центре", для размещения заголовков таблицы во всех столбцах следует выделить всю 3-ю строку, затем щелкнуть на ней правой кнопкой мыши, из контекстного меню выберите команду Формат ячеек, откройте закладку Выравнивание, установите флажок в поле Переносить по словам, выберите в полях По вертикали – По центру, По горизонтали – По центру, затем нажмите OK.

!

Перед вводом данных задайте форматы ячеек таблицы – Числовой, число десятичных знаков 0, выравнивание – по центру. Наименование предметов – формат Текстовый. Выравнивание – по левому краю (отступ) – 1.

!

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

Рис. 3. Общий вид исходной таблицы на рабочем листе Закупка. Задание.

На рабочем листе "Реализация" внесите исходные данные в таблицу и оформите, как показано на рисунке 4.

Рис. 4. Общий вид исходной таблицы на рабочем листе Реализация. 27

Сохранение рабочей книги (файла). Сохраните промежуточные результаты своей работы. Для этого выберите команду Сохранить как... в меню Файл, затем в диалоговом окне "Сохранение документа" найдите и откройте свою папку, присвойте файлу имя – Расчет дохода и добавьте свою фамилию. Нажмите кнопку Сохранить. Задание.

На рабочем листе "Цена" создайте и заполните две таблицы – Расходы на закупку и Расчет цен как показано на рисунке 5.

Рис. 5. Общий вид рабочего листа Цена. Указание. Оформление заголовков таблиц выполняется аналогично предыдущим заданиям. Задайте форматы ячеек в таблице Расходы на закупку: А4:А9 – текстовый; В4:В8 – денежный, число десятичных знаков – 2, обозначение – р. C4:C8 – числовой, число десятичных знаков – 0. D4:D9 – денежный, число десятичных знаков – 2, обозначение – р. В ячейки А4:С8 внесите данные с клавиатуры. В ячейку D4 введите формулу = В4*С4. Для этого наберите с клавиатуры знак =, затем щелкните левой кнопкой мыши на ячейке В4, нажмите клавишу "знак умножения – * на дополнительной клавиатуре и щелкните мышью на ячейке С4, подтвердите формулу – нажмите клавишу Enter. Чтобы не повторять набор формулы в ячейках D5, D6, D7, D8. Скопируйте в эти ячейки содержимое ячейки D4 вместе с формулой. Для снятия команды копирования с ячейки D4 нажмите клавишу Esc. 28

Обратите внимание на изменение ссылок в формулах суммирования, т.к. они относительные. Задайте форматы ячеек в таблице Расчет цен: А14:А18 – текстовый; В14:В18 – денежный, число десятичных знаков – 2, обозначение – р. C14:C18 – процентный, число десятичных знаков – 0. D14:D18 – денежный, число десятичных знаков – 2, обозначение – р. В ячейки А14:С18, В14:В18 и С14:С18 внесите данные с клавиатуры. В ячейку D14 введите формулу = В14*С14+В14 Для этого наберите с клавиатуры знак =, затем щелкните левой кнопкой мыши на ячейке В14, нажмите "знак умножения – *, щелкните мышью на ячейке С14, затем нажмите "знак сложение – + и подтвердите формулу и нажатием клавиши Enter. Аналогично предыдущей таблице скопируйте содержимое ячейки D14 с формулой в ячейки D15, D16, D17, D18. Примените к таблицам обрамление, как показано на рисунке 5. Задание.

Создайте и заполните таблицы на листе Выручка, как показано на рисунке 6.

Рис. 6. Общий вид рабочего листа Выручка. Указание. В таблицах задайте форматы ячеек, выравнивание данных в них и обрамление ячеек, как показано на рисунке 6. Обратите внимание, что в таблице Выручка от реализации за 1 квартал число, отражающее количество проданного товара, совпадает с количеством проданного товара на листе Реализация, но только за январь, февраль и март месяцы. 29

Задание.

Заполните и оформите таблицы на листе Доход (рис. 7).

Рис. 7. Общий вид рабочего листа Доход. Указание. Обратите внимание, что в таблицах добавились данные столбца Цена закупки из таблицы Расчет цен расположенной на листе Цена. В таблицах задайте форматы ячеек, выравнивание данных в них и обрамление ячеек, как показано на рисунке 7. Внесите данные в таблицы. В таблице Доход от реализации за полугодие количество проданного товара, число в столбце Н, совпадает с суммой за полугодие на листе Реализация. В таблице Доход от реализации за 1 квартал количество проданного товара также составляет сумму, но только за январь, февраль и март месяцы. В ячейках E4:E8 находится формула =D4*(B4-C4). В строке ИТОГО в ячейке E9 находится формула, суммирующая ячейки E4:E8. В ячейках E14:E18 находится формула =D14*(B14-C14). В строке ИТОГО в ячейке E19 находится формула, суммирующая ячейки E14:E18. После оформления рабочих листов, создания таблиц и внесения в них данных, можно считать работу полностью выполненной. Однако полученные таблицы представляют собой разрозненные источники данных и не взаимосвязаны между собой, что не позволяет использовать их как единую базу данных для получения различных отчетов. 30

Убедитесь в этом на простых примерах: 1. Измените наименование товара – Диван в ячейке А4 на листе Закупка на другое – Софа. Проверьте, произошло изменение наименования данного товара на остальных листах или оно осталось прежним? 2. Измените на листе Реализация количество проданных стульев в феврале с 18 на 50. Проверьте, произошли соответствующие изменения на других рабочих листах? 3. Измените на листе Цена в таблице Расходы на закупку в графе Цена закупки (ячейка В4) число 2 000,00 р. на новую цену закупки – 5 000,00 р. Проверьте, произошли соответствующие изменения в соседней таблице Расчет цен, и на других рабочих листах где используется данный показатель? Таким образом, при изменении содержимого в исходной ячейке, не происходит никаких изменений в ячейках содержащих аналогичную информацию в других таблицах, как бы "логически" связанных между собой или как кажется на первый взгляд имеющих общий исходный компонент. Изменения происходят только в тех ячейках, которые имеют прямую связь с исходной ячейкой например, при выполнении п.3 на листе Цена изменяется ячейка D4, т.к. содержит формулу со ссылкой на ячейку В4, и как следствие изменяется итоговая сумма в ячейке D9. Возникает необходимость установить связь между таблицами, расположенными как на одном рабочем листе, так и на разных рабочих листах.

Связывание таблиц в Excel. Если на одном рабочем листе используются данные из другого листа, то эти листы считаются связанными. С помощью связывания можно свести воедино значения ячеек из нескольких разных таблиц на одном рабочем листе. Изменение содержимого ячейки на одном листе или таблице (источнике) рабочей книги приводит к изменению связанных с ней ячеек в листах или таблицах (приемниках). Этот принцип отличает связывание листов от простого копирования содержимого ячеек из одного листа в другой. В зависимости от техники исполнения связывание бывает “прямым“ и через команду СПЕЦИАЛЬНАЯ ВСТАВКА. 1 способ – "Прямое связывание ячеек" Прямое связывание листов используется непосредственно при вводе формулы в ячейку, когда в качестве одного из элементов формулы используется ссылка на ячейку другого листа. Например, если в ячейке таблицы В4 на рабочем Листе2 содержится формула, которая использует ссылку на ячейку А4 другого рабочего листа (например, Листа1) и оба листа загружены данными, то такое связывание листов называется “прямым”. Термин “прямое” связывание обозначает, что пользователь сам непосредственно при вводе формулы указывает имя листа и абсолютный адрес ячейки, разделенные восклицательным знаком "!". Примеры формул: = C5*Лист1! A4 = Лист3! В2*100% = Лист1! A1- Лист2! A1 31

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

Одинарные кавычки

Например: = 'C:\Мои документы\[Расчет дохода.xls]Закупка'!H19 Имя диска Имя каталога (папки)

Имя рабочего листа Имя рабочей книги (файла)

Адрес ячейки

2 способ – Связывание ячеек через команду "Специальная вставка" Связывание через команду СПЕЦИАЛЬНАЯ ВСТАВКА производится, если какая либо ячейка таблицы на одном рабочем листе должна содержать значение ячейки из другого рабочего листа. Чтобы отразить в ячейке С4 на листе Цена значение ячейки Н4 на исходном листе Закупка, нужно поместить курсор на ячейку Н4 исходного листа и выполнить команду Правка–Копировать. На листе Цена поставить курсор на ячейку С4, которую необходимо связать с исходной, и выполнить команду Правка–Специальная вставка– Вставить связь (см рис. 8). Тогда на листе Цена появится указание на ячейку исходного листа Закупка, например: = Закупка!$Н$4 При таком связывании EXCEL автоматически использует абсолютный адрес на ячейку, т.к. относительный адрес обращения может привести к ошибкам, особенно если обращаться к незагруженным файлам (рабочим книгам). Задание.

Свяжите ячейки С4, С5, С6, С7, С8 в таблице Расходы на закупку на листе Цена с соответствующими ячейками на листе Закупка, используя различные способы связывания ячеек (рис. 8).

32

Рис. 8 Связывание ячеек различных рабочих листов

! При связывании ячеек определите, какие ячейки являются исходными. ! Для одной связываемой таблицы исходными могут быть ячейки из разных таблиц на различных рабочих листах или на текущем листе.

Задания для самостоятельной работы. Перед выполнением самостоятельного задания, рассмотрим на примере таблиц листа Цена связывание ячеек. Например: 1. на листе Цена в таблице Расходы на закупку ячейки А4:А8 связаны с ячейками таблицы Количество закупленной продукции на листе Закупка; 2. ячейки В4:В8 являются исходными, т.к. содержат первоначальные сведения о ценах закупленного товара; 3. ячейки С4:С8 связаны с ячейками Н4:Н8 на листе Закупка; 4. ячейки D4:D8 содержат формулы подсчета затраченных средств на приобретенный товар и ссылаются на ячейки собственной таблицы (например, формула в ячейке D4 имеет вид =В4*С4, что означает умножение цены товара на его количество); 5. ячейка D9 является суммой ячеек D4:D8; 6. во второй таблице Расчет цен на этом же листе ячейки А14:А18 связаны аналогично п.1; 7. ячейки В14:В18 являются связанными с исходными ячейками текущего листа В4:В8; 8. ячейки С4:С8 являются исходными, т.к. содержат первоначальные сведения о наценке салона на закупленный товар; 9. ячейки D14:D18 содержат формулы расчета цены продажи товара и ссылаются на ячейки собственной таблицы (например, формула в ячейке D14 имеет вид =В14*С14+В14, что означает умножение закупочной цены на установленный процент наценки, что дает сумму наценки, которую надо прибавить к закупочной цене); 33

После выполнения всех операций с этими таблицами произведите проверку их "работоспособности". Измените наименование товара – Диван в ячейке А4 на листе Закупка на другое – например Софа. Измените количество закупленного товара Софа в июне (в ячейке G4 на листе Закупка введите число 11). Измените цену закупки Софы в ячейке В4 на листе Цена на другую – 2500,00 р. Измените процент наценки Софы в ячейке С14 на листе Цена с 50% на 32%. Проверьте, произошли изменения в связанных таблицах или нет? Обратите внимание, на какие ячейки различных таблиц повлияли внесенные изменения. Внимание! При связывании ячеек через СПЕЦИАЛЬНУЮ ВСТАВКУ... копирование на соседние ячейки становится проблематичным из-за абсолютной адресации ячеек.

Задание 1. Выполните связывание ячеек остальных таблиц рабочей книги, используя различные способы. Указание. В таблицах по расчету выручки и дохода за 1 квартал используется формула суммирования исходных ячеек только 1 квартала. Задание 2. Создайте на листах Выручка и Доход таблицы по расчету за 2 квартал. Свяжите эти таблицы с соответствующими исходными данными. Указание. В таблицах по расчету выручки и дохода за 2 квартал используйте исходные ячейки только 2 квартала. Задание 3. Постройте круговую диаграмму на листе Доход и проанализируйте распределение дохода по видам продукции. Задание 4. Добавьте в конец рабочей книги рабочий лист Сводная ведомость. Создайте на нем сводную таблицу, отражающую по наименованиям товаров количество закупки и продажи, наценку, закупочную и продажную цены, доход от реализации за 1 квартал и за 2 квартал. Свяжите эту таблицу с соответствующими исходными данными на других рабочих листах. Указание. В таблицах по расчету выручки и дохода за 2 квартал используйте исходные ячейки только 2 квартала.

34

Microsoft Excel 97. Работа № 5 Автоматизация документа (на примере формирования прайс-листа). Цель работы: создание автоматизированного документа c использованием "собственных" имен ячеек, текстовых и логических функций, Мастера функций и макросов. В этой работе мы создадим прайс-лист фирмы "МультиМедиа Технологии". По аналогии с этим прайс-листом Вы самостоятельно сможете создавать любые аналогичные документы в Excel. Исходные текстовые данные уже набраны и С:\ Мои документы \ Заготовки для Excel \ price.xls (рис.1).

находятся

в

файле

Откройте его. Вам нужно оформить имеющийся документ в конечный проект (см. листы приложений 1 и 2).

Рис. 1 Исходный вид документа прайс-лист (из файла price.xls). Проанализируйте содержание документа: название фирмы, название прайс-листа, почтовый адрес, телефон, факс и два адреса электронной почты. Также обратите внимание на расположение текста в ячейках, на ширину столбцов, имена Рабочих листов. 35

Оформление таблицы Оформление заголовков и таблицы прайс-листа ничем не отличается от оформления таблиц в предыдущих работах. 1. Переместите заголовок на столбец вправо. Для этого выделите ячейки с заголовками и адресами (шапку документа) и левой кнопкой мыши протяните боковую границу выделенных ячеек на два столбца вправо (не надо тащить за маркер автозаполнения!!!). Заголовок переместился. Сейчас будет понятно, зачем мы это сделали. 2. Настройте ширину столбца А. Для этого выделите весь столбец А и выберите команду Формат–Столбец–Автоподбор ширины. Если бы не переместили заголовки, то ширина столбца А была бы совсем другая! 3. Создайте шапку самой таблицы: Наименование и Цена продукта ячейки А7 и В7. Так как цены будут выражены в долларах и в рублях (цены в рублях будем рассчитывать по текущему курсу ММВБ!), то ячейку В7 в заголовке объединим с ячейкой С7, а в ячейках В8 и С8 обозначим цены в USD и руб. соответственно. Выполните центрирование текста заголовка таблицы. Если нужно, измените ширину столбцов В и С. 4. Оформите шапку документа: в строке 1 – Мультимедиа Технологии (шрифт Arial, полужирный, курсив, размер – 14 пт, выравнивание – по центру трех ячеек); в строке 2 – Прайс-лист программной продукции (шрифт Arial, полужирный, размер – 18 пт, выравнивание – по центру трех ячеек); в строках 3, 4, 5 – почтовый адрес, телефоны, факс и электронный адрес фирмы (шрифт Arial, обычный, размер – 9 пт, выравнивание – по правому краю ячеек). 5. На каждый продукт отведено три строки: название продукта, краткий комментарий содержания курса и требуемые ресурсы компьютера. Задайте обрамление всей таблице, используя команду Границы в меню Формат ячейки. 6. Оформите заголовок и название каждого продукта полужирным шрифтом. 7. Убедитесь, что прайс-лист не выходит за пределы формата бумаги А4. Для просмотра размещения прайс-листа на бумаге, на панели инструментов нажмите кнопку Предварительный просмотр. Если в колонтитулах (верхнем и/или нижнем) находится текст, например Лист1 или номер страницы и т.д., то выберите команду Файл – Параметры страницы и в окне диалога Колонтитулы удалите предлагаемый текст или в режиме Предварительного просмотра эту операцию можно сделать через команду Страница. 8. Задайте форматы ячеек с ценами: для цен в долларах – финансовый без десятичных знаков с обозначением в долларах США; для рублевых цен – числовой формат с разделителем разрядов и двумя десятичными знаками. 9. Введите числа в ячейки долларовой цены: 40, 80, 60, 90, 90, 35, 35, 35. 10. Присвойте рабочему листу новое название – Прайс-лист. Примечание: если в Вашем прайс-листе имеются пустые строки между шапкой документа и самой таблицей, оставьте только одну строку, а остальные удалите. 36

Итак прайс-лист принял привычный вид, но только с ценами в долларах. Теперь необходимо ввести в ячейки для рублевых цен формулу их расчета. Цена в рублях это не просто перемножение цены в долларах на текущий курс рубля!!! Для этого расчета необходимо сначала ввести переменные, которые будут отражать непосредственно текущий курс рубля, дополнительный процент устанавливаемый банком и будут находиться на отдельном листе. Ввод переменных и задание имен ячейкам Перейдите на второй лист. Сделайте столбец А шире, для того, чтобы уместилось название. В А2 запишите: Текущий курс рубля; В А3 запишите: Дополнительный процент; В В2 введите текущий курс рубля – 6,02; В В3 введите размер дополнительного процента – 3%. Для дальнейшей работы удобно ввести "собственные имена" (абсолютный адрес) для переменных. Выделите А2:В3. Выберите Вставка–Имя–Создать и по умолчанию в окне "Создать имена" должен стоять флажок (галочка) в столбце слева, нажмите OK. Таким образом Вы присвоили имена ячейкам В2 и В3. Слева от строки формул находится поле имен ячеек, открыв список этих имен можно увидеть весь список только что присвоенных имен ячеек наших переменных (в них автоматически дописаны знаки подчеркивания между словами). Например: текущий_курс_рубля. Если курсор находится в именованной ячейке, то ее имя будет в поле имени ячейки. Теперь можно вернуться на первый лист (Прайс-лист) и начать ввод формул в ячейки для расчета рублевых цен (помните – формула начинается со знака =). Не забудьте перейти на английский алфавит при вводе формулы или щелкайте левой кнопкой мыши на те ячейки которые участвуют в формуле, а между ними вводите знаки арифметических действий. В ячейке С9 должна быть формула: =В9*Текущий_курс_рубля. Мышью щелкните на ячейке В9, затем на дополнительной клавиатуре нажмите клавишу умножения – *, затем перейдите на второй лист, щелкните на ячейке В2, нажмите ENTER (если все сделано правильно, то в ячейке С9 будет результат расчета – 240,80). Это цена в рублях, но без учета дополнительного банковского процента, поэтому учтем дополнительный процент и внесем исправления в формулу. Для этого щелкните мышью в строке формул и добавьте к имеющейся формуле дополнительные множители: *(1+ Дополнительный_процент). С клавиатуры наберите знак умножения – *, затем круглую скобку и 1 со знаком +, после чего перейдите на второй лист и щелкните на ячейке В3, затем ENTER. В ячейке С9 формула приняла вид: =В9*Текущий_курс_рубля*(1+Дополнительный_процент), а в самой ячейке появилась рублевая цена – 248,02. Скопируйте формулу расчета рублевой цены на остальные ячейки по видам продукции. (Собственные имена ячеек играют роль абсолютных ссылок, поэтому не изменяются!). Присвойте второму рабочему листу новое название – Переменные. Итак, мы практически получили прайс-лист для конечного потребителя.

37

Оформление таблицы для дилеров Но фирмы работают еще и с дилерами, которые имеют скидки при покупке товаров. Необходимо ввести в прайс-лист цены с учетом скидок для дилеров. Предположим, что фирма имеет два варианта скидок для дилеров приобретающих от 5 до 20 экземпляров и свыше 20 экземпляров (для них скидка больше). Для этого нужно создать таблицу цен для дилеров (она будет находиться под основной таблицей). Добавьте две строки: поместить курсор в строку перед которой будет вставлена новая, в меню выберите Вставка–Строки. Теперь отступите одну строку и введите название таблицы Цены для дилеров. Название таблицы: шрифт – полужирный курсив, размер шрифта 14 пт (центрировать на три ячейки). Сама таблица для дилеров похожа на первую только цены будут в долларах и отличие их в том сколько экземпляров приобретает дилер следовательно и размер скидки будет разный. Поэтому таблица будет иметь три столбца: Наименование товара, Цена продукта: от 5 до 20 и свыше 20, т.к. при заключении контракта с дилерами нужно будет учитывать курс рубля (см. образец приложения к работе). Шапку таблицы оформите также как и в первой таблице и сделайте обрамление. Чтобы не набирать снова наименования продуктов, в ячейку А38 введем ссылку на соответствующую ячейку первой таблицы и запишем ее в виде формулы: =А9, т.к. каждый продукт содержит три строки, то номера ячеек будут увеличиваться на 3. Аналогично введите ссылки на остальные ячейки прайс-листа с наименованием товара. Обратите внимание: если произойдет изменение в названии продукта или появится его новая версия, то достаточно сделать изменение только в исходной (влияющей) ячейке, а в таблице дилеров автоматически произойдут аналогичные изменения. Для оформления ячеек с ценами для дилеров надо внести дополнительные переменные на втором листе. В ячейку А5 введите Скидки для дилеров, в ячейки А6 и А7 введите текст: от 5 до 20 и свыше 20. Т.к. скидки даются в процентах, поэтому в ячейки В6 и В7 введите соответственно 25% и 50%. Аналогичным способом создайте два новых имени для ячеек В6 и В7. Посмотрите в поле имен ячеек должно быть теперь четыре разных имени ячеек. Вернитесь на первый лист и продолжите заполнение таблицы для дилеров. Введем формулу для расчета цены первого продукта с учетом существующих скидок для категории дилеров от 5 до 20. Примечание: цены для дилеров должны быть меньше на величину скидки, т.е. при цене продукта в 40$, дилер от 5 до 20 получит скидку 25%, что составит 10$, следовательно цена для него получится 40$–10$=30$. Исходные данные для первого продукта находятся в ячейке В9, поэтому в ячейку В38 введем формулу: =В9*(1-от_5_до_20). После нажатия клавиши ENTER вы увидите цену $ 30,00 (не забудьте задать формат этой ячейке – Финансовый с двумя десятичными знаками и обозначением валюты – $ США). Аналогично введите формулы в остальные ячейки этого столбца таблицы. Сделайте то же самое для столбца со скидкой «свыше 20». 38

Использование функций в Excel. Мастер функций. Продолжим работу с оформлением прайс-листа, при этом будем использовать встроенные в Excel функции. В ячейку А47 введите текст: Цены в рублях рассчитываются по курсу ММВБ на день продажи + 3% В ячейку А48 введите текст: Текущий курс рубля = 6,02 рублей за 1$ на день 29/03/1998 Первая фраза отражает принцип расчета рублевых цен в прайс-листе, а вторая содержит информацию для покупателя о текущем курсе рубля на определенный день. Но обе эти фразы как бы состоят из двух частей: текстовой части и части содержащей информацию о размере дополнительного процента, величине курса рубля, а также дате на которую взят курс рубля. Надо понимать, что вторая часть может изменяться в разные дни, т.к. в ней используются переменные. Т.е. при изменении переменных на втором листе весь прайс-лист будет пересчитан, а строки текст останется прежним! Изменим этот недостаток. Задача заключается в том, чтобы изменить фразы таким образом при котором текстовая часть оставалась неизменной, а вторая часть отображала переменные со второго листа. Рассмотрим строку текста: Цены в рублях рассчитываются по курсу ММВБ на день продажи + 3%. В ней текстовая (неизменяемая) часть – "Цены в рублях рассчитываются по курсу ММВБ на день продажи + ", а вторая (переменная) часть ссылается на ячейку второго листа Дополнительный_процент, потом опять текстовая часть которая содержит знак %. Начнем редактирование (можно нажать клавишу F2 для редактирования). Преобразуйте строку текста в формулу, т.е. поставьте перед текстом знак равенства (=) и текстовую часть возьмите в кавычки, затем с клавиатуры наберите знак & перейдите на лист Переменные щелкните на ячейку Дополнительный_процент поставьте знак умножения * и наберите 100, опять наберите знак & поставьте в кавычках знак %. В окончательном виде формула будет иметь вид: ="Цены в рублях рассчитываются по курсу ММВБ на день продажи + " &Дополнительный_процент*100&"%" где & - оператор текстовой функции СЦЕПИТЬ (см. приложение к работе), дополнительный_процент – аргумент со второго листа. Внешне фраза не изменилась, но она стала формулой, которая "следит" за переменной. Проверьте, работает ли ваша формула. Перейдите на второй лист и введите дополнительный процент, например 10%. Верните исходный процент 3%. Аналогично измените в формулу следующую фразу. Она должна принять следующий вид: ="Текущий курс рубля = "&Текущий_курс_рубля & " рублей за 1 $ на день 29/03/1998" Измените курс доллара, например 31,27. Проверьте работу формулы. В этой формуле имеется один недостаток – она не изменяет дату. Почему? Потому что у нас нет двух переменных отражающих текущую дату и дату изменения курса. 39

Теперь введем в эту формулу функцию даты: СЕГОДНЯ(): ="Текущий курс рубля = "&Текущий_курс_рубля&" рублей за 1$ на день " &СЕГОДНЯ() Вместо даты у вас, наверное, получилось число. Изменим формулу с помощью Мастера функций. Удалите из формулы функцию СЕГОДНЯ() и после знака & вызовите Мастера функций, нажав на панели инструментов кнопку fx , затем в окне Мастер функций выберите Категорию: – Текстовые, Функция: – Текст (рис. 2). Функция ТЕКСТ форматирует число и преобразует его в текст. Синтаксис функции: ТЕКСТ(значение;строка_формат). (Более подробно: см. приложение). Окно диалога на следующем шаге Мастера функций имеет вид, показанный на рис. 2.

Рис. 2 Окно Мастера функций (2-й шаг) В поле Значение введите первый аргумент – функцию СЕГОДНЯ(). В поле Строка_формат введите второй аргумент – ДД/ММ/ГГГГ и нажмите ОК. Формула теперь имеет вид: ="Текущий курс рубля = "&Текущий_курс_рубля & " рублей за 1 $"&" на день " &ТЕКСТ(СЕГОДНЯ();"ДД/ММ/ГГГГ") В прайс-листе будет выведен текст: Текущий курс рубля = 6,02 рублей за 1$ на день 29/03/1998 Если все формулы работают, то Вы получили грамотно работающий прайс-лист. На этом наверное, можно было бы и закончить. НО! ♠ Мы можем при работе с реальным прайс-листом забыть изменить текущий курс рубля. Добавим перед нашими переменными на втором листе три строки. Выделите три строки и в меню Вставка выберите Строки. В ячейку А2 введите текст – Текущая дата; в ячейку А3 – Дата изменения курса; в ячейку В2 введите формулу: =СЕГОДНЯ(). В ячейке В3 должна быть дата изменения курса рубля – введите вчерашнюю дату, разделяя день, месяц и год дробной чертой. В этом случае формат ячейкам присвоится автоматически. 40

Очевидно, если две даты не совпадают, надо проверить изменился курс рубля или нет и, чтобы при несовпадении дат программа сделала напоминание о том, что на сегодняшний день возможно изменился курс рубля. В ячейку С5 введем с помощью Мастера функций логическую функцию ЕСЛИ (информацию о функции вы найдете в приложении к работе). Окно диалога Мастера функций (2-й шаг) показано на рис.3.

Рис. 3 Окно диалога Мастера функций (2-й шаг) В поле Логическое_выражение введите В2< >В3 (без пробелов); в поле Значение_если_истина – Дата изменилась. Возможно изменился курс рубля; в поле Значение_если_ложь ничего вводить не будем, поэтому поставьте просто две кавычки. Нажмите ОК. Так как у нас даты не совпадают, то появляется надпись-напоминание о необходимости проверки курса рубля. Сделайте это сообщение полужирным красного цвета, размер шрифта 12 пт. Проверим работу условия. Введите совпадающую дату изменения курса: надпись пропадет. Снова введите вчерашнюю дату. Итак, Excel напоминает автоматически о возможном изменении курса рубля! Дальнейшая автоматизация прайс-листа возможна при использовании макроса. Использование макросов в Excel. При работе в Excel возникает необходимость несколько раз выполнять одну и ту же последовательность действий. В этом случае можно записать эту последовательность под определенным именем. Записанная под определенным именем последовательность действий называется макросом. Записанный макрос можно вызывать для его выполнения из основного меню при помощи кнопки на панели инструментов или на рабочей области, а также комбинацией клавиш. Макрос может быть назначен графическому объекту. В нашем случае повторяющаяся последовательность действий: ежедневная проверка курса рубля и его изменение, а также даты его изменения.

41

Создадим и запишем для автоматизации этой процедуры макрос. Выберите команду Сервис–Макрос–Начать запись. В окне диалога запишем имя макроса: Изменить_дату. !(Не забудьте знак подчеркивания). В Описание запишем: Изменить дату на текущую. Нажмите ОК. Начинаем записывать макрос. В окне программы появилась панель с кнопкой Остановить запись макроса. Копируем содержимое ячейки В2 – это текущая дата. Так как в ячейку В3 нужно вставить только значение, а не формулу, то в меню выберите команду Правка– Специальная вставка–Значение. Нажмите ОК и затем Esc, чтобы убрать мерцающую рамку. Теперь запись макроса можно остановить кнопкой Остановить запись. Создание и графическое оформление макроса в Excel. Для удобства использования макроса можно создать кнопку при нажатии на которую макрос начнет выполняться. На панели Рисование выберите Надпись, нарисуйте ее на рабочем поле листа и впечатайте название этой кнопки – Изменение даты. Из контекстного меню выберите Формат надписи, затем в открывшемся диалоговом окне задайте параметры кнопки: на закладке Цвета и линии цвет заливки – красный, линя – Нет линий; на закладке Шрифт – полужирный, размер 12 пт, цвет желтый. Нажмите ОК. Затем на панели Рисование найдите кнопку Тень нажмите ее и выберите один из предложенных вариантов расположения теней. В завершении из контекстного меню выберите Назначить макрос… и выберите свой макрос Изменить_дату, затем нажмите ОК. Осталось только связать ячейку А6 на листе Прайс-лист с ячейкой С5 на листе Переменные. Введите формулу в ячейку А6: =Переменные!C5. Проверьте работу макроса и кнопки при разных значениях Даты изменения курса. Итак, работа завершена и Вы получили автоматизированный коммерческий прайс-лист. Сохраните свой файл под именем Прайс-лист и добавьте свою фамилию.

Образец листа Переменные

Текущ ая дата Дата изменения курса

19.01.2003 19.01.2003

Текущ ий курс рубля Дополнительный процент

6,02 3%

Скидки для дилеров: от 5 до 20 свыш е 20

25% 50%

42

Изменение даты

Образец Прайс-листа

М ульт имедиа Технологии Прайс-лист программной продукции г.Москва, ул. Энергетическая, д.8, корп.2, 4-й этаж , "МультиМедиа Технологии - отдел торговли" тел. (095) 362-7486, факс (095) 362-7023 (1-й) e-m ail: m m t@glas .apc.org (2-й) e-m ail: m m t@m m t.m s k.ru

Наименование English Gold Курс английского языка для русскоязычного пользователя (500 МВ), 12000 слов, TOEFL) Fra ncais d'Or Курс французского языка для русскоязычного пользователя (600 МВ, 12000 слов, слайд-фильм) EspaNol de Oro Курс испанского языка для русскогоязычного пользователя (W indows 3.1/95, 500 МВ, 12000 слов) English Platinum Углубленный курс английского языка для русскоязычного пользователя (W indows 3.1/95, 2 CD-ROM, 1100 МВ, 15000 слов, видеофильм) Golden Russian Курс русского языка для англоязычного пользователя (W indows 3.1/95, 500 МВ, 12000 слов) W inTeachPro "MS Excel 7.0" Комплексные мультимедийные курсы для самостоятельного изучения MS Excel 7.0 for W indows 95 W inTeachPro "MS W ord 7.0" Комплексные мультимедийные курсы для самостоятельного изучения MS W ord 7.0 for W indows 95 W inTeachPro "MS W indow s 95" Комплексные мультимедийные курсы для самостоятельного изучения операционной системы MS W indows 95

Цена продукта USD руб. $ 40 248,02

$

80

496,05

$

60

372,04

$

90

558,05

$

90

558,05

$

35

217,02

$

35

217,02

$

35

217,02

Цены для дилеров Наименование

Цена продукта от 5 до 20 свыше 20 $ 30,00 $ 20,00 $ 60,00 $ 40,00 $ 45,00 $ 30,00 $ 67,50 $ 45,00 $ 67,50 $ 45,00 $ 26,25 $ 17,50 $ 26,25 $ 17,50 $ 26,25 $ 17,50

English Gold Fra ncais d'Or EspaNol de Oro English Platinum Golden Russian W inTeachPro "MS Excel 7.0" W inTeachPro "MS W ord 7.0" W inTeachPro "MS W indow s 95"

Цены в рублях рассчитываются по курсу ММВБ на день продажи + 3% Текущ ий курс рубля = 6,02 рублей за 1$ на день 19/01/2003

43

Приложение к работе № 5. Применение операторов в формулах Операторами обозначаются операции, которые следует выполнить над операндами формулы. В Microsoft Excel включено четыре вида операторов: арифметические, текстовые, а также операторы сравнения и адресные операторы. Арифметические операторы используются для выполнения основных математических вычислений над числами (например сложение, вычитание или умножение, другие действия над числами и получение численных результатов). Результатом выполнения арифметической операции всегда является число. Арифметический оператор

Значение

Пример

+ (знак плюс)

Сложение

3+3

– (знак минус)

Вычитание Унарный минус

3–1 –1

* (звездочка)

Умножение

3*3

/ (косая черта)

Деление

3/3

% (знак процента)

Процент

20%

^ (крышка)

Возведение в степень

3^2 (аналогично 3*3)

Операторы сравнения используются для обозначения операций сравнения двух чисел. Результатом выполнения операции сравнения является логическое значение ИСТИНА или ЛОЖЬ. Оператор сравнения

Значение

Пример

= (знак равенства)

Равно

А1=В1

> (знак больше)

Больше

A1>B1

< (знак меньше)

Меньше

A1
>= (знак больше и знак равенства)

Больше или равно

A1>=B1

<= (знак меньше и знак равенства)

Меньше или равно

A1<=B1

Не равно

A1<>B1

<> (знак больше и знак меньше)

44

Текстовый оператор «&» - амперсант используется для обозначения операции объединения последовательностей символов в единую последовательность. Текстовый оператор

Значение

Пример

& (амперсант)

Объединение последовательностей символов в одну последовательность

Результатом выполнения выражения "Северный" & " ветер" будет: "Северный ветер"

Адресные операторы объединяют диапазоны ячеек для осуществления вычислений. Адресный оператор : (двоеточие)

, (запятая) (пробел)

Значение

Пример

Оператор диапазона, который ссылается на все ячейки между границами диапазона включительно. Оператор объединения, который ссылается на объединение ячеек диапазонов. Оператор пересечения, который ссылается на общие ячейки диапазонов.

B5:B15

СУММ(B5:B15,D5:D15) СУММ(B5:B15 A7:D7)

Функция СЦЕПИТЬ: объединяет несколько текстовых строк в одну. Замечание: вместо функции СЦЕПИТЬ для объединения текстов можно использовать оператор "&" – амперсант. Синтаксис СЦЕПИТЬ (текст1;текст2;...) где текст1, текст2, ... – это от 1 до 30 элементов текста, объединяемых в один элемент текста. Элементами текста могут быть текстовые строки, числа или ссылки, которые ссылаются на одну ячейку. Примеры: СЦЕПИТЬ("Суммарное "; "Значение") равняется "Суммарное Значение". Это эквивалентно выражению "Суммарное"&" "&"Значение". Пусть имеется рабочий лист, в котором собраны данные о видах рыб, обитающих в реке, и пусть ячейка C2 содержит "вида", ячейка C5 содержит " речная форель" и ячейка C8 содержит 32. Тогда: СЦЕПИТЬ("Численность популяции ";C2;" ";C5;" составляет ";C8;" на километр.") равняется "Численность популяции вида речная форель составляет 32 на километр." Функция ТЕКСТ: преобразует значение в текст в заданном числовом формате. Синтаксис ТЕКСТ(значение;формат) где Значение – это либо числовое значение, либо формула, вычисление которой дает числовое значение, либо ссылка на ячейку, содержащую числовое значение; Формат - это числовой формат в текстовой форме с вкладки Число диалога Формат ячеек. Формат не может содержать звездочку (*) и не может быть Общим числовым форматом . 45

Замечания: Форматирование ячейки с помощью вкладки Число (команда Ячейки... меню Формат) меняет только формат, но не значение. Использование функции ТЕКСТ преобразует значение в форматированный текст, и результат больше не участвует в вычислениях как число. Примеры: ТЕКСТ(2,715; "0,00 р.") равняется "2,72 р." ТЕКСТ("15.4.91"; "Д МММ, ГГГГ") равняется "15 Апр, 1991" Функция ЕСЛИ возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Функция ЕСЛИ используется для условной проверки значений и формул. Синтаксис ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) Лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ. Значение_если_истина – это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА. Если лог_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА. Значение_если_истина может быть другой формулой. Значение_если_ложь – это значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ. Если лог_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ. Значение_если_ложь может быть другой формулой. Замечания: До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь, чтобы конструировать более сложные проверки, см. последний из приведенных ниже примеров. Функция ЕСЛИ всегда возвращает значение, возвращаемое вычисленным аргументом значение_если_истина и значение_если_ложь. Если какой-либо аргумент функции ЕСЛИ является массивом, то при выполнении функции ЕСЛИ вычисляется каждый элемент массива. Если какой-либо из аргументов значение_если_истина или значение_если_ложь является действием, то все действия выполняются. Примеры: В следующем примере, если значение ячейки A10 - 100, то лог_выражение имеет значение ИСТИНА и вычисляется сумма для ячеек B5:B15. В противном случае лог_выражение имеет значение ЛОЖЬ и возвращается пустой текст (""), очищающий ячейку, которая содержит функцию ЕСЛИ. ЕСЛИ(A10=100;СУММ(B5:B15);"")

46

Microsoft Excel 97. Работа № 6 Задачи оптимизации. Цель работы: Знакомство с методами решения задач оптимизации в электронных таблицах. Одной из задач оптимизации является задача об использовании сырья. При этом решается задача оптимизации целевой функции – критерия задачи при заданных в виде равенств и неравенств ограничений. Принятие оптимальных решений требует выполнения трех этапов: 1. анализ исходных данных; 2. создание математической модели; 3. решение задачи на компьютере; 4. анализ полученных результатов. Рассмотрим простейшую задачу, в которой проиллюстрируем некоторые понятия задач оптимизации. Отметим, что рассматриваемая задача относится к задачам нелинейного программирования. Задача № 1. Расчет изделия с минимальным расходом материала. Спроектировать бак, имеющий форму прямоугольного параллелепипеда, объем которого равен V = a·b·h, где a, b, h –стороны бака (длина, ширина, высота). Требуется определить размеры бака объемом V = 2000, чтобы на его изготовление пошло как можно меньше материала. Площадь материала для изготовления бака равна сумме площадей всех стенок бака и выражается формулой S = a·b + a·b + b·h + b·h + a·h + a·h = 2(a·b + (a +b)h).

a h h a

b

b

Составим математическую модель. Очевидно, что целью задачи является экономия материала. Тогда целевая функция F зависящая от трех переменных a, b и h – это просто площадь необходимого материала S. Необходимо найти при каких значениях a, b и h целевая функция F будет минимальной. Итак, задача может быть записана в форме математической модели: F = S → min V = 2000. 47

Эта запись читается так: минимизировать величину S при условии, что V = 2000. Подставив значения F и S, получим: F=2·(a·b + (a + b)·h) → min, а т.к. V=a·b·h, то a·b·h = 2000 И, наконец, нужно добавить ограничения (граничные условия) на параметры бака: a > 0, b > 0, h > 0. Составим таблицу для решения задачи и решим ее средствами Excel. ♠ Будьте внимательны: имена столбцов набираются латинскими буквами! Ввод данных Введите текст:

в ячейку А1 – Целевая функция F; в ячейку А3 – Расчетные показатели; в ячейки А4:А7 – a=, b=, h=, V=. Ячейки D1, D2, D3 будем использовать для ввода начальных значений переменных a, b и h, для дальнейшего поиска оптимального решения. Пока будем считать их равными нулю. В ячейку B1 введем для целевой функции F формулу, которая будет иметь следующий вид =2*(D1*D2+(D1+D2)*D3) В ячейки B4, B5 и B6 последовательно введем формулу со ссылкой на ячейки D1, D2, D3 (в B4 =D1, в B5 =D2 и в B6 =D3), для дальнейшего ввода ограничений на параметры бака. В ячейку B7 введите формулу =D1*D2*D3 для расчета объема бака, т.е. ограничения по объему, который должен быть равен 2000. Приступим к поиску решения задачи. Выберите команду Сервис–Поиск решения. Появится окно диалога «Поиск решения» – рис. 1.

Рис. 1 Окно диалога «Поиск решения». Указатель находится в ячейке В1.

48

Так как формула для целевой функции находится в ячейке B1, то нужно щелкнуть мышью на этой ячейке. В поле окна диалога «Установить целевую ячейку» появится автоматически абсолютная ссылка $B$1. (Примечание: можно набрать значение абсолютной ссылки с клавиатуры, но щелкнуть мышью проще!) В поле «Изменяя ячейки» укажите ссылку на диапазон ячеек D1:D3. Установите переключатель «Минимальное значение». Теперь необходимо ввести ограничение для поиска решения. Нажмите кнопку Добавить, чтобы ввести информацию в поле Ограничения – рис. 2.

Рис. 2 Окно диалога «Добавить ограничения» В поле Ссылка на ячейку введите B4. В списке неравенств выберите >= – больше или равно. В поле Ограничение введите число 0. Нажмите кнопку Добавить. Введите аналогичные ограничения для ячеек B5 и B6. Введите ограничение для ячейки B7: =2000. После ввода последнего ограничения нажмите клавишу Enter или кнопку OK. Окно диалога «Поиск решения» примет следующий вид – рис. 3.

Рис. 3 Окно диалога «Поиск решения» после ввода ограничений

49

Решение задачи. Теперь все параметры для поиска решения введены и, можно дать команду к выполнению поиска решения. Нажмите кнопку Выполнить. В окне Результаты поиска решения выведено сообщение «Поиск не может найти подходящего решения», т.к. в ячейках D1, D2, D3 были заданы нулевые начальные значения (рис. 4).

Рис. 4 Окно Результаты поиска решения (при нулевых начальных данных!) При решении задач линейного программирования достаточно часто не удается получить оптимального решения. Это происходит по следующим причинам. • Неправильная математическая модель или неправильные исходные данные. • Неограниченность целевой функции. При максимизации целевая функция должна быть ограничена сверху, при минимизации – снизу. Измените начальные условия в ячейках D1:D3 на 1. Снова выберите команду Сервис – Поиск решения и нажмите кнопку Выполнить. Теперь в окне поиска решения появилось сообщение: «Решение найдено. Все ограничения и условия оптимальности выполнены». (Примечание: если в эти ячейки ввести отрицательные числа, то согласно заданным ограничениям, решение все равно будет найдено).

Рис. 5 Окно Результаты поиска решения (при ненулевых начальных данных!) Чтобы получить отчет по найденному решению в окне Результаты поиска решения выберите Тип отчета: Результаты, нажав кнопку OK, получите лист с отчетом по результатам поиска решения. Перед тем листом, где записана постановка задачи автоматически будет вставлен лист Отчет по результатам 1. В этом отчете содержится ответ на поставленную задачу. Искомый параллелепипед оказался кубом со сторонами ≈ 12,6. Переименуйте рабочий лист и дайте ему название Бак. 50

Задача № 2. План выгодного производства. Предположим, что мы решили производить несколько видов конфет. Назовем их условно "A", "B" и "C". Конфеты можно производить в любых количествах (сбыт обеспечен), но запасы сырья ограничены. Известно, что реализация 10-ти килограмм конфет "А" дает прибыль 9 р., "В" – 10 р. и "С" – 16 р. Надо определить, каких конфет и сколько десятков килограмм необходимо произвести, чтобы общая прибыль от реализации была максимальной. На другом рабочем листе, назовите его Конфеты, создайте таблицу с данными норм расхода сырья как показано на рис. 6. (ячейки B6, C6, D6 имеют формат Денежный)

Рис. 6. Таблица с данными норм расхода сырья. Создайте и введите формулы в таблицы для нахождения решения поставленной задачи, как указано на рис 7. Примечание: Количество конфет будет измеряться в десятках килограмм, т.к. нормы расхода сырья и получаемая от этого прибыль тоже приведены из расчета на 10 кг конфет.

Рис. 7. Таблицы с формулами для Поиска решения максимальной прибыли. Обратите внимание, что в ячейках А17, В17, С17 находится формула, отражающая сумму расхода сырья на производство каждого вида конфет, при этом каждая из них имеет ограничение по количеству имеющегося сырья. В меню Сервис активизируйте команду Поиск решения и опишите его ограничения, как показано на рис 8. 51

Рис. 8. Вид окна Поиск решения с заданными ограничениями. Если Вы сделали все верно, то решение будет таким, как на рис 9.

Рис. 9. Вид рабочего листа "Конфеты" после нахождения решения. Из решения видно, что оптимальный план выпуска предусматривает изготовление 80 кг конфет "В" и 200 кг конфет "С". Конфеты "А" производить не стоит. Полученная Вами прибыль составит 400 р. При этом сырье израсходуется полностью, кроме наполнителя, расход которого составит 84 кг из имевшихся 180 кг. 52

Задача № 3 Транспортная задача. Определение оптимального плана перевозок продукции со складов в пункты реализации. Нужно перевезти с минимальными затратами весь груз из трех складов в 2 города: Озеры и Луховицы. В Озеры надо перевезти 45 контейнеров, в Луховицы – 79. На складах имеется 124 контейнера, которые распределены следующим образом: склад № 1 – 18 контейнеров, склад № 2 – 75 и склад № 3 – 31. Задание. Присвойте рабочему листу новое имя – Перевозки. Стоимость перевозки одного контейнера со склада в пункт назначения приведена в таблице № 1. Таблица № 1. Стоимостное определение затрат на перевозку всех контейнеров со складов по городам (в у.е.) Сумма расходов на Стоимость перевозки одного контейнера перевозку со склада в город (в у.е.) (в у.е.) Склад г. Озеры г. Луховицы №1 17 6 №2 12 13 №3 9 8

?

На рисунке показана эта же таблица, выполненная в Excel.

Рис. 10. Таблица с внесенными данными стоимости перевозки одного контейнера со склада в города. В таблице № 2 приведено количество контейнеров на каждом складе и их общее количество. А также общее количество потребности в контейнерах по каждому городу.

53

Таблица № 2.

Создайте Таблицу № 2 в Excel, как показано на рисунке. В таблице № 3 приведены обозначения для объемов перевозок "Склад–Город". Например, переменная х1 обозначает число контейнеров, которое должно быть перевезено в г. Озеры со склада № 1 при условии выполнения поставленной задачи. Таблица № 3. Распределение контейнеров по перевозкам "Склад–Город" (обозначения для объемов перевозок даны с учетом имен ячеек) Количество контейнеров г. Озеры г. Луховицы на складах 18 №1 х1 у1 75 №2 х2 у2 31 №3 х3 у3 Всего 124 45 79 Создайте на этом же рабочем листе Таблицу № 3, пока без внесения количественных данных по складам и по городам, как показано ниже.

Рис. 11. Таблица для нахождения оптимального распределения контейнеров по перевозкам со складов по городам. Составим математическую модель задачи: Целевую функцию J определим как сумму затрат на перевозку всех контейнеров с каждого склада по городам, с учетом стоимости перевозки каждого контейнера со склада в город при известной потребности в них.

54

Она имеет следующий вид и зависит от 6 переменных: J(j1, j2, j3, j4, j5, j6) = 17*C18 + 12*C19 + 9*C20 + 6*D18 + 13*D19 + 8*D20 где: числа 17, 12, 9, 6, 13 и 8 являются содержимым ячеек В4:С6 и соответствуют переменным j1, j2, j3, j4, j5, j6 (таблица №1). Запишем уравнения для ограничений. Тот факт, что все контейнеры вывезены со складов по городам, записывается в виде трех уравнений: х1 + у1 = 18 х2 + у2 = 75 х3 + у3 = 31 Сумма контейнеров, полностью удовлетворяющая потребность каждого города дает еще два уравнения: х1 + х2 + х3 = 45 у1 + у2 + у3 = 79 Еще два ограничения совершенно естественны. Все значения переменных должны быть целыми и неотрицательными, т.к. речь идет о количестве контейнеров. Задача поставлена, теперь можно приступить к ее решению. Т.к. целевая функция определяет сумму всех затрат по перевозке всех контейнеров, то она примет вид формулы со ссылкой на соответствующие ячейки: =B4*C18+B5*C19+B6*C20+C4*D18+C5*D19+C6*D20 Введите формулу целевой функции в ячейку D4. Введите в ячейку В18 формулу для ограничения =C18+D18. Введите в ячейку В19 формулу для ограничения =C19+D19. Введите в ячейку В20 формулу для ограничения =C20+D20. Введите в ячейку В21 формулу для ограничения =B18+B19+B20. Введите в ячейку С21 формулу для ограничения =C18+C19+C20. Введите в ячейку D21 формулу для ограничения =D18+D19+D20. Введите в ячейки С18:D20 начальные значения переменных. В данном случае положим эти значения нулевыми. (Или выделите весь диапазон этих ячеек, введите с клавиатуры 0, а затем нажмите Ctrl+Enter.) Объедините ячейки D4, D5, D6 в одну и задайте формат Выравнивание по вертикали и Выравнивание по горизонтали – По центру. Если все выполнено верно, должен получиться результат как на рис. 12.

55

ячейки,

Рис. 12. Вид рабочего листа Перевозки с нулевыми значениями для нахождения поиска решения задачи. Выполните команду Сервис–Поиск решения. Появится окно диалога “Поиск решения”. В поле ввода Установить целевую ячейку введите ссылку на ячейку D4.

♠ Установите переключатель Минимальное значение. В поле ввода Изменяя ячейки укажите ссылки на ячейки C18:D20. Затем необходимо ввести информацию в поле Ограничения. Нажмите кнопку Добавить. Появится окно диалога “Добавить ограничения”. В поле ввода Ссылка на ячейку введите ссылку на ячейку В18. В поле ввода Ограничение введите = и число 18. Аналогичную операцию проделайте с ячейками В19, В20, С21 и D21. Для ввода ограничения на целочисленность переменных. В поле ввода Ограничение введите для ячеек C18:D20: цел.

56

Для ввода ограничения на неотрицательность переменных возможны два варианта: 1. В окне диалога “Добавить ограничения” в поле ввода Ссылка на ячейку введите ссылку на диапазон ячеек C18:D20. В поле ввода Ограничение введите >= и число 0. 2. В окне диалога "Поиск решения" нажмите кнопку Параметры и затем в окне "Параметры поиска решения" поставьте флажки у параметров Неотрицательные значения и Линейная модель. На рис. 13 и рис. 14 представлены "Параметры поиска решения" и "Поиск решения".

заполненные

окна

диалога

Рис. 13. Заполненное окно "Параметры поиска решения" с установленными флажками

Рис. 14. Заполненное окно "Поиск решения" по второму варианту. Нажмите кнопку Выполнить. Результаты решения показаны на рис. 15. Вы можете вывести результаты расчетов на отдельный лист. 57

Рис. 15. Результат решения транспортной задачи Мы решали транспортную задачу при условии минимума целевой функции – стоимости перевозки всего груза. С помощью Excel мы легко можем оценить преимущество расчетов. Выберите команду Максимальное значение.

Сервис–Поиск

решения

и

установите

переключатель

Результат показан на рис. 16. Видно что максимальная стоимость перевозок равна 1556 у.е. Таким образом применение методов линейного программирования в Excel дает возможность уменьшить затраты на перевозки максимум на 270 у.е.

58

Рис. 16. Результат решения транспортной задачи при условии максимума целевой функции Примечание: Решение в целых числах не всегда может быть получено. В таких случаях в окне диалога “Результаты поиска решения” выводится соответствующее сообщение. Одной из возможных причин может быть ошибка в записи ограничений.

59

Microsoft Excel 97. Работа № 7 Табличные базы данных (списки). Сортировка данных, фильтрация данных, подведение промежуточных итогов, разделение и закрепление областей. Цель работы: Работа с табличными базами данных, сортировка и фильтрация данных, Нахождение промежуточных итогов. Разделение и закрепление областей рабочего листа в Excel. Списки, или табличные базы данных – это такие таблицы, в которых все строки, за исключением заголовков, имеют одинаковую структуру и типы данных. Для работы со списками в Eхcel имеются дополнительные возможности, которые перечислены в заголовке лабораторной работы. В программе Excel они реализованы в меню Данные. Рассмотрим на примере рабочей книги (файла) Продажа быттехники такую таблицу на листе Товары все перечисленные цели работы. Запустите программу Microsoft Eхcel, затем откройте заготовку файла: С \ Мои документы \ Шаблон для Eхcel \ Продажа быттехники Сохраните файл в свою папку. Назовите его так: Продажа быттехники затем добавьте свою фамилию. Упражнение 1. Сортировка данных в электронных таблицах. Проанализируем содержимое ячеек таблицы. В столбцах A, B, C, D и E внесены наименования товаров, их модели, присвоены коды по порядку, цена поступления и количество каждого товара, в столбцах F, G и H введены формулы для расчета стоимости приобретенного товара, определения цены реализации с 20% наценкой и вычисления прибыли. Теперь рассмотрим, как можно выполнить сортировку данных в таблице Товары по полю Наименование товара. Поместите курсор на первую ячейку в столбце Наименование товара. Затем на панели инструментов Стандартная нажмите кнопку (рис. 1).

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

Рис. 1. Вид таблицы Товары перед началом сортировки. 60

Результатом этой операции должна стать таблица, в которой строки будут выстроены по алфавиту для данных первого столбца (рис. 2). При этом, как видно из рисунка, данные в остальных столбцах также переместились, отражая сведения по товару, а в столбце Коды последовательность нарушилась.

Рис. 2. Вид таблицы после сортировки по столбцу Наименование товара. Действуя по аналогии, отсортируйте данные таблицы по возрастанию значений в столбцах Количество, Цена поступления, Планируемая прибыль. Из полученного результата видно, что первую позицию занял товар с нулевой прибылью (рис. 3).

Рис. 3. Вид списка товаров после сортировки по столбцу Планируемая прибыль. В заключение, отсортируйте строки таблицы снова по возрастанию столбца Код. 61

Чтобы в дальнейшем было удобней работать с таблицей, изменим в ней порядок столбцов. Поставим в таблице столбец Код первым. Для этого необходимо вначале вставить пустой столбец перед столбцом Наименование товара. Поместите курсор на любую ячейку столбца А, затем в меню Вставка выберите пункт Столбцы (рис. 4). В таблице должен появиться новый пустой столбец (столбец A). При этом вся таблица сместилась вправо. Обратите внимание, что формулы, по которым, выполнялись расчеты в трех последних столбцах, по-прежнему работают правильно, несмотря на то, что имена столбцов изменились (с C, D и E на D, E и F). Это произошло благодаря относительным ссылкам на ячейки в формулах.

Рис. 4 Теперь, нужно выделить весь столбец D и вырезать его в буфер обмена, вызвав правой кнопкой мыши контекстное меню (рис. 5).

Рис. 5 62

Затем вырезанный столбец нужно вставить на место столбца A (рис. 6). Для этого выделите весь столбец А, правой кнопкой мыши вызовите контекстное меню и дайте команду Вставить.

Рис. 6 Теперь первым столбцом в таблице стал столбец Код, а столбец D остался пустой. Удалите столбец D. Для этого выделите весь столбец D и в контекстном меню выберите пункт Удалить (рис. 7). Запомните, как можно добавить новый столбец и как удалить ненужный столбец. Аналогично можно поступать и со строками электронной таблицы.

Рис. 7 Теперь требуется отсортировать данные в таблице по Наименованию товара, не сортируя при этом столбец с кодами. Выделите все ячейки с данными, исключая Код (диапазон ячеек В2:Н24), затем вызовите диалоговое окно Сортировка в меню Данные (рис. 8).

63

А) первоначальный вид окна Б) окончательный вид окна Рис. 8. Диалоговое окно Сортировка диапазона Рассмотрим содержимое диалогового окна, оно содержит несколько областей, в которых задаются (выбираются) параметры к предстоящей сортировке данных. Три области с полями для выбора критерия сортировки, причем каждая область имеет два направления сортировки – по возрастанию и по убыванию, также имеется область для выбора признака, по которому будут идентифицированы выделенные в таблице ячейки: а) по подписям первой строки выделенного диапазона ячеек (рис. 8А); б) по обозначениям столбцов листа (рис. 8Б). В нашем случае удобнее идентифицировать поля по обозначениям столбцов листа (по названиям столбцов рабочей книги Excel) и выбрать первый критерий сортировки Столбец В, а направление сортировки – по возрастанию. Установите все параметры для сортировки как показано на рис. 8Б. Нажмите кнопку OK. В результате этой операции все товары должны "выстроиться" по алфавиту, а Коды, поскольку они не попали в выделенный диапазон ячеек, так и остались располагаться по возрастанию. Вспомните, что происходило, когда просто сортировали товары по алфавиту! Упражнение 2. Использование фильтров в электронных таблицах. В Excel фильтры позволяют показать в таблице только нужные данные, а ненужные скрыть. Самый простой способ фильтрации списков – использование встроенного в программу Excel Автофильтра. Отобразим в таблице Товары только Холодильники. Выделите весь столбец B с наименованиями товаров, и в меню Данные выберите пункт Фильтр–Автофильтр (рис. 9).

64

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

Рис. 10. Список значений для определения критерия фильтрации. Если мы выберем в списке значение Холодильник, то в результате получим следующую таблицу (рис. 11). Обратите внимание, что кнопка Это означает, что фильтр включен.

Автофильтра

изменила

цвет

Рис. 11. Вид таблицы после назначения фильтра. 65

на

синий.

Для закрепления навыка по использованию фильтра назначьте фильтр на другие наименования товаров. Отменить фильтр можно, выбрав в списке фильтра значение (Все). Фильтрация данных может быть и более сложной. Выделите все столбцы таблицы и назначьте для них Автофильтр. Теперь каждый столбец с данными может быть отфильтрован самостоятельно. В фильтре Наименование товара выберите значение Газовая плита, а в фильтре Цена поступления – значение (Условие...). Затем в открывшемся окне Пользовательский автофильтр, настройте тип условия для фильтрации цены (например, меньше или равно) и назначьте предельное значение для этого условия – 3 000 (рис. 12).

Рис. 12. Вид окна Пользовательский автофильтр. В полученной таблице будут работать два фильтра. Один отображает только газовые плиты, другой из этих газовых плит покажет только те, цена у которых меньше или равна выбранному значению 3 000р.(рис. 13).

Рис. 13. Вид таблицы с двумя включенными фильтрами. Задания для самостоятельного выполнения. Задание 1. Задание 2.

Задание 3.

Проведите сложную фильтрацию по каким-либо полям таблицы Товары. Для фильтрации поля Количество назначьте два условия в Пользовательском автофильтре – первый тип условия – меньше с предельным значением 30 и второй тип условия – больше с предельным значением 10. Отключите режим фильтрации для выполнения следующего Упражнения. Снятие режима Автофильтр с таблицы выполняется так же, как и его назначение, повторным выбором в меню Данные пункта Фильтр– Автофильтр.

66

Упражнение 3. Подведение промежуточных итогов. Основным условием списковой таблицы является одинаковая структура строк и типов данных в них для всех ячеек таблицы. Это означает, что в середине списка не может быть строки, суммирующей какиелибо значения из верхних строк. Однако на практике такая необходимость часто возникает. Проблема решается с помощью встроенного в Excel средства Промежуточные итоги. Допустим, необходимо просуммировать количество товаров одного наименования и всего товара в целом. А заодно просуммировать стоимость этих товаров. В меню Данные выберем пункт Итоги... (рис. 14).

Рис. 14. В окне Промежуточные итоги необходимо настроить "При каждом изменении в:", "Операция:" и "Добавить итоги по:".

поля:

В поле "При каждом изменении в:" – выберите из списка Наименование товара. В поле "Операция:" – выберите из списка Сумма. В поле "Добавить итоги по:" – поставьте флажки напротив Количество, Стоимость, Планируемая прибыль. Затем проверьте, стоят ли флажки в нижней части окна у параметров отображения итогов – Заменить текущие итоги, Итоги под данными.

Рис. 15. Диалоговое окно Промежуточные итоги. 67

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

Рис. 16. Вид таблицы с развернутой структурой групп товаров и Промежуточными итогами по ним.

Рис. 17. Вид таблицы со скрытой структурой всех групп товаров, кроме Группы Пылесосы 68

Как видно из рисунка 17 символ минус после скрытия группы меняется на плюс. Нажав на кнопку с плюсом, можно вновь открыть группу. Примечание. Обратите внимание на верхнюю часть структурной панели, там имеются кнопки с номерами уровней структуры товаров (кнопки Уровни структуры). С помощью этих кнопок можно скрыть или развернуть все группы одного уровня. Поупражняйтесь в этом на своей таблице. Для отмены режима Промежуточные итоги следует снова открыть окно настройки (Данные–Итоги... и нажать кнопку Убрать все). Таблица вновь примет вид обычного списка. Существует и другой способ структурирования групп без использования режима Промежуточные итоги. При этом в таблице не возникают итоговые строки по группе. Выделите в таблице строки с товаром одного наименования – Стиральная машина. Затем в меню Данные выберите пункт Группа и структура–Группировать. В результате будет создана структурная группа для Стиральных машин, но без итоговой строки. Так же появится и структурная панель с кнопками уровней структуры. Но ее присутствие практически не имеет смысла, так как скрытие группы приведет к скрытию и всей информации об этих товарах, а итоговой строки у нас нет (рис. 18). Следовательно, ручное создание структурных групп имеет смысл при ручном создании итоговых строк. Такая необходимость встречается при практической работе в электронных таблицах не списковой структуры, например, при имеющихся в таблице ячейках суммирующих какие-либо показатели.

Рис. 18. Пример таблицы при ручном структурировании. 69

Задания для самостоятельного выполнения. Задание 1. Произведите группировку различных групп товаров таблицы Товары. Задание 2. С помощью кнопок Уровни структуры скройте и отобразите сгруппированные данные. Задание 3. Отмените созданные структурные группы (Данные–Группа и структура–Удалить структуру). Упражнение 4. Разделение и закрепление областей 1. Разделение областей. Часто при работе с большими таблицами возникает необходимость видеть строку заголовков и (или) столбец названий строк всегда, независимо от местонахождения текущей ячейки. Для этого в Excel встроена возможность разделения и закрепления областей таблицы. Допустим, в таблице Товары необходимо постоянно видеть заголовки таблицы и столбец с названиями товаров и их модели. Поместите курсор на ячейку D2. Затем в меню Окно выберите пункт Разделить. В таблице появятся две серые линии разделения. Обратите внимание, что пересечение линий произошло в верхнем левом углу выбранной ячейки, при этом каждая из 4 областей имеет вертикальную и горизонтальную полосы прокрутки. Точку пересечения этих линий можно мышью переместить в любое место таблицы, тем самым, закрепив другие области с данными (рис. 19).

Рис. 19. Вид таблицы с разделенными областями. Снимите разделение областей (Окно–Снять разделение). 2. Закрепление областей. Поместите курсор на ячейку D2. Затем в меню Окно выберите пункт Закрепить области. В таблице уже нет серых линий разделения и области имеют по одной полосе прокрутки – вертикальную и горизонтальную. Обратите внимание, что пересечение линий произошло в верхнем левом углу выбранной ячейки, при этом используя вертикальную и горизонтальную полосы прокрутки можно убедиться в том, что закрепленные области не перемещаются и невозможно переместить точку пересечения этих линий другое место таблицы. Снимите закрепление областей (Окно–Снять закрепление областей).

70

Образец исходного листа Товары файла Продажа быттехники

71

СОДЕРЖАНИЕ Microsoft Excel 97. Работа № 1 Знакомство с электронными таблицами Excel. ........................................................................... 3 Microsoft Excel 97. Работа № 2 Форматирование ячеек. Рабочие листы Excel. .......................................................................... 12 Microsoft Excel 97. Работа № 3 Относительные и абсолютные адреса ячеек. Графическое представление числовых данных......................................................................... 17 Microsoft Excel 97. Работа № 4 Связанные таблицы. ..................................................................................................................... 25 Microsoft Excel 97. Работа № 5 Автоматизация документа (на примере формирования прайс-листа). ................................... 35 Приложение к работе № 5............................................................................................................ 44 Microsoft Excel 97. Работа № 6 Задачи оптимизации. .................................................................................................................... 47 Microsoft Excel 97. Работа № 7 Табличные базы данных (списки). Сортировка данных, фильтрация данных, подведение промежуточных итогов, разделение и закрепление областей............................. 60

72