Электронное пособие по дисциплине информатика п. Школьный, 2010

Вид материала

Документы

Содержание Выпуск продукции = Количество выпущенных изделий Себестоимость выпускаемой продукции = Количество выпущенных изделий Рентабельность продукции = Прибыль от реализации продук­ции/Себестоимость выпускаемой продукции Про­центный формат Подоходный налог = (Оклад - Необлагаемый налогом доход) Всего удержано = Подоходный налог + Отчисления в благотво­рительный фонд Правка/Переместить/Скопировать лист).

Подобный материал:

• Электронное учебное пособие по дисциплине «Пакеты прикладных программ» Пояснительная, 92.77kb.
• Электронное учебно-методическое пособие для студентов специальности 1-24 01 02 Правоведение, 363.56kb.
• О конкурсе на лучшее электронное методическое пособие, электронный учебник, 43.71kb.
• Лекции по дисциплине «Информатика и математика» Тема 12: Организационные меры, аппаратные, 247.81kb.
• Электронное гиперссылочное учебное пособие по дисциплине «Основы теории управления», 57.71kb.
• Методическое пособие по выполнению курсовых работ по дисциплине, 450.1kb.
• Программа конференции включает следующие тематические направления региональная политика, 67.5kb.
• Программа конференции включает следующие тематические направления региональная политика, 67.65kb.
• Решение экономических задач компьютерными средствами > Информатика в экономике: Учебное, 721.96kb.
• Литература Информатика в экономике: Учебное пособие, 756kb.

Тема 2.2. «Создание, заполнение, оформление и редактирование

электронных таблиц»

Цель практического занятия. Закрепить навыки органи­зации расчетов с абсолютной адресацией данных в электронных таблицах MS Excel.


Ячейки в формулах могут иметь относительный и абсолютный адрес. При копировании формул адреса ячеек, имеющие относительный адрес (например, С1) изменяются, адреса ячеек, имеющие абсолютный адрес (например, $С$1) не изменяются. Абсолютный адрес ячейки задается нажатием клавиши F4.



Задание 2.1. Создать таблицу расчета рентабельности продукции. Константы вводить в расчетные формулы в виде абсолютной адресации.

Исходные данные представлены на рис. 2.1.




Рис. 2.1. Исходные данные к заданию 2.1.


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

1. Запустите редактор электронных таблиц Microsoft Excel (при стандартной установке MS Office выполните Пуск/Программы/Microsoft Office/ Microsoft Office Excel 2007.
   
2. Откройте файл «Расчеты». Для этого воспользуемся кнопкой Microsoft Office . Откроется пункт меню «Файл», где мы видим последние документы, или вызываем пункт «Открыть».
   

Рис. 2.2. Исходные данные к заданию 2.1.

3. На новом листе электронной книги «Расчеты» создать таблицу констант (отпускная цена одного изделия, задайте для ячейки денежный формат) и основную расчетную таблицу по заданию.
   
4. Введите исходные данные. При вводе номеров в колонку «А» (числа 1, 2, 3 и т.д.) используйте прием автозаполнения ряда чисел. Для этого наберите два первых числа ряда (числа 1 и 2), выделите их мышкой и подведите курсор к правому нижнему углу выделенных ячеек до изменения вида курсора черный крестик. Прихватите мышью маркер автозаполнения протяните его вниз до нужного значения. Произойдет создание ряда натуральных чисел (арифметическая прогрессия).
   
5. Выделите цветом ячейку со значением константы — отпускной цены 57,00 р.
   

Рекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации, рекомендуете при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул oкрашенная ячейка (т.е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символа $ с клавиатуры или нажатием клавиши [F4]).

6. Произведите расчеты во всех строках таблицы. Формулы для расчета:

Выпуск продукции = Количество выпущенных изделий х Oтпускная цена одного изделия, в ячейку С7 введите формулу = С5 * $Е$2 (ячейка Е2 задана в виде абсолютной адресации);

Себестоимость выпускаемой продукции = Количество выпущенных изделий х Себестоимость одного изделия, в ячейку С8 введите формулу = С5*С6;

Прибыль от реализации продукции = Выпуск продукции — Се­бестоимость выпускаемой продукции, в ячейку С9 введите формулу = С7-С8;

Рентабельность продукции = Прибыль от реализации продук­ции/Себестоимость выпускаемой продукции, в ячейку С10 вве­дите формулу = С9/С8.

На строку расчета рентабельности продукции наложите Про­центный формат чисел. Остальные расчеты производите в Де­нежном формате.

Формулы из колонки «С» скопируйте автокопированием (за маркер автозаполнения) вправо по строке в колонки «D» и «Е».

7. Отформатируйте рассчитанную таблицу.

8. Переименуйте лист электронной книги, присвоив ему имя «Задание 2.1».

9. Выполните текущее сохранение файла (Файл/Сохранить).


Задание 2.2. Создать таблицу расчета дохода сотрудников организации. Константы вводить в расчетные формулы в виде абсолютной адресации.

Исходные данные представлены на рис. 2.2.




Рис. 2.2. Исходные данные к заданию 2.2.


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

1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию.
   
2. Введите значения констант и исходные данные. Форматы данных (денежный или процентный) задайте по образцу задания.
   
3. Произведите расчеты по формулам, применяя к константам абсолютную адресацию.
   

Формулы для расчетов:

Подоходный налог = (Оклад - Необлагаемый налогом доход) х % подоходного налога, в ячейку D10 введите формулу = (С10-$С$3)*$С$4;

Отчисления в благотворительный фонд = Оклад х % отчисле­ния в благотворительный фонд, в ячейку Е10 введите форму­лу = С10*$С$5;

Всего удержано = Подоходный налог + Отчисления в благотво­рительный фонд, в ячейку F10 введите формулу = D9 + E9;

К выдаче = Оклад - Всего удержано, в ячейку G10 введите формулу = C10-F10.


4. Отформатируйте рассчитанную таблицу.

5. Переименуйте лист электронной книги, присвоив ему имя «Задание 2.2».

6. Выполните текущее сохранение файла (Файл/Сохранить).

Дополнительные задания

Задание 2.3. Создать таблицу расчета квартальной и годовой прибыли. Константы вводить в расчетные формулы в виде абсолютной адресации.

Исходные данные представлены на рис. 2.3.



Рис. 2.3. Исходные данные к заданию 2.3.

Формулы для расчетов:

Доход = (Розничная цена)*(Кол-во проданных изделий)

Себестоимость = (Розничная цена)*(Процент себестоимости))*(Кол-во проданных изделий)

Прибыль = Доход - Себестоимость - Расходы

Отформатируйте рассчитанную таблицу.

Переименуйте лист электронной книги, присвоив ему имя «Задание 2.3».

Выполните текущее сохранение файла (Файл/Сохранить).

Задание 2.4. Создать таблицу расчета стоимости квартиры. Константы вводить в расчетные формулы в виде абсолютной адресации.

Исходные данные представлены на рис. 2.4.





Рис. 2.4. Исходные данные к заданию 2.4.

Отформатируйте рассчитанную таблицу.

Переименуйте лист электронной книги, присвоив ему имя «Задание 2.4».

Выполните текущее сохранение файла (Файл/Сохранить).


К содержанию


Тема 2.3. «Функции математические и логические»

Цель занятия: Изучение информационной технологии органи­зации расчетов с использованием встроенных функций в табли­цах MS Excel.

Теоретическая часть

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

Функция. В общем случае — это переменная величина, значе­ние которой зависит от значений других величин (аргументов), функция имеет имя (например, КОРЕНЬ(...), SIN(...)) и, как правило, аргументы, которые записываются в круглых скобках следом за именем функции. Скобки — обязательная принадлеж­ность функции, даже если у нее нет аргументов. Если аргументов несколько, один аргумент отделяется от другого запятой. В каче­стве аргументов функции могут использоваться числа; адреса яче­ек, диапазоны ячеек, арифметические выражения и функции. Смысл и порядок следования аргументов однозначно определен описанием функции, составленным ее автором. Например, если в ячейке G6 записана формула с функцией возведения в степень =СТЕПЕНЬ(А4,2.3), значением этой ячейки будет значение А4, возведенное в степень 2.3.

Работая с функциями, надо помнить следующее:

• функция, записанная в формуле, как правило, возвращает уникальное значение (арифметическое или логическое);

• существуют функции, которые не возвращают числовое или логическое значения, а выполняют некоторые операции (напри­мер, объединяют текстовые строки);

• существуют функции без аргументов (например, функция ПИ() возвращает число π= 3.1415...).

В программе Excel можно использовать свыше 400 функций, которые разделены на категории (тематические группы): матема­тические, финансовые, статистические, текстовые, логические, Даты и времени.

Для упрощения ввода функций в Excel предусмотрен специальный Мастер функций, который можно вызвать нажатием кнопки f_x в строке ввода. Мастер функций имеет два окна – два шага (1 и 2). После вызова Мастера функций появляется окно шага 1 (рис. 3.1).





Рис. 3.1. Мастер функций «Шаг 1»


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

Примечание. В русифицированной программе Excel часть функций обозначается привычными названиями (SIN(...), COS(...), LOG(...) и др.), т.е. латинскими буквами, а часть — словами по-русски (КОРЕНЬ(...), СТЕПЕНЬ(..), ...).

Выбрав в этом списке функцию (на рис. 3.1 показан квадратный корень), надо нажать кнопку ОК и на экране появится вто­рое диалоговое окно (рис. 3.2).




Рис. 3.2. Мастер функций «Шаг 2»


В этом окне вы можете выбрать аргумент (аргументы) функции. В правом верхнем углу отображается значение функции при на­бранных аргументах.

Основные математические функции Excel.

Ранее мы уже рассмотрели функцию суммирования СУММ.

Широко используются также следующие функции.

СРЗНАЧ(<список аргументов>) — возвращает среднее арифметическое из значений всех аргументов. Например, СРЗНАЧ(С1:С7) возвращает среднее значение чисел, записанных в ячейках С1, С2, … , С7.

МАКС(<список аргументов?-) — возвращает максимальное число из списка аргументов. Например, МАКС(С1:С7) возвраща­ет максимальное из чисел, записанных в ячейках С1, С2, ..., С7.

МИН(<список аргументов?-) — возвращает минимальное чис­ло из списка аргументов.

Перечислим наиболее распространенные математические функ­ции Excel.

ПРОИЗВЕД(<список аргументов>) — возвращает произведе­ние значений аргументов.

КОРЕНЬ(х) — возвращает корень квадратный из числа х.

СЛЧИС() — возвращает случайное число в интервале от 0 до 1.

ABS(x) — возвращает модуль значения аргумента.

LN(x) — возвращает натуральный логарифм числа х (по осно­ванию е = 2,71828...).

ЕХР(х) — возвращает экспоненту числа х (ех).

SIN(х) — возвращает синус числа х, заданного в радианах.

COS(x) — возвращает косинус числа х, заданного в радианах.

TAN(х) — возвращает тангенс числа х, заданного в радианах.

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

Логические функции. При решении некоторых задач значение ячейки необходимо вычислять одним из нескольких способов в зависимости от выполнения или невыполнения одного или не­скольких условий.

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

Для решения таких задач применяют условную функцию ЕСЛИ. Эта функция имеет формат:

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

Первый аргумент функции ЕСЛИ — логическое выражение (в частном случае, условное выражение), которое принимает одно из двух значений: «Истина» или «Ложь» (1 или 0). Если логическое выражение принимает значение «Истина», то функция принимает значение выражения 1, в противном случае – значение выражения 2.


Практическая часть

Задание 3.1. Создать таблицу динамики розничных цен и произвести расчет средних значений.

Исходные данные представлены на рис. 3.3.




Рис. 3.3. Исходные данные для задания 3.1.

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

1. Запустите редактор электронных таблиц Microsoft Excel (при стандартной установке MS Office выполните Пуск/Программы/Microsoft Office/ Microsoft Office Excel 2007.
   
2. Откройте файл «Расчеты». Через кнопку .
   
3. Переименуйте ярлычок Лист №… , присвоив ему имя Задание 3.1.
   
4. На листе «Задание 3.1» создайте таблицу по образцу, как на рис. 3.3.
   
5. Произведите расчет изменения цены в колонке «Е» по формуле
   

Изменение цены = Цена на 01. 06. 2005/Цена на 01. 04.2005.

Не забудьте задать процентный формат чисел в колонке «Е» (Шрифт/Формат ячеек/Число/Процентный).

6. Рассчитайте средние значения по колонкам, пользуясь мастером функций f_x. Функция СРЗНАЧ находится в разделе «Статистические». Для расчета функции среднего значения уста­новите курсор в соответствующей ячейке для расчета среднего значения (В14), запустите мастер функций (кнопкой Вставка функции f_x или командой Вставка/Функция) и на первом шаге мастера выберите функцию СРЗНАЧ категория Статистичес­кие/СРЗНАЧ) (рис. 3.4).




Рис. 3.4. Выбор функции расчета среднего значения СРЗНАЧ

После нажатия на кнопку ОК откроется окно для выбора диапазона данных для вычисления заданной функции. В качестве первого числа выделите группу ячеек с данными для расчета среднего значения В6:В13 и нажмите кнопку ОК (рис. 3.5). В ячейке В14 появится среднее значение данных колонки «В».

Аналогично рассчитайте средние значения в других колонках.




Рис. 3.5. Выбор диапазона данных для расчета среднего значения СРЗНАЧ

7. В ячейке А2 задайте функцию СЕГОДНЯ, отображающую текущую дату, установленную в компьютере (Вставка/Функция/Дата и Время/Сегодня).

8. Переименуйте Лист, выполните текущее сохранение файла (Файл/Сохранить).


Задание 3.2. Скопировать таблицу подсчета котировок курса дол­лара (Лист «Курс доллара») и произвести под таблицей расчет средних значений, максимального и минимального значений курсов покупки и продажи доллара. Расчет произвести с использованием мастера функций.

С
копируйте содержимое листа «Курс доллара» на новый лист ( Правка/Переместить/Скопировать лист). Можно воспользоваться командой Переместить/Скопировать лист контекстного меню ярлычка. Не забудьте для копирования поставить галочку в окошке Создавать копию (рис. 3.6). Перемещать и копировать листы можно перетаскивая их ярлычки (для копирования удерживаем [CTRL]).


Рис. 3.6. Копирование листа электронной книги

Краткая справка. Для расчета максимального/минимального значения установите курсор в ячейке расчета, выберите встроенную функцию Excel МАКС (МИН) из категории «Статистические», в качестве первого числа выделите диапазон ячеек значений столбца В4:В23).

Переименуйте Лист, присвоив ему имя «Задание 3.2», выполните текущее сохранение.

Задание 3.3. Применение функции ЕСЛИ при проверке усло­вий. Создать таблицу расчета премии за экономию горючесмазочных материалов (ГСМ).

Исходные данные представлены на рис. 3.7.


Р
ис. 3.7. Исходные данные для задания 3.3.

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

1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию.
   
2. Произвести расчет Премии (25 % от базовой ставки) по формуле
   

Премия = Базовая ставка х 0,25 при условии, что

План расходования ГСМ > Фактически израсходовано ГСМ.

Для проверки условия используйте функцию ЕСЛИ.

Для расчета Премии установите курсор в ячейке F4, запус­тите мастер функций (кнопкой Вставка функции f_x или коман­дой Вставка/Функция) и выберите функцию ЕСЛИ (категория — Логические/ ЕСЛИ).

Задайте условие и параметры функции ЕСЛИ (рис. 3.8).




Рис. 3.8. Задание параметров функции ЕСЛИ

В первой строке «Логическое выражение» задайте условие С4 > D4.

Во второй строке задайте формулу расчета премии, если ус­ловие выполняется Е4*0,25.

В третьей строке задайте значение 0, поскольку в этом слу­чае (невыполнение условия) премия не начисляется.

3. Произведите сортировку по столбцу фактического расходо­вания ГСМ по возрастанию. Для сортировки установите курсор на любую ячейку таблицы, выберите в меню Данные команду Сортировка, задайте сортировку по столбцу «Фактическое расходо­вание ГСМ».

4. Выполните переименование листа по номеру задания и текущее сохранение файла.


К содержанию


Тема 2.4 «Проведение расчетов и поиска информации в электронной таблице с использованием формул»

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

Задание 20.1. В таблице «Доход сотрудников» выполнить сор­тировку и фильтрацию данных.

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

1. Запустите редактор электронных таблиц Microsoft Excel. Откройте файл «Расчеты».
   
2. Скопируйте таблицу с листа «Задание 2.2».
   

3. П
   роизведите сортировку по фамилиям сотрудников в алфа­витном порядке по возрастанию (выделите блок ячеек B10:G17 без итогов, выберите в меню Данные команду Сортировка, сортировать по Ф.И.О.) (рис. 4.1).
   

Рис. 4.1. Задание условия сортировки данных

3. Добавьте расчет средней, максимальной и минимальной суммы к выдаче.
   
4. Произведите фильтрацию значений дохода, превышающих 1600 р.
   

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

Д
ля установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командой Данные/Фильтр/Автофильтр. В заголовках полей появятся стрелки выпадающих списков. Щелк­ните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «К выдаче»), и вы увидите список всех не­повторяющихся значений этого поля. Выберите команду для фильтрации Условие. В открывшемся окне Пользовательский ав­тофильтр задайте условие — больше 1600 (рис. 4.2).


Рис. 4.2. Выбор условия фильтрации

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

Проследите, как изменился вид таблицы.

Конечный вид таблицы после сортировки и фильтрации представлен на рис. 4.3.

3. П
   ереименуйте лист (Задание 4.1). Выполните текущее сохранение файла (Файл/Сохранить).
   

Рис. 4.3. Конечный вид таблицы после сортировки и фильтрации


Задание 4.2. В таблице «Средняя годовая температура возду­ха» выполнить условное форматирование и ввод данных.

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

1. Н
   а очередном свободном листе электронной книги «Расче­ты» создайте таблицу по заданию (рис. 4.4).
   

Рис. 4.4. Исходные данные для задания 4.2.

2. После ввода названия таблицы, отформатируйте его и расположите по центру блока В1:М1.
   
3. При наборе месяцев используйте автокопирование, не за­будьте повернуть данные на 90°.
   
4. Используйте автоподбор ширины ячеек, предварительно выделив ячейки (Формат/Столбец/Автоподбор ширины).
   
5. Проведите условное форматирование значений температур в ячейках В4:М9 (Формат/Условное форматирование) (рис. 4.5).
   

Установите формат данных:

меньше 0 — синим цветом шрифта (полужирный),

равное 0 — зеленый фон, цвет шрифта — белый;

больше 0 — красным цветом шрифта (полужирный).

Примечание. Условное форматирование можно задавать как до набора данных, так и после.

5. Переименуйте лист (Задание 4.2). Выполните текущее сохранение файла (Файл/Сохранить).





Рис. 4.5. Условное форматирование данных


Дополнительные задания

Задание 4.3. Скопировать таблицу расчета суммарной выручки (задание 1.2, лист «Выручка») и определить фильтрацией, в ка­кие дни выручка по первому подразделению не превысила 3000 р.


Скопируйте содержимое листа «Выручка» на новый лист (Правка/Переместить— Скопировать лист). Не забудьте для копирования поставить галочку в окошке Создать копию. Перемещать и копировать листы можно перетаскивая их ярлычки (для копирования удерживаем [CTRL]).

Задайте фильтрацию командой Данные/Фильтр/Автофильтр. И условие выбор для первого подразделения - меньше или равно 3000р.

Переименуйте лист (Задание 4.3). Выполните текущее сохранение файла (Файл/Сохранить).


Задание 4.4. Скопировать таблицу котировки курса доллара (задание 1.1, лист «Курс доллара») и провести условное форматирование значений курсов покупки и продажи доллара.


Установите формат данных:

меньше 31,5 - зеленый цвет шрифта, полужирный;

больше или равно 31,5 - оранжевый цвет шрифта, полужирный курсив.

Переименуйте лист (Задание 4.4). Выполните текущее сохранение файла (Файл/Сохранить).

Задание 4.5. Скопировать таблицу задания 1.3 и рассчитать по каждому заказу максимальное, минимальное и среднее значение.

Установите формат данных:

меньше 1600 - красный цвет шрифта, полужирный;

больше или равно 1600 - зеленый цвет шрифта, полужирный курсив.

Переименуйте лист (Задание 4.5). Выполните текущее сохранение файла (Файл/Сохранить).

К содержанию