Файл: Методичка MS Excel(2011).doc

Различие между методами адресации ячеек проявляется при копировании формул. При копировании формулы записанные в ней абсолютные адреса не меняются, а относительные изменяются соответственно тому, как соотносятся адреса ячеек «откуда» и «куда» копировать. В смешанном адресе при копировании изменяется только та половинка адреса, перед которой нет знака $.

Пример: A4 =$B$6+3*F7-$A11/C$18

 (копирование)

D10 =$B$6+3*I13-$A17/F$18

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

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

3.3. Обращение к встроенным функциям

В MS Excel пользователь при записи формул в ячейки ЭТ может использовать встроенные функции. Все встроенные функции в зависимости от своего назначения разделены по категориям: Математические, Логические, Текстовые, Финансовые, Статистические, Инженерные и т.д.

Обращение к встроенным функциям может осуществляться по-разному:

1) Можно при записи формулы в ячейку набрать имя функции (большими буквами) и указать после него в скобках значения аргументов функции (через ;). При этом, если аргументом является адрес ячейки или блока, то его можно не набирать, а просто выделить нужную ячейку или блок в ЭТ (адрес запишется автоматически). Недостаток данного способа в том, что требуется хорошо знать синтаксис функции, т.е. ее точное имя, сколько и каких аргументов у этой функции и т.п.

2) Можно вызвать Мастер функций с помощью команды Вставка→Функция… (или кнопки f_x в строке формул). В окне диалога Мастера функций следует раскрыть список категорий функций (кнопка с треугольником поля Категории) и выбрать нужную категорию. Ниже отобразится список функций, входящих в данную категорию.

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

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

3) Наиболее часто используемые функции вынесены на панель инструментов Стандартная (в виде кнопки - Автосумма). К ним относятся: сложение значений блока (СУММ), поиск максимального значения в блоке (МАКС), поиск минимального значения в блоке (МИН) и вычисление среднего значения в блоке (СРЗНАЧ). Данные функции имеют простой синтаксис: в скобках после имени могут быть перечислены значения (адреса ячеек) для поиска через точку с запятой, либо просто указан адрес блока (как один аргумент). Через кнопку Автосуммы может также происходить обращение и к другим функциям.

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

3.4. Создание итоговой строки

Вычисление итога по столбцам, т.е. итоговой строки, одно из наиболее часто используемых действий, выполняемых в числовых таблицах. Поэтому в MS Excel данное действие было вынесено на панель инструментов Стандартная в виде кнопки – Автосумма.

Для быстрого вычисления итога по столбцу достаточно сделать текущей ячейку под этим столбцом (где должна отобразиться сумма) и щелкнуть по знаку ∑ на кнопке Автосуммы. В ячейку запишется обращение к функции СУММ, в качестве аргумента которой будет указан адрес блока вышестоящих ячеек (с числовыми значениями). Если предложенный адрес блока верен, следует завершить ввод, нажав Enter.

3.5. Функция СУММЕСЛИ

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

Синтаксис функции СУММЕСЛИ:

=СУММЕСЛИ (Блок1; Критерий; Блок2), где Блок1 – блок проверки критерия, Критерий – условие для проверки, Блок2 – блок для суммирования.

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

	А	В	С
1		Стоимость имущества	Комиссионные
2	Предприятие 1	100000	7000
3	Предприятие 2	250000	17500
4	Предприятие 3	150000	10500
5	Предприятие 4	350000	24500
6	Предприятие 5	300000	21000
7			=СУММЕСЛИ(В2:В6; “>200000”; C2:C6)

3.6. Установление связи между ячейками разных листов

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

Например: =Лист1!А5; =Лист2!А7+Лист2!В7;

=Лист2!В3+Лист5!С8-Лист3!Е2.

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

3.7. Практическое задание для выполнения на ПК

1. Запустить MS Excel.

2. Сформировать заголовок и шапку таблицы:

Расчёт заработной платы

Фамилия

Тариф

за 1

день

Число

отработан-ных дней

Начис-

лено

Удержания

Сумма

выдаче

3. Ввести произвольные данные в столбцы 1-3 (10 строк).

Число отработанных дней вводится за месяц (в среднем 21-22, но не более 31).

4. Заполнить расчётные столбцы таблицы по формулам:

"Начислено" = "Тариф" * "Число отработанных дней",

“Удержания”:

"1" = 1% от начисленного,

"2" = 13% от начисленного.

5. Для вычисления удержания 3 справа от таблицы (на пустом месте) сформировать табличку из двух ячеек:

Проценты удержания

В качестве значения процентов удержания записать произвольное значение (например, 7 или др.).

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

Методы адресации описаны в пункте 3.2 (стр.21).

6. Заполнить последний расчётный столбец таблицы по формуле:

"Сумма к выдаче" = "Начислено" – "Удержания".

7. Сделать итоговую строку для столбцов "начислено" и "сумма к выдаче".

8. Вызвать Мастер Функций и просмотреть список функций разных категорий. Научиться вызывать подсказку о действии нужной функции.

9. Под итоговой строкой вычислить сумму начисленного тем, у кого число отработанных дней не менее 15 (т.е. >=15).

10. Ниже сделать строку, в которой под соответствующими столбцами выдать минимальный тариф, среднее число отработанных дней и максимальное значение по удержанию 3 (использовать встроенные функции).

11. Перейти на Лист2 и сформировать таблицу:

Сведения об удержаниях

Фамилия

Удержания

12. Столбец "фамилия" заполнить, установив связь с соответствующими ячейками Листа1 (по формуле =Лист1!адрес ячейки).

"Удержания" вычислить как сумму всех удержаний (в формуле делать ссылки на ячейки Листа1).

13. Сделать итоговую строку, обрамление и цветовое оформление таблицы на Листе2.

14. На Листе3 сформировать таблицу:

Проценты от суммы к выдаче

Фамилия

15% от суммы к

выдаче

23% от суммы к

выдаче

15. Заполнить столбцы таблицы, устанавливая связь с ячейками Листа1.

16. Сделать итоговую строку, обрамление и цветовое оформление таблицы на Листе3.

17. Научиться менять формат ячейки (команда ФорматЯчейки).

18. В таблицу на Листе1 после столбца "фамилия" вставить 2 пустых столбца: "должность", "дата поступления". Заполнить эти столбцы произвольными данными.

Задать для вывода даты формат "ДД.ММ.ГГ". Например, 12.09.07 .

19. Сделать обрамление и цветовое оформление таблицы на Листе1.

20. Сохранить таблицу в файле Таблица3-фамилия в своей папке (фамилия) на диске D:.

Лабораторная работа N4

Построение графиков и диаграмм по данным таблицы

4.1. Вызов Мастера диаграмм

В MS Excel имеется возможность построения графиков и диаграмм по данным, записанным в ЭТ. Для этого используется специальный Мастер диаграмм.

Вызов Мастера диаграмм осуществляется с помощью команды Вставка→Диаграмма…, либо через кнопку – Мастер диаграмм на панели инструментов Стандартная. При этом желательно предварительно выделить блок ячеек, по данным которых будет происходить построение графика или диаграммы.

После запуска Мастера диаграмм на экране последовательно будут появляться 4 диалоговых окна (Шаги построения графика или диаграммы), в которых пользователь может задать требуемые параметры для построения графика или диаграммы. Внизу каждого окна расположены 4 кнопки: Отмена, Назад, Далее, Готово, используя которые пользователь может в любой момент завершить создание диаграммы (Готово), отменить ее создание (Отмена), прейти к следующему шагу (Далее) или вернуться к предыдущему шагу (Назад).

4.2. Выбор типа диаграммы (Шаг 1)

После запуска Мастера диаграмм первым появляется окно Выбор типа диаграммы (Шаг1). В этом окне (вкладка Стандартные) можно выбрать как Тип диаграммы: Гистограмма, Линейчатая, График, Круговая, Точечная, С областями, Кольцевая, Лепестковая и др., так и Вид ее отображения (на плоскости, объемно и т.д.). После выбора нужного типа и вида диаграммы (графика) следует щелкнуть по кнопке Далее.

4.3. Источник данных диаграммы (Шаг 2)

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

4.4. Параметры диаграммы (Шаг 3)

На Шаге 3 выдается окно Параметры диаграммы, в котором содержится 6 вкладок, используемых для изменения различных параметров диаграммы (графика): Подписи данных, Таблица данных, Заголовки, Оси, Линии сетки, Легенда.

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

Если в исходный диапазон (Шаг2) были включены ячейки с названиями (строк или столбцов), то эти названия будут автоматически записаны в легенду. Если исходный блок содержал только числовые данные, то легенда будет состоять из стандартных названий: Ряд1, Ряд2, Ряд3 и т.д.

После задания всех параметров диаграммы следует щелкнуть по кнопке Далее.

4.5. Размещение диаграммы (Шаг 4)

На Шаге 4 появляется окно Размещение диаграммы, в котором необходимо задать место, куда будет осуществляться вывод готовой диаграммы (графика). Она может помещаться на отдельный новый лист (переключатель отдельном). Тогда следует задать имя этого листа (стандартное имя: Диаграмма1 и т.д.). Можно также задать размещение диаграммы (графика) на одном из уже имеющихся листов ЭТ (переключатель имеющемся). В этом случае нужно выбрать название листа через раскрывающийся список.

Шаг 4 является последним при создании диаграммы (графика), поэтому далее следует щелкнуть кнопку Готово.

4.6. Изменение параметров диаграммы (графика)

Изменение параметров уже созданной диаграммы (графика) можно с помощью контекстного меню для диаграммы, которое вызывается щелчком правой кнопки мыши по диаграмме. Также, если просто отметить диаграмму мышью, то в меню появится команда Диаграмма, в списке которой будут перечислены команды для внесения изменений по всем 4-м шагам создания диаграммы: Тип диаграммы…, Исходные данные…, Параметры диаграммы…, Размещение…. Эти же команды будут присутствовать и в контекстном меню.

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

4.7. Практическое задание для выполнения на ПК

1. Запустить MS Excel.

2. На Листе1 сформировать таблицу:

Выручка от реализации товаров по магазину