Технология работы с формулами на примере подсчета стоимости обслуживания.

Государственное образовательное учреждение

Высшего профессионального образования

Донской государственный технический университет

(ДГТУ)

МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ

ПО РАБОТЕ С ТАБЛИЦАМИ

В СРЕДЕ MS EXCEL

Ростов – на – Дону

2007

Кафедра «Информатика»

Составитель: к.т.н. Крыгина Л.Ф.

Методические указания к выполнению лабораторных работ в среде MS Excel. — ДГТУ,  Ростов-на-Дону, 2007, 20 с.

Методические указания содержат задания и рекомендации по их выполнению в среде MS Excel.

Печатается по решению методической комиссии факультета «Автоматизация и информатика»

Ответственный за выпуск: доц. Галин А.Б.

© ДОНСКОЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ

2007

ЗАДАНИЕ 1

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

2. Выполните настройку книги.

3. Освойте технологию переименования листов книги.

4. Сохраните рабочую книгу.

5. Для приобретения навыков работы в среде Excel необходимо выполнить все техноло­гические операции, приведенные ниже.

ТЕХНОЛОГИЯ РАБОТЫ

1. Создайте новую рабочую книгу, воспользовавшись одним из следующих вариантов:

1-й вариант.При загрузке среды Excel 97 на экране появляется новая книга со стан­дартным именем Книга (номер);

2-й вариант. На экране уже отображена созданная ранее книга с уникальным именем. В этом случае для создания новой книги воспользуйтесь командой Файл, Создать.

2. Сделайте настройку среды Excel для рабочей книги, в которой будет вестись учет обслуживания туристических экскурсий на маршрутах А, Б и т.д. Выполните команду Сервис, Параметры и в диалоговом окне установите следующие параметры:

- на вкладке Общие:

§ Стиль ссылок: А1, т.е. нет флажка;

§ Листов в новой книге — 2;

§ Стандартный шрифт — Arial Суг, размер 10;

§ Выберите рабочий каталог для сохранения новых книг;

§ Введите имя пользователя;

- на вкладке Вид, установите флажки следующих параметров:

§ Отображать: строку формул, строку состояния ;

§ Примечания: не отображать ;

§ Объекты: отображать;

§ Параметры окна: сетка, заголовки строк и столбцов, горизонтальная и вертикаль­ная полосы прокрутки, ярлычки листов, авторазбиение на страницы;

- на вкладке Вычисления, установите флажки следующих параметров:

§ Автоматически производить вычисления ;

§ Точность: как на экране;

- на вкладке Правка, установите все флажки, кроме параметра Фиксированный десятичный формат при вводе.

3. Переименуйте рабочий лист, выполнив следующие действия:

                   - установите указатель мыши на Лист 1 и вызовите контекстное меню, щелкнув правой клавишей мыши;

   - выберите в контекстном меню команду Переименовать;

   - введите в диалоговом меню новое имя листа (например, Туристы) .

4. Сохраните созданную рабочую книгу под именем Турагентство.xls в своей папке диска H:, выполнив команду Файл, Сохранить как.В диалоговом окне уста­новите следующие параметры:

Папка: имя своей папки

Имя файла: Турагентство

Тип файла: книга Microsoft Excel

5. Тренинг работы с листами и книгами. Проделайте типовые техно­логические операции.

Таблица 1.Типовые технологические операции с рабочими книгами и листами

Контрольные вопросы

1. Что понимается под рабочей книгой электронной таблицы?

2. Какие типовые операции с рабочими книгами вы знаете?

3. Какие типовые операции с рабочими листами вы знаете?

ЗАДАНИЕ 2

 Сформируйте структуру таблицы и заполните ее постоянными значениями — числа­ми, символами, текстом.

В качестве примера рассматривается таблицы обслуживания туристических экскурсий на маршрутах А, Б и т.д. (рис. 1).

В любой таблице всегда можно выделить минимум две структурные части – название и ее шапку.

Рис. 1. Таблицы обслуживания туристических маршрутов.

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

- задайте способ выравнивания названия граф (при больших текстах необходимо обес­печить перенос по словам);

- в каждую ячейку одной строки введите названия граф таблицы;

- установите ширину каждого столбца таблицы.

После окончания оформления шапки таблицы введите в таблицу постоянные данные:

- Тарифы на маршруты: экскурсия и транспорт;

- Число заявок на маршруты.

После окончания работы по заполнению таблиц постоянными данными запомните ее как рабочую книгу.

Для лучшего понимания технологии работы в Excel выполните тренинг. Для этого проделайте все операции, указанные в табл. 3.

Примечание. Все расчеты будут производиться в следующем задании.

ТЕХНОЛОГИЯ РАБОТЫ

1. Загрузите с жесткого диска созданный в работе 1 шаблон таблиц с именем Турагентство:

- выполните команду Файл, Открыть;

- в диалоговом окне установите следующие параметры:

Папка: имя вашей папки

Имя файла: Турагентство

Контрольные вопросы

1. Опишите структуру типового интерфейса электронной таблицы.

2. Как указывается адрес ячейки?

3. Как указывается блок (диапазон) ячеек при выполнении какой-либо команды?

4. Какие операции форматирования электронной таблицы вы знаете, и как они выполняются?

5. Какие технологические операции над ячейка и блоками ячеек вы знаете, и как они выполняются?

ЗАДАНИЕ 3

Технология работы с формулами на примере подсчета стоимости обслуживания.

О каждом маршруте известна стоимость собственно экскурсии и стоимость транспортных расходов. Известна также емкость автобуса. В самой таблице фиксируется желаемый маршрут и число заявок (человек) на обслуживание. Минимальное число автобусов определяется как целая часть от <Число заявок>/<Вместимость автобуса>. Фирма обслуживает не всех туристов, а только такое их максимальное количество, чтобы не оказалось ни одного автобуса, заполненного менее чем на 30%. Фактическое число определяется в колонке <выделено> автобусов. Для этого, нужно выяснить, сколько туристов еще не размещено в автобусы. Если их оказалось больше чем 30% емкости автобуса, значит, <выделено> автобусов будет на единицу больше минимально необходимого их числа. В противном случае, будет <выделено> этот минимум. <Стоимость обслуживания > определяется произведением числа выделенных автобусов на сумму экскурсионного и транспортного обслуживания маршрута. В колонке <Примечание> в столбце F следует показать число пустых мест в автобусе или число отклоненных заявок (то, что есть). Рядом в столбце G должно быть выведено (отклонено заявок) или (пустых мест) по факту. В клетке Е14 показать число обращений для обслуживания более 100 заявок. Для выявления стоимости маршрута из таблицы тарифов следует воспользоваться функцией ГПР().

В созданной в предыдущем задании рабочей книге, хранящейся в файле с именем Турагентство, рассчитайте:

v Минимально-необходимое число автобусов и количество фактически выделенных автобусов;

v Стоимость обслуживания маршрутов;

v Количество отклоненных заявок и пустых мест в автобусе.

Все расчеты производятся с помощью формул, которые вводятся в соответствующие ячейки таблицы. В формулах используются функции, встроенные в MS Excel. Формулы будем вводить в строку 9 для маршрута А. Для расчета показателей остальных маршрутов, скопируем формулы в соответствующих столбцах.

1. Минимально-необходимое количество автобусов подсчитывается с помощью функции ОКРВНИЗ().
2. Количество фактически выделенных автобусов, число пустых мест в автобусе и число отклоненных заявок рассчитывается с помощью функции ЕСЛИ().
3. Стоимость обслуживания маршрута подсчитывается с помощью функции ГПР.

ТЕХНОЛОГИЯ РАБОТЫ

1. Загрузите с жесткого диска рабочую книгу с именем Турагентство:

- выполните команду Файл, Открыть;

- в диалоговом окне установите следующие параметры:

Папка: имя вашей папки

Имя файла: Турагентство

Тип файла: Книга Microsoft Excel

1. Для ввода формул воспользуйтесь Мастером функций.

- установите курсор в ячейку С9 и выберите мышью на панели инструментов кнопку Мастера функций;

- в 1-м диалоговом окне выберите вид функции:

Категория: — Математические

Функция: — ОКРВНИЗ (функция округляет число до ближайшего меньшего по модулю целого).

- щелкните по кнопке <ОК>;

- во 2-м диалоговом окне, устанавливая курсор в каждой строке, введите соответ­ствующие операнды функции:

Число (округляемое числовое значение) – В9/$В$5 (<число заявок>/<Мест в автобусе>);

Значимость (кратное, до которого требуется округлить) – 1 (т.е. до целого).

- щелкните по кнопке <ОК>

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

3. С помощью Мастера функции введите формулы в остальные ячейки данной строки.

- В ячейку D9 введите формулу:

=ЕСЛИ((B9-$B$5*C9)*100%/$B$5>=30%;C9+1;C9).

Логическое выражение - (B9-$B$5*C9)*100%/$B$5>=30% (условие проверки количества заявок, не поместившихся в минимально выделенное число автобусов);

Значение_если_истина -C9+1 (число автобусов увеличивается на 1, если число оставшихся заявок составляет 30 и более процентов от количества мест в автобусе);

Значение_если_ложь - C9 (число выделенных автобусов остается равным минимально выделенному количеству).

- В ячейку Е9 введите следующую формулу:

=D9*(ГПР(A9;$B$2:$E$4;2;ЛОЖЬ)+ГПР(A9;$B$2:$E$4;3;ЛОЖЬ)).

Число выделенных автобусов (ячейка D9) умножается на сумму стоимости экскурсии и стоимости транспорта. Стоимость экскурсии и транспорта выбираются из таблицы тарифов с помощью функции ГПР.

       В диалоговом окне мастера функции ГПР вводятся следующие операнды:

       Искомое значение – А9 – адрес ячейки, куда надо поместить полученное значение;

       Таблица - $B$2:$E$24 – диапазон ячеек, в котором размещена таблица тарифов (ссылки абсолютные, т.к. не должны изменяться при копировании формулы);

       Номер строки – номер строки в таблице, из которой должно быть возвращено сопоставляемое значение. Первая строка в таблице имеет номер 1. Строка тарифов на экскурсии имеет номер 2, а на транспорт – 3.

       Диапазон просмотра – ЛОЖЬ (если искомые данные в таблице отсортированы по возрастанию, вводится значение ИСТИНА, в противном случае – ЛОЖЬ);

- В ячейку F9 введите формулу:

=ЕСЛИ(D9>C9;D9*$B$5-B9;B9-D9*$B$5)

Логическое выражение - D9>C9 (сравнение фактически выделенного и минимального числа автобусов);

Значение_если_истина - D9*$B$5-B9 (число выделенных автобусов (ячейка D9) умноженное на число мест в автобусе (ячейка В5) минус число заявок (ячейка В9));

Значение_если_ложь - B9-D9*$B$5 (число заявок (ячейка В9) минус число пассажиров в выделенных автобусах (D9*$B$5).

В ячейку G9 введите формулу:

=ЕСЛИ(D9>C9;"(пустых мест)";"(отклонено заявок)").

Логическое выражение - D9>C9 (сравнение фактически выделенного и минимального числа автобусов);

Значение_если_истина - "(пустых мест)";

Значение_если_ложь -"(отклонено заявок)".

4. Скопируйте эти формулы во все остальные ячейки столбцов:

- выделите блок ячеек А9 : G9;

- установите курсор в правый нижний угол выделенного блока и, после появления черного крестика, нажав левую кнопку мыши, протащите ее до строки 12 включительно.

1. Подсчитайте общее число заявок и общую стоимость обслуживания в строке Всего:

- выделите блок ячеек В9:В13;

- нажмите кнопку автосуммирования на панели инструментов;

- повторите те же действия для диапазона ячеек Е9:Е13.

1. Подсчитайте число крупных заявок (более 100):

- В ячейку Е14 введите формулу

 =СЧЁТЕСЛИ(B9:B12;">100").

Функция СЧЕТЕСЛИ() подсчитывает количество непустых ячеек в указанном диапазоне, удовлетворяющих заданному условию:

Диапазон - B9:B12 (содержит число заявок на маршрутах);

Условие – “>100” – (более 100 заявок).

В результате выполнения вышеуказанных действий должна положиться таблица, приведенная на рис. 2.

7. Переименуйте текущий лист:

- установите курсор на имени текущего листа и вызовите контекстное меню;

- выберите параметр Переименоватьи введите новое имя, например Маршруты.

8. Сохраните рабочую книгу.

9. Закройте рабочую книгу командой Файл, Закрыть.

 Рис. 2. Таблица расчета стоимости обслуживания туристических маршрутов

Контрольные вопросы

1. Перечислите и поясните основные типы входных данных, которые могут быть введены в ячейки электронной таблицы.

2. Что такое формула в электронной таблице и ее типы. Приведите примеры.

3. Что такое функция в электронной таблице и ее типы. Приведите примеры.

4. Поясните очередность выполнения операций в арифметических формулах.

5. Поясните, для чего используются абсолютные и относительные адреса ячеек.

6. В чем суть правил автоматической настойки формул при выполнении операций копирования и перемещения?

7. Покажите на примерах все возможные варианты автоматического изменения адресов в формулах при выполнении операции копирования.

8. Покажите на примерах все возможные варианты автоматического изменения адресов в формулах при выполнении операции перемещения.

ЗАДАНИЕ 4

Для таблицы на рис. 2 постройте два вида диаграмм — внедренную на лист с исходными данными и на отдельном листе.

Для этого необходимо выполнить следующие действия:

1. Построить внедренную диаграмму, оформив ее так, как показано на рис. 13.

2. Построить диаграмму другого типа и разместить ее на отдельном листе.

ТЕХНОЛОГИЯ РАБОТЫ

1. Постройте внедренную диаграмму, выполнив следующие операции:

нажмите кнопку Мастер диаграмм или выполните команду Вставка, Диаграмма.

Этап 1 . Выбор типа и формата диаграммы:

§ на вкладке Стандартные выберите тип диаграммы Гистограмма и вид диа­граммы — номер 3;

§ щелкните по кнопке <Далее>.

Этап 2. Выбор и указание диапазона данных для построения диаграммы:

§ на вкладке Диапазон данных установите переключатель Ряды в столбцах;

§ выделите диапазон данных А7:А12;

§ нажмите клавишу Ctrl и удерживая ее выделите диапазон С7:D12.

§ в том же диалоговом окне щелкните по вкладке Ряд;

§ в окне Ряд выделена строка с названием Ряд1, установите курсор в строке Имя и щелкните в ячейке А7с названием Номера маршрутов;

§ для создания подписей по оси X щелкните в строке Подписи оси X и выделите данные первого столбца таблицы, т.е. диапазон А8:А12;

§ щелкните по кнопке <Далее>.

Этап 3 . Задание параметров диаграммы:

§ на вкладке Заголовки введите названия в соответствующих строках:

§ Название диаграммы: Сведения о транспорте

§ Ось X: Номера маршрутов

§ Ось Y: Кол-во автобусов

§ на вкладке Легенда поставьте флажок Добавить легенду и переключатель Справа;

§ щелкните по кнопке <Далее>.

Этап 4 . Размещение диаграммы:

§ установите переключатель Поместить диаграмму на имеющемся листе

§ щелкните по кнопке <Готово>;

§ в результате на рабочем листе будет создана внедренная диаграмма.  

§ выделите на диаграмме область заголовка оси значений, вызовите контекстное меню правой кнопкой мыши и выберите пункт Формат названия оси;

§ в диалоговом окне Формат названия оси на вкладке Выравнивание установите Ориентацию вдоль оси, т.е. повернуть надпись на 90^о, выравнивание текста по горизонтали и по вертикали – по центру.

§ Сравните результат с рис. 3.

Рис. 3. Диаграмма сведений о выделенном транспорте по маршрутам.

Внимание! Для изменения размера диаграммы установите кур­сор мыши в поле диаграммы и один раз щелкните левой кноп­кой на контуре диаграммы. На контуре появятся выделенные черные метки (квадраты). Установите курсор мыши на эти мет­ки. Курсор мыши изменит свое начертание на черную тонкую двустороннюю стрелку <—>. Удерживая нажатой левую кнопку, протащите мышь для изменения размеров поля диаграммы.

2. Постройте диаграмму другого типа на отдельном листе. Для этого выполните дейст­вия, аналогичные описанным в п. З, но на четвертом шаге установите переключатель На отдельном листе.

Контрольные вопросы

1. Перечислите и поясните содержание основных технологических этапов работы с электронной таблицей.

2. Расскажите, для чего используются диаграммы.

3. Какие типы диаграмм вы знаете?

4. Как построить диаграмму?

5. Как отредактировать диаграмму?

Литература

1. Информатика. Б.В. Соболь и др. Учебник. –Ростов – на – Дону: Феникс, 2005.

1. Информатика:Учебник. – 3-е перераб. Изд. /Под ред. Проф. Н.В.Макаровой. –М.: Финансы и статистика, 2006.

2. Информатика: Практикум по технологии работы на компьютере/ Под ред. Проф. Н.В.Макаровой. –3-е изд. Перераб. –М.: Финансы и статистика. 2000.

3. Информатика: Учеб. Пособие для студ. пед. Вузов / А.В. Могилев, Н.И.Пак, Е.К.Хеннер; Под ред. Е.К.Хеннера. –М., 1999.

4. Информатика. Базовый курс / Симонович С.В. и др. – СПб: Питер, 2007.