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

Нажмите «ОК» и перейдите ко 2-му шагу Мастера функций.

Ø Шаг 2. Указание аргументов выбранной функции, т.е. исходных данных для получения результата

MS Excel по умолчанию предлагает перемножить все значения ячеек диапазона B3:I3.

Для получения искомого произведения необходимо указать 1-й и 2-й сомножители (Число 1 и Число 2):

· Число 1 – это стоимость 1 поездки в автобусе. Щелкните мышью на ячейке В3.

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

· Число 2 – это СУММА всех поездок за неделю, т.е. в качестве аргумента функции ПРОИЗВЕД выступает
встроенная функция СУММ. Как вызвать другую функцию?

o Щелкните мышкой в поле Число2.

o Слева от строки формул щелкните мышкой на поле со списком рядом с именем функции «ПРОИЗВЕД»

o Выберите функцию СУММ.

Обратите внимание, что окно функции ПРОИЗВЕД заменено на окно для ввода аргументов функции СУММ.

Проанализируйте как изменился вид функции ПРОИЗВЕД в строке формул.

o
Число 1 в функции СУММ – это диапазон ячеек C3:I3. Выделите эти ячейки мышью.

Чтобы переключиться в окно функции ПРОИЗВЕД щелкните мышью на слове ПРОИЗВЕД в строке формул.

Таким образом мы указали оба сомножителя, причем второй из них – встроенная функция.

Далее нажмите «ОК».

Ячейка J3 содержит полученный результат расчета, а в строке формул отражается конструкция формулы.

5.12. С помощью маркера заполнения получите сумму расходов за неделю по каждому виду транспорта.

5.13. Получите общий итог расходов за неделю с помощью функции Автосумма (S).

Результат работы должен быть примерно такой:

5.14.
Постройте диаграмму, отражающую динамику поездок на каждом виде транспорта:

5.14.1. Тип диаграммы – График.

5.14.2. Диапазон данных: Ячейки А3:А6; С3:I6. (динамические ряды строятся по количеству поездок, а именами рядов являются виды транспорта)

Напоминаем, что несмежные диапазоны ячеек выделяются с нажатой клавишей CTRL.

Ряд – Подписи оси Х – ячейки С2: I2 (названия дней недели).

5.14.3. Название диаграммы – Динамика поездок

5.14.4. Размещение на отдельном листе с именем «Динамика».

5.14.5. На полученном листе диаграммы увеличьте толщину линий каждого ряда (Правая кнопка мыши, «Формат ряда данных»).

Построенная диаграмма должна иметь примерно такой вид:

Лабораторная работа №3.

Цели лабораторной работы

Ø Установка точного значения ширины столбца.

Ø Работа с Мастером функций.

Ø Введение понятия «абсолютный адрес»,

Новую рабочую книгу сохраните на собственном диске в папке «MS Excel» как файл MS Excel с именем «Лабораторная работа 3».

Задание 1. Необходимо получить таблицу квадратов двузначных чисел, используя Мастер функций и возможности автозаполнения MS Excel.

1. Назовите Лист1 именем «Квадрат».

2. В ячейку А1 введите текст «Таблица квадратов двузначных чисел». Выровняйте текст по центру выделения ячеек А1:К1. (Главная – Выравнивание – Формат ячеек (Выравнивание – по Горизонтали))

3. В ячейки В2:К2 введите числа от 0 до 9. (Напоминаем, необходимо ввести только первые 2 числа, остальные цифры последовательности заполняются с помощью маркера заполнения.)

4. В ячейки А3:А11 аналогично введите числа от 1 до 9.

5. Выделите столбцы А:К и установите для них одинаковую ширину – 5 символов (Формат – Столбец – Ширина).

Получится таблица следующего вида:

Предполагается, что цифры по вертикали (в столбце А) обозначают число десятков двузначного числа, а цифры по горизонтали (строка 2) – число единиц двузначного числа.

Т.е., например, в ячейке Н3 подразумевается число 16, а в ячейке Е11 – число 93.

6. Необходимо в ячейку В3 поместить, формулу, возводящую в квадрат число 10. Для этого воспользуемся Мастером функций.

6.1. Выполните команду «Формулы – Вставить Функцию»

6.2. Шаг 1. Категория функции – Математические; Имя функции – СТЕПЕНЬ. Кнопка " ОК ".

6.3. Шаг 2. В окне для задания аргументов функции:

Ø в поле Число введите выражение А3*10+В2 (число десятков, умноженное на 10 плюс число единиц). Не забудьте! Адреса ячеек в выражении нужно указывать по щелчку мыши, а не вводить с клавиатуры!

Ø в поле Степень введите цифру 2 (квадрат – это вторая степень);нажмите «ОК»

Если теперь распространить вправо и вниз полученную формулу с помощью маркера заполнения, то необходимый результат не будет достигнут. MS Excel автоматически изменит с учетом смещения адреса ячеек, на которые ссылается формула. Так в ячейке С3, например, возведется в квадрат не число 11, а число, вычисленное по формуле =В3*10+С2. (т.е. 100*10+1=1001)

Т.е. нужно или вводить одну и ту же формулу 100 раз, или каким-то образом указать MS Excel, что число десятков можно брать только из столбца А, а число единиц только из строки 2.

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

Для создания абсолютной ссылки на ячейку ставится знак доллара ($) перед той позицией адреса, которая не должна изменяться.

Для установки знака доллара необходимо:

1. Дважды щелкнуть мышью на ячейке, содержащей формулу.

2. Установить курсор мыши перед закрепляемой позицией.

3. Поставить знак доллара одним из следующих способов:

· комбинацией клавиш Shift +4 на латинском регистре

· Однократным нажатием клавиши F4 – полный абсолютный адрес, при копировании фиксируются позиции и строки, и столбца (Например, $A$1)

· Двукратным нажатием F4 - неполный абсолютный адрес, закреплена (при копировании не меняется) позиция строки (Например, C$3)

· Трехкратным нажатием F4 - неполный абсолютный адрес, закреплена (при копировании не меняется) позиция столбца (Например, $B1)

· Четырехкратное нажатие F4 превращает адрес ячейки в относительный (М5)

7. Распространим действие формулы из ячейки В3 на остальные ячейки таблицы:

7.1. Щелкните дважды на ячейке В3 и приведите формулу с помощью клавиши F4 к следующему виду =СТЕПЕНЬ($A3*10+B$2;2).

7.2. Нажмите Enter.

7.3. Заполните формулой с помощью маркера заполнения свободные ячейки таблицы (вправо, затем, не снимая выделения вниз).

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

 

9. Результаты предъявите преподавателю.

Задание 2. Знакомство с категорий Статистических функций MS Excel, использование абсолютного адреса. Повторение приемов форматирования таблиц, изменения форматов ячейки, построения диаграмм

1. Назовите Лист2 именем «Налог».

2. Спроектируйте нижеприведенную таблицу:

2.1. При оформлении первых трех строк используйте параметр горизонтального выравнивания "По центру выделения" (Главная – Выравнивание – Формат ячеек (Выравнивание – по Горизонтали))

2.2. Ячейки с заголовками столбцов таблицы выровняйте по горизонтали и вертикали «По центру» и примените отображение «Переносить по словам».

2.3. Ширина столбца А – 30 символов (Главная – Формат – Ширина Столбца).

2.4. Шрифт всей таблицы Times New Roman, обычный, 14.

2.5. Установите внешние и внутренние границы созданной таблицы (Главная – Шрифт – Изменение границ)

3. Произведите расчет показателей:

3.1. Итоговые суммы рассчитайте с помощью функции Автосумма (Формулы - кнопка ∑).

3.2. Средние значения рассчитайте с помощью Мастера функций (Формулы – Вставить Функцию):

3.2.1. Шаг 1. Категория - Статистические, Функция – СРЗНАЧ.

3.2.2. Шаг 2. Укажите мышью диапазон ячеек для расчета среднего:

· B5:D5 для подсчета средней суммы по району

· В5:В12 для расчета среднего значения по городу

3.3. При расчете доли необходимо рассуждать следующим образом:

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

· При этом «Всего» по городу составляет 100%. Долю итога по району нужно определить.

· Составим пропорцию:

2768,5 – 100%

420 - х%

Как известно

· При применении процентного формата ячеек MS Excel автоматически производит умножение содержимого ячейки на 100 и добавляет знак %.

· Поэтому в ячейке F5:

установите процентный формат (Главная – Число – Процентный);

введите формулу =Е5/Е13

· Полученную формулу нельзя распространить вниз в диапазоне F6:F12. Т.к. в ней должны меняться цифры по районам (числитель), а знаменатель (Всего по городу) остается без изменения, то необходимо воспользоваться возможностями абсолютной ссылки (адреса). В ссылке на адрес ячейки Е13 нужно закрепить позицию строки. Поэтому:

o Щелкните дважды в ячейке F5 (результат вычисления принимает вид формулы)

o Щелкните в формуле на адресе ячейки Е13

o Дважды нажмите функциональную клавишу F4, чтобы получить следующую конструкцию адреса Е$13

o Нажмите Enter