Методические рекомендации к практическим работам по дисциплине «Информатика» часть 2
методическая разработка по информатике и икт (11 класс) на тему

Практическая работа № 4. Использование функций Excel.

Цель работы: Закрепление навыков по использованию функций Excel: решение типовых задач по обработке массивов с использованием электронных таблиц, ознакомление с логическими функциями Excel.

Общие сведения

Вставка   функций

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

Функция — заранее определенное выражение, которое имеет один или несколько аргументов и возвращает единственное значение. В состав Excel входит свыше 250 функций.

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

• выделить ячейку, в которую следует поместить результат;
• выполнить команду Вставка -» Функция;
• в открывшемся окне выбрать категорию функций (рис. 3.20);
• выбрать из списка нужную функцию и нажать кнопку ОК;
• в открывшемся окне задать диапазон ячеек вручную или с помощью мыши;
• нажать кнопку ОК.

            Логические   функции

Список логических функций можно увидеть, выбрав в первом окне Мастер функции — «Логические».

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

ЕСЛИ (логическое выражение; выражение 1; выражение 2).

Если логическое выражение принимает значение «Истина», то функция ЕСЛИ принимает значение «Выражение 1»; если логическое выражение принимает значение «Ложь», то функция ЕСЛИ принимает значение «Выражение 2».

В качестве «Выражение 1» и «Выражение 2» можно записать вложенную функцию ЕСЛИ. Число вложенных ЕСЛИ не должно превышать семи.

Порядок работы

1. На Рабочем листе создайте таблицу по предложенному образцу (рис. 3.21).

1. Определите для всей таблицы в целом:

•        минимальное количество осадков, выпавшее за три года;
. суммарное количество осадков, выпавшее за три года;

• среднемесячное количество осадков по итогам трехлетних наблюдений;
• максимальное количество осадков, выпавшее за один месяц, по итогам трехлетних наблюдений;
• количество засушливых месяцев за все три года, в которые выпало меньше 10 мм осадков.

3.        Те же данные определите для каждого года и оформите в виде отдельной электронной таблицы в соответствии с рис. 3.22.

Примечание. При вводе года в таблице должны отражаться данные именно за этот год, в случае некорректного ввода должно выдаваться сообщение «Данные отсутствуют».

Для выполнения расчетов заполните формулами ячейки G4:G8, используя Мастер функций.

Рис. 3.21. Образец оформления результатов расчета

4.        В ячейку G4 введите формулу: =MAKC(B5:D16), а в ячейку G5=MHH(B5:D16) и так далее в соответствии с требуемой обработкой двухмерного массива B5:D16.

Рис. 3.22. Вариант оформления таблицы

5. Определите количество засушливых месяцев за три года. Для этого воспользуйтесь функцией СЧЕТ ЕСЛИ, которая подсчитывает количество полных ячеек, удовлетворяющих заданному критерию внутри интервала.
6. Ячейку G11 отведите для ввода года и присвойте ей имя «ГОД» (Вставка -» Имя -» Присвоить), именованная ячейка будет адресоваться абсолютно.
7. В ячейку G12 с использованием Мастера функций введите формулу:

=ЕСЛИ(ГОД=2002; МАКС(В5:В16); ЕСЛИ(ГОД=2003; МАКС(С5:С16); ЕСЛИ (ГОД=2004; MAKC(D5:D16); «Данные отсутствуют»))).

8.        Для выполнения следующих выборок эту формулу скопируй те в ячейки G13:G16 и отредактируйте, заменив функцию МАКС
на требуемую по смыслу. Но прежде замените относительную адресацию на абсолютную, иначе копирование формулы будет про
изводиться неправильно, т. е. формула должна принять следующий
вид:

=ЕСЛИ(ГОД=2002; МАКС($В$5:$В$16); ЕСЛИ(ГОД=2003; МАКС($С$5:$С$16); ЕСЛИ(ГОД=2004; MAKC($D$5:$D$16); «Данные отсутствуют»)))

9.        Введите в ячейку G11 год 2002 и проверьте правильность
заполнения.

10.        Сохраните результаты работы в файле с именем «Work2.xls».

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

1. Что такое функция?
2. Какими способами можно вставить функцию в формулу?
3. Как работает программа «Мастер функций»?
4. Какие категории функций вы знаете?
5. Как можно определить максимальное значение в диапазоне ячеек?
6. Напишите формат функции СЧЕТ ЕСЛИ и объясните, как работает эта функция.
7. К какой категории относится функция ЕСЛИ? Когда используется эта функция?
8. Как адресовать ячейку абсолютно?

Практическая работа № 5. Работа с листами. Построение диаграмм.

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

Общие сведения

Листы книги Excel можно переименовывать, добавлять, удалять, вставлять и т.д. Для переименования Рабочего листа необходимо нажать правую кнопку мыши на его ярлычке и в появившемся контекстном меню выбрать команду Переименовать. Затем удалить старое имя, ввести новое и нажать клавишу [Enter].

С помощью контекстного меню можно также удалять, вставлять, копировать листы.

Установка   связей   между   Рабочими листами

В Excel можно создавать формулы со ссылками на ячейки других листов. Это происходит следующим образом:

• создайте на Рабочих листах требуемые таблицы;
• выделите ячейку, в которую будет копироваться значение, наберите знак «=»;
• перейдите на лист, в котором находится нужное значение, выберите нужную ячейку и нажмите клавишу [Enter];
• в строке формул должна появиться формула, в которой за именем листа следует восклицательный знак, а перед буквой столбца и номером строки стоит знак «$», например: =Лист2!$Б$12.

Построение   диаграмм

Диаграмма — это удобное средство графического представления данных. Создать диаграмму легче всего с помощью Мастера диаграмм.

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

действия:

•        подготовьте лист со столбцами и строками, снабдите их надписями, которые впоследствии появятся на диаграмме;

• выделите диапазон ячеек с данными:
• нажмите кнопку Мастер диаграмм
• откроется окно Мастер диаграмм, в котором предлагается выбрать тип диаграммы (рис. 3.23). После выбора нажмите кнопку Далее;

Рис. 3.23. Первое окно Мастер диаграмм

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

Созданную диаграмму можно масштабировать, форматировать, перемещать и удалять:

• для масштабирования следует установить указатель мыши на край диаграммы и щелкнуть левой кнопкой мыши — диаграмма будет выделена. Теперь для изменения ее размеров можно использовать один из маркеров.
• для форматирования следует сделать по диаграмме двойной щелчок. Откроется окно диалога «Формат области диаграммы», куда можно ввести необходимые изменения.
• для удаления необходимо выделить диаграмму и нажать клавишу [Delete].

Порядок работы

1. Подготовьте ведомость на выдачу заработной платы (приведенные данные условные). Поскольку в дальнейшем будем рабо

                        Рис. 3.24. Примерный вид таблицы на листе «Начисления»

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

1. лист — «Начисления» (Сведения о начислениях);
2. лист — «Диаграмма»;
3. лист — «Детские» (Ведомость на выдачу компенсации на детей);

     4 лист — «К выдаче» (Ведомость на выдачу заработной платы);
2. Создайте таблицу на листе «Начисления» (рис. 3.24). Для это
го выполните следующие действия:

• запустите Excel;
• сформируйте строки заголовка;

• измените ширину столбца (в зависимости от объема вводимой информации);

• выполните обрамление таблицы;

• определите формат числа «Денежный» для ячеек, содержащих суммы. Данное действие можно выполнить до ввода данных в таблицу;

• заполните ячейки столбца последовательностью чисел 1, 2...;
• введите формулы в верхнюю ячейку столбца;

• скопируйте формулы вниз по столбцу и в некоторых случаях вправо по ряду;
• заполните таблицу текстовой и фиксированной числовой информацией;

• выполните сортировку строк;

• рассчитайте величину профсоюзных и пенсионных взносов, исходя из того, что каждый из них равен 1 % от оклада;
• рассчитайте подоходный налог по формуле: 13 % от оклада за вычетом минимальной заработной платы и пенсионного налога.
• для подсчета суммы к выдаче примените формулу, вычисляющую разность оклада и налогов.

•        заполните столбцы «ФИО», «Оклад» и «Число детей», после
того как введены все формулы. Результат будет вычисляться сразу же после ввода данных в ячейки. При желании воспользуйтесь режимом Формы для заполнения таблицы.

3. Постройте диаграмму на основе готовой таблицы:

•        выделите заполненные данными ячейки таблицы, относящиеся к столбцам «ФИО» и «Сумма к выдаче»;

          Рис. 3.25. Примерный вид диаграммы                  Рис. 3.26. Примерный вид таблицы начислений

•        запустите Мастер диаграмм и, передвигаясь по шагам, создайте диаграмму в соответствии с рис. 3.25.

3. Создайте ведомость на получение компенсации на детей на основе таблицы начислений (рис. 3.26):

• перейдите к листу «Детские»;
• сформируйте заголовки таблицы;

• в столбец «ФИО» поместите список сотрудников, который имеется на листе «Начисления», для этого установите связь между листами. Сначала перейдите на лист «Детские» и в первую ячейку столбца «ФИО» введите знак «=», символизирующий ввод формулы. Затем перейдите на лист «Начисления», выделите первую ячейку в столбце «ФИО» и нажмите клавишу [Enter]. Активным станет лист «Детские», где отобразится первая фамилия в списке. Для переноса всех остальных фамилий выполните копирование формулы по столбцу «ФИО» на листе «Детские».

Список фамилий теперь есть и на листе «Детские». Если внести новые данные в таблицу начислений, они отразятся и на листе «Детские». Нужно будет только распространить формулу далее:

•        в графе «Сумма» аналогичным образом разместите формулу: =Начисления!НЗ*57,

где НЗ — адрес первой ячейки на листе «Начисления», содержащей число детей; 57 — пособие на одного ребенка;

•        скопируйте эту формулу вниз и примените формат числа «Денежный»;

•выполните обрамление таблицы.

4. Создайте ведомость на выдачу заработной платы. Оформите лист «К выдаче» в соответствии с рис. 3.27, в котором будут отображаться «ФИО» сотрудников, а также «Сумма к выдаче» и «Подпись».        

5. Сохраните результат работы в файле с именем «Work3.xls».

Рис. 3.27. Примерный вид ведомости на выдачу заработной платы    

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

1. Как осуществляется переход между Рабочими листами книги?
2. Как удалить лист из Рабочей книги?
3. Какие способы переименования Рабочего листа вы знаете?
4. Как устанавливаются связи между Рабочими листами?
5. Как происходит копирование формул?
6. Что нужно сделать, чтобы при операции копирования не происходила автоматическая смена адреса?
7. Для чего нужны диаграммы?
8. Как можно изменить размер диаграммы?
9. Как удалить диаграмму?