Задание 1.
Математические:
Статистические:
1. Спланируйте расходы на бензин для ежедневных поездок из п. Половинка в г. Урай на автомобиле. Если известно:
- расстояние м/д населёнными пунктами в км. (30 км. в одну сторону)
- расход бензина (8 литров на 100 км.)
- количество поездок в месяц разное (т.к. разное количество рабочих дней.)
- цена 1 литра бензина (n рублей за литр.)
- ежемесячный прогнозируемый рост цены на бензин - k% в месяц
Рассчитайте ежемесячный и годовой расход на бензин. Постройте график изменения цены бензина и график ежемесячных расходов.
2. Представьте, что вы директор ресторана. Общий месячный фонд заработной платы - 10000$. На совете акционеров было установлено, что:
- официант получает в 1,5 раза больше мойщика посуды;
- повар - в 3 раза больше мойщика посуды;
- шеф-повар - на 30$ больше...
1. Рассчитайте еженедельную выручку зоопарка, если известно:
- количество проданных билетов каждый день
- цена взрослого билета - 15 руб.
- цена детского на 30% дешевле чем взрослого. Постройте диаграмму (график) ежедневной выручки зоопарка.
2. Подготовьте бланк заказа для магазина, если известно:
- продукты(хлеб, мука, макаронные изделия и т.д., не менее 10 наименований)
- цена каждого продукта
- количество заказанного каждого продукта
Рассчитайте на какую сумму заказано продуктов. Усовершенствуйте бланк заказа, добавив скидку (например 10%), если стоимость купленных продуктов будет более 5000 руб. Постройте диаграмму (гистограмму) стоимости...
1. Найти решение уравнения вида kx + b = 0, где k, b - произвольные постоянные.
2. Сахарный тростник содержит 9% сахара. Сколько сахара будет получено из 20 тонн сахарного тростника?
3. Школьники должны были посадить 200 деревьев. Они перевыполнили план посадки на 23%. Сколько деревьев они посадили?
4. Из 50 кг. семян, собранных учениками, 17% составили семена клена, 15% - семена липы, 25% - семена акации, а стальное - семена дуба. Сколько килограмм...
Задание 1.
В Excel рассчитать общую стоимость техники .
1. Ввод данных.
1.1. Переименуйте Лист 1 . Для этого щелкните на ярлыке листа правой кнопкой, выберите из контекстного меню команду Переименовать и введите новое имя «Задание 1» .
1.2. Начиная с ячейки А1, введите последовательно в электронную таблицу данные , указанные на рис.10.
Рис. 1.10. Начальный вид таблицы
1.3. Отрегулируйте ширину столбцов.
Это можно выполнить автоматически,сделав двойной щелчок мыши на границе столбцов (курсор при этом превратится в двустороннюю стрелочку) или вручную, установив курсор на границе между столбцами и растащив столбец до нужной ширины. Перенос по словам можно сделать при помощи панели Формат ячеек , выбрав его из контекстного меню, поставить в закладке Выравнивание галочку на пункте Переносить по словам .
Рис. 1.11. Окно форматирования ячеек
1.4. В ячейку А2 введите первый вид техники -Плуг.
В другие ячейки, начиная с А3 по А11 введите другие виды сельхозтехники:
Культиватор;
Борона;
Лущильник;
Сеялка;
Опрыскиватель;
Каток;
Фреза;
Разбрасыватель минеральных удобрений ;
Косилка.
Рис. 1.12. Промежуточный вид таблицы
2. Создание формул.
2.1. Внесите в таблицу количество сельхозтехники и цены в долларах ($) в соответствии с рисунком, а также добавьте дополнительные строчки в указанных на рисунке ниже.
2.2. Рассчитайте общую стоимость закупки (в $), используя метод ввода формул вручную:
– установите курсор в ячейке D2;
– введите знак равенства (=), а затем вручную напечатайте формулу:
В2*С2, обратите внимание, что все действия повторяются выше в строке формул.
2.3. Рассмотрим более рациональный способ ввода формул, которым рекомендуем пользоваться в дальнейшем – метод ввода формул путем указания ячеек.
Рассчитайте общую стоимость закупки (в $). Для этого:
– установите курсор в ячейке D3;
– щелкните в строке формул и введите знак равенства (=);
– щелкните по ячейке В3. Убедитесь, что вокруг ячейки В3 появилась активная рамка, а в строке формул отобразился адрес ячейки В3
Рис. 1.13. Ввод формулы путем указания ячеек
– продолжите ввод формулы, напечатав с клавиатуры знак умножения (*);
– щелкните по ячейке С3, убедитесь, что ее адрес также отразился в строке формул.
– для завершения ввода формулы нажмите клавишу
3. Адресация ячеек.
Для автоматизации однотипных вычислений в электронных таблицах используется механизм копирования и перемещения формул, при котором происходит автоматическая настройка ссылок на ячейки с исходными данными.
3.1. Просчитайте общую стоимость закупки (в $) для оставшихся видов сельхозтехники, используя маркер автозаполнения. Для этого:
– щелкните по ячейке D3;
– установите курсор на маркер автозаполнения;
– нажмите левую кнопку мыши и, не отжимая, протащите формулу вниз до конца списка и отпустите левую кнопку;
– убедитесь, что в каждой строке программа изменила ссылки на ячейки в соответствии с новым положением формулы (в выбранной на рис. 8 ячейке D11 формула выглядит =В11*С11) и что все ячейки заполнились соответствующими числовыми значениями.
Рис. 1.14. Промежуточный вид таблицы
Просчитайте цену сельхозтехники в рублях, используя указанный в таблице курс доллара по отношению к рублю для чего:
– установите курсор в ячейке Е2;
– введите формулу =С2*В27;
– убедитесь, что получилось числовое значение 78260;
– попробуйте распространить формулу вниз на весь список с помощью маркера автозаполнения. Убедитесь, что везде получились нули! Это произошло потому, что при копировании формулы относительная ссылка на курс доллара в ячейке В27 автоматически изменилась на В28, В29 и т.д. А поскольку эти ячейки пустые, то при умножении на них получается 0. Таким образом, исходную формулу перевода цены из долларов в рубли следует изменить так, чтобы ссылка на ячейку В27 при копировании не менялась.
Для этого существует абсолютная ссылка на ячейку, которая при копировании и переносе не изменяется.
Пересчитайте столбец Е:
– удалите все содержимое диапазона ячеек Е2:Е11, введите в ячейку Е2 формулу = С2*$В$27;
– с помощью маркера автозаполнения распространите формулу вниз на весь список. Просмотрите формулы и убедитесь, что относительные ссылки изменились, но абсолютная ссылка на ячейку В27 осталась прежней. Убедитесь, что цена рассчитывается правильно.
3.3. Зная цену вида сельскохозяйственной техники в рублях и ее количество, самостоятельно рассчитайте последний столбец: общую сумму закупки в рублях.
4. Использование функций.
Функции вводятся обычным набором с клавиатуры или более предпочтительным способом – с помощью Мастера функций . Рассмотрим оба эти метода на примерах.
4.1. Рассчитайте итог по столбцу «Количество», используя функцию СУММ (функция нахождения суммы), методом ввода функций вручную.
Метод ввода функций вручную заключается в том, что нужно ввести вручную с клавиатуры имя функции и список ее аргументов. Иногда этот метод оказывается самым эффективным. При вводе функций обратите внимание, что функции поименованы на английском языке.
Для расчета итога по столбцу «Количество»:
– установите курсор в ячейку В13;
– напечатайте с клавиатуры формулу =СУММ(B2:B11);
– нажмите клавишу
4.2. Рассчитайте итог по столбцу «Цена, $», используя средство Мастер функций .
Для ввода функции и ее аргументов в полуавтоматическом режиме предназначено средство Мастер функций ( fx ), которое обеспечивает правильное написание функции, соблюдение необходимого количества аргументов и их правильную последовательность.
Для его открытия используются:
– Вкладка Формулы , где указана библиотека функций;
– кнопка Мастер функций на панели формул (рис. 1.15).
Рис. 1.15. Кнопка «Мастер функций» на панели формул
Для расчета итога по столбцу «Цена, $»:
– установите курсор в ячейке С13;
– вызовите диалоговое окно Мастер функций одним из указанных выше способов;
– в поле Функция найдите СУММ;
– в поле Число 1 можно ввести сразу весь диапазон суммирования С2:С11 (диапазон можно ввести с клавиатуры, а можно выделить на листе левой кнопкой мыши, и тогда он отобразится в формуле автоматически) (рис. 1.16);
Рис. 1.16. Расчет суммы через Мастер функций
– обратите внимание на кнопку сворачивания диалогового окна, расположенную в правой части поля Число 1 . Это приведет к временному сворачиванию окна, в результате чего будет виден весь рабочий лист;
– щелкните по кнопке ОК, убедитесь, что в ячейке С13 появилось числовое значение 11185.
4.3. Аналогичным образом рассчитайте итог по оставшимся столбцам.
4.4. Рассчитайте дополнительные параметры, указанные в таблице (средние цены, минимальные и максимальные). Данные функции находятся в категории Статистический . Для этого в указанных ячейках используйте соответствующие функции.
Таблица 1.2
Адреса ячеек и соответствующие им расчетные функции
Адрес ячейки |
Формула |
Действие |
С15 |
СРЗНАЧ(C2:C11) |
Вычисление среднего значения из указанного диапазона |
Е17 |
СРЗНАЧ(E2:E11) |
|
С19 |
M ИН(C2:C11) |
Нахождение минимального значения из указанного диапазона |
Е21 |
M ИН(E2:E11) |
|
С23 |
MA КС(C2:C11) |
Нахождение максимального значения из указанного диапазона |
Е25 |
MA КС(E2:E11) |
5. Форматирование данных.
Числовые значения, которые вводятся в ячейки, как правило, никак не отформатированы. Другими словами, они состоят из последовательности цифр. Лучше всего форматировать числа, чтобы они легко читались и были согласованными в смысле количества десятичных разрядов.
Если переместить курсор в ячейку с отформатированным числовым значением, то в строке формул будет отображено числовое значение в неформатированном виде. При работе с ячейкой всегда обращайте внимание на строку формул! Некоторые операции форматирования Excel выполняет автоматически.
Например, если ввести в ячейку значение 10%, то программа будет знать, что вы хотите использовать процентный формат, и применит его автоматически. Аналогично если вы используете пробел для отделения в числах тысяч от сотен (например, 123 456), Excel применит форматирование с этим разделителем автоматически. Если вы ставите после числового значения знак денежной единицы, установленный по умолчанию, например «руб.», то к данной ячейке будет применен денежный формат.
Для установки форматов ячеек предназначено диалоговое окно Формат ячеек.
Существует несколько способов вызова окна Формат ячеек. Прежде всего, необходимо выделить ячейки, которые должны быть отформатированы, а затем выбрать команду Формат / Ячейки или щелкнуть правой кнопкой мыши по выделенным ячейкам и из контекстного меню выбрать команду Формат ячеек.
Далее на вкладке Число диалогового окна Формат ячеек из представленных категорий можно выбрать нужный формат. При выборе соответствующей категории из списка правая сторона панели изменяется так, чтобы отобразить соответствующие опции.
Кроме этого диалоговое окно Формат ячеек содержит несколько вкладок, предоставляющих пользователю различные возможности для форматирования: Шрифт, Эффекты шрифта, Выравнивание, Обрамление, Фон, Защита ячейки.
5.1. Измените формат диапазона ячеек С2:С13 на Денежный:
– выделите диапазон ячеек С2:С13;
– щелкните внутри диапазона правой кнопкой мыши;
– выберите команду Формат / Ячейки;
– на вкладке Число выберите категорию Денежный;
– в перечне Формат выберите USD $ Английский (США);
– параметр Дробная часть укажите равным 0;
– нажмите кнопку ОК (рис. 1.17).
Рис. 1.17. Установка «Денежного» формата ячеек
5.2. Аналогичным образом измените формат для столбцов «Общая стоимость закупки, $», «Цена, руб.», «Общая стоимость закупки, руб.». Также измените формат для ячеек итога со средней, минимальной и максимальной ценами. Для рублевых данных используйте формат RUB руб. русский и дробную часть укажите равной 1.
Обратите внимание, что если в ячейке после смены формата вместо числа показывается ряд символов (решетка ##########), то это значит, что столбец недостаточно широк для отображения числа в выбранном формате, а значит необходимо увеличить ширину столбца.
6. Оформление таблиц.
К элементам рабочей таблицы можно применить также методы стилистического форматирования, которое осуществляется с помощью закладки Главная. Полный набор опций форматирования содержится в диалоговом окне Формат ячеек. Важно помнить, что атрибуты форматирования применяются только к выделенным ячейкам или группе ячеек. Поэтому перед форматированием нужно выделить ячейку или диапазон ячеек.
6.1. Добавьте заголовок к таблице:
– щелкните правой кнопкой мыши по цифре 1 у первой строки;
– выберите команду Вставить строки;
– выделите диапазон ячеек А1:F1 и выполните команду
– введите в объединенные ячейки название «Отчет по закупке сельскохозяйственного оборудования»;
– щелкнув правой кнопкой мыши, вызовите контекстное меню Формат ячейки. В открывшемся окне, в закладке шрифт установите следующие параметры: Шрифт – Calibi , начертание – полужирный курсив, кегль – 14; Эффекты шрифта – цвет синий; Выравнивание – по центру; Обрамление – положение линий со всех сторон, стиль – сплошная линия 2,5 пт., цвет – зеленый; Фон – желтый 2;
– нажмите кнопку ОК.
6.2. Отформатируйте содержимое таблицы:
– примените полужирное начертание к данным в диапазонах ячеек А2:F2, А3:А28;
– установите Фон и Обрамление для диапазонов ячеек: А14:F14; А16:С16; А18:Е18; А20:С20; А22:Е22; А24:С24; А26:Е26;
– выделите курс доллара полужирным начертанием и красным цветом;
– диапазон ячеек А2:F12 оформите Обрамлением: внешняя рамка и линии внутри.
6.3. Отрегулируйте ширину столбцов, если в процессе форматирования данные в ячейках увеличились и не умещаются в границы ячейки (рис. 1.18).
Рис. 1.18. Конечный вид таблицы
6.4. Установите горизонтальную ориентацию листа: Главная / Печать / Ориентация альбомная.
6.5. Сохраните электронную таблицу в личной папке под именем «Работа 1».
Занятие 1. НАЗНАЧЕНИЕ ПРОГРАММЫ.
ВИД ЭКРАНА. ВВОД ДАННЫХ В ТАБЛИЦУ
Программа Microsoft Excel относится к классу программ, называемых электронными таблицами . Электронные таблицы ориентированы прежде всего на решение экономических и инженерных задач, позволяют систематизировать данные из любой сферы деятельности. Существуют следующие версии данной программы – Microsoft Excel 4.0, 5.0, 7.0, 97, 2000.
Программа Microsoft Excel позволяет:
Практическое задание 1
Задание: создать таблицу, занести в нее следующие данные:
Порядок работы
Занятие 2. ФОРМАТИРОВАНИЕ ТАБЛИЦЫ
Выделение фрагментов таблицы
Чтобы выполнить какое-либо действие с группой ячеек, их необходимо сначала выделить. При этом фон всех ячеек, кроме первой, будет закрашен черным цветом. Но не закрашенная ячейка тоже будет выделена.
Изменение размеров ячеек
В реальных таблицах, как правило, все столбцы имеют различную ширину. Существует несколько способов изменения размеров ячейки.
Если необходимо изменить размеры сразу нескольких ячеек, их необходимо сначала выделить.
Форматирование содержимого ячеек
Команда Формат – Ячейка предназначена для выполнения основных действий с ячейками. Действие будет выполнено с активной ячейкой или с группой выделенных ячеек. Команда содержит следующие подрежимы:
ЧИСЛО – позволяет явно определить тип данных в ячейке и форму представления этого типа. Например, для числового или денежного формата можно определить количество знаков после запятой.
ВЫРАВНИВАНИЕ – определяет способ расположения данных относительно границ ячейки. Если включен режим “ПЕРЕНОСИТЬ ПО СЛОВАМ”, то текст в ячейке разбивается на несколько строк. Режим позволяет расположить текст в ячейке вертикально или даже под выбранным углом.
ШРИФТ – определяет параметры шрифта в ячейке (наименование, размер, стиль написания).
ГРАНИЦА – обрамляет выделенные ячейки, при этом можно определить толщину линии, ее цвет и местоположение.
ВИД – закрашивает фон ячеек с помощью выделенного цвета или узора.
ЗАЩИТА – устанавливается защита на внесение изменений.
Команда применяется к выделенной или активной в настоящий момент ячейке.
Практическое задание 2
Создайте таблицу следующего вида на первом рабочем листе.
При создании таблицы примените следующие установки:
Сохраните готовую таблицу в папке Users в файле ископаемые.xls .
Заголовок таблицы
Для расположения заголовка по центру относительно границ таблицы существует специальная пиктограмма, которая называется “Объединить и поместить в центре”. Предварительно выделяется группа ячеек над таблицей, при нажатии на данную пиктограмму они объединяются в одну и набираемый в ней текст центрируется.
Практическое задание 2.1
Над созданной таблицей наберите заголовок “Полезные ископаемые” 14 размером, полужирным курсивом.
Занятие 3. РАСЧЕТ ПО ФОРМУЛАМ
Правила работы с формулами
Если необходимо рассчитать данные в столбце по однотипной формуле, в которой меняются только адреса ячеек при переходе на следующую строку таблицы, то такую формулу можно скопировать или размножить на все ячейки данного столбца.
Например:
Расчет суммы в последнем столбце происходит путем перемножения данных из столбца “Цена одного экземпляра” и данных из столбца “Количество”, формула при переходе на следующую строку в таблице не изменяется, изменяются только адреса ячеек.
Копирование содержимого ячеек
Выделяем исходную ячейку, помещаем указатель мыши на край рамки и при нажатой клавише и левой клавише мыши перемещаем рамочку в новое место. При этом копируется содержимое ячейки, в том числе и формула.
Автозаполнение ячеек
Выделяем исходную ячейку, в нижнем правом углу находится маркер заполнения, помещаем курсор мыши на него, он примет вид + ; при нажатой левой клавише растягиваем границу рамки на группу ячеек. При этом все выделенные ячейки заполняются содержимым первой ячейки. При этом при копировании и автозаполнении соответствующим образом изменяются адреса ячеек в формулах. Например, формула = А1 + В1 изменится на = А2 + В2.
Например: = $A$5 * A6
При копировании этой формулы в следующую строку ссылка на первую ячейку останется неизменной, а второй адрес в формуле изменится.
Расчет итоговых сумм по столбцам
В таблицах часто необходимо подсчитать итоговые суммы по столбцу. Для этого существует специальная пиктограмма Автосуммирование . Предварительно ячейки с исходными данными нужно выделить, для этого нажимаем пиктограмму, сумма будет расположена в свободной ячейке под столбцом.
Практическое задание 3
Создайте таблицу следующего вида:
Под таблицей рассчитайте по формуле среднюю длину рек.
Занятие 4. ПРЕДСТАВЛЕНИЕ ДАННЫХ ИЗ ТАБЛИЦЫ В ГРАФИЧЕСКОМ ВИДЕ
Программа Microsoft Excel предоставляет пользователю широкие возможности для визуализации числовых данных из таблиц. Двумерное изображение при этом называется диаграммой , объемное – гистограммой . Числовые ряды можно представить в виде графиков . Не важно, какую форму представления данных вы выберите, порядок действий будет один и тот же. При этом будет работать программа, которая называется Мастером диаграмм . Пользователю только необходимо в окне диалога определить параметры изображения.
Порядок построения диаграммы:
1. Выделяем фрагменты таблицы, на основе которых будет построена диаграмма. Ячейки, содержащие наименования столбцов, тоже выделяются, они будут использоваться как подписи на диаграмме. Если необходимо выделить несмежные фрагменты таблицы, то второй фрагмент выделяется при нажатой клавише.
2. Выбираем команду Вставка – Диаграмма или нажимаем соответствующую пиктограмму на панели инструментов. На экране появится первое из окон диалога Мастера диаграмм.
3. В каждом окне выбираем один из предлагаемых вариантов щелчком мыши. Для переключения между подрежимами можно использовать вкладки в верхней части окон. Для перехода к следующему окну нажимаем кнопку “Далее”, кнопка “Назад” позволяет вернуться к предыдущему шагу. Кнопка “Готово” позволит закончить процесс построения диаграммы.
1 окно: Определяем тип диаграммы. При этом выбираем его в стандартных или нестандартных диаграммах. Это окно представлено на рис. 4.
2 окно: Будет представлена диаграмма выбранного вами типа, построенная на основании выделенных данных. Если диаграмма не получилась, то проверьте правильность выделения исходных данных в таблице или выберите другой тип диаграммы.
3 окно: Можно определить заголовок диаграммы, подписи к данным, наличие и местоположение легенды (легенда – это пояснения к диаграмме: какой цвет соответствует какому типу данных).
4 окно: Определяет местоположение диаграммы. Ее можно расположит на том же листе, что и таблицу с исходными данными, и на отдельном листе.
Рис. 4. Первое окно Мастера диаграмм для определения типа диаграммы
Для примера построим диаграмму на основе следующей таблицы.
Озера
Наименование | Наибольшая глубина, м |
|
Каспийское море | 1025 |
|
Женевское озеро | ||
Ладожское озеро | ||
Онежское озеро | ||
Байкал | 1620 |
Диаграмма будет построена на основе столбцов “Наименование” и “Наибольшая глубина”. Эти столбцы необходимо выделить.
Нажимаем пиктограмму и изображением диаграммы. В первом окне выбираем тип диаграммы – круговая. Во втором окне будет представлен результат построения диаграммы, переходим к следующему окну. В третьем окне определим название – “Глубины озер”. Возле каждого сектора установим значение глубины. Расположим легенду внизу под диаграммой. Далее представлен результата нашей работы:
Изменение параметров форматирования уже построенной диаграммы.
Если необходимо изменить форму представления любой составной части диаграммы. Щелчком мыши выделяем фрагмент диаграммы, вокруг него появится рамка с маркерами изменения размеров, при этом фрагмент диаграммы считается выделенным и мы его правим. Далее нажмите правую кнопку мыши для вызова контекстного меню, которое будет содержать перечень действий. Для изменения параметров форматирования выберите режим Формат подписи данных и измените параметры со значений на доли.
Рис. 5. Контекстное меню для форматирования построенной диаграммы
Действия с диаграммой
С диаграммой, как и со вставленным рисунком, можно выполнить следующие действия.
1. Для изменения размеров всей диаграммы целиком или любого ее фрагмента необходимо сначала выделить нужный участок. Вокруг него появится рамка с маркерами изменения размера. Перемещая эти маркеры при нажатой клавише мыши, изменяем размеры.
2. Чтобы переместить диаграмму по рабочему листу, сначала выделяем ее, помещаем указатель мыши в любое место диаграммы и при нажатой левой клавише перемещаем диаграмму в новое место.
3. Для удаления диаграммы сначала выделяем ее, затем нажимаем клавишу Del или выбираем команду “Удалить” в контекстном меню диаграммы.
Занятие 5. РАБОТА С ФУНКЦИЯМИ
Программа содержит более 150 встроенных функций для обработки данных. Для удобства поиска все функции разбиты на категории, внутри каждой категории они отсортированы в алфавитном порядке. Кроме этого есть две категории – “10 недавно использовавшихся” и “Полный алфавитный перечень”, в котором все встроенные функции располагаются в алфавитном порядке.
Для вставки функции в формулу можно воспользоваться мастером функций, при этом функции могут быть вложенными друг в друга, но не более 8 раз. Главными задачами при использовании функции являются определение самой функции и аргумента. Как правило, аргументом являются адреса ячеек. Если необходимо указать диапазон ячеек, то первый и последний адреса разделяются двоеточием, например А12:С20.
Порядок работы с функциями
Рассмотрим работу с функциями на примере логических функций. При решении некоторых задач значение ячейки необходимо вычислять одним из нескольких способов – в зависимости от выполнения или невыполнения одного или нескольких условий.
Для решения таких задач применяют условную функцию ЕСЛИ:
ЕСЛИ(,).
Если логическое выражение имеет значение “Истина” (1), ЕСЛИ принимает значение выражения 1, а если “Ложь” – значение выражения 2. В качестве выражения 1 или выражения 2 можно записать вложенную функцию ЕСЛИ. Число вложенных функций ЕСЛИ не должно превышать семи. Например, если в какой-либо ячейке будет записана функция ЕСЛИ(C5=1,D5*E5,D5-E5)), то при С5=1 функция будет иметь значение “Истина” и текущая ячейка примет значение D5*E5, если С5=1 будет иметь значение “Ложь”, то значением функции будет D5-E5.
Рис. 6. Мастера функций
Если условий много, записывать вложенные функции ЕСЛИ становится неудобно. В этом случае на месте логического выражения можно указать одну из двух логических функций: И (и) или ИЛИ (или).
Формат функций одинаков:
И(,..),
ИЛИ(,..).
Функция И принимает значение “Истина”, если одновременно истинны все логические выражения, указанные в качестве аргументов этой функции. В остальных случаях Значение И – “Ложь”. В скобках можно указать до 30 логических выражений.
Функция ИЛИ принимает значение “Истина”, если истинно хотя бы одно из логических выражений, указанных в качестве аргументов этой функции. В остальных случаях значение ИЛИ – “Ложь”.
Давайте рассмотрим, как работают логические функции, на примере.
Создадим таблицу с заголовком “Результаты вычисления”:
Значение последнего столбца может меняться в завистимости от значения набранного бала. Пусть при набранном балле 21 абитуриент считается зачисленным, при меньшем значении – нет. Тогда формула для занесения в последний столбец выглядит следующим образом:
ЕСЛИ (С2
Практическое задание 5
Создать таблицу для расчета заработной платы:
Первые три столбца начисляются в свободной форме, налог рассчитывается в зависимости от суммы во втором столбце. Налог начислить по следующему правилу: если сумма начислений с начала года у сотрудника меньше 20000 руб., то берется 12% от налогооблагаемой суммы. Если сумма начислений с начала года больше 20000 руб., то берется 20% от налогооблагаемой суммы. Для ввода формулы начисления налога использовать Мастер функций.
Занятие 6. РАБОТА С ШАБЛОНАМИ
Для минимизации действий при создании стандартных документов удобно воспользоваться готовыми шаблонами. Чтобы воспользоваться ими, необходимо вызвать команду Файл – Создать ; в появившемся диалоговом окне выбрать вкладку Решения и определить нужный документ. Заполнить поля документа. Сохранить созданный документ, используя команду Файл – Сохранить как .
Пользователь имеет возможность создать бланк любого документа, сохранив его в качестве шаблона. При этом создается заготовка для таблицы без исходных данных. Можно заранее определить формат данных для пустых ячеек. Ячейки сначала выделяются, а затем командой Формат – Ячейка определяются параметры форматирования.
Рассмотрим работу с шаблоном на примере.
Данные с каждой геологической станции заносятся в однотипную таблицу. Имеет смысл создать шаблон, содержащий заготовку таблицы данных.
1. Создадим пустую таблицу следующего вида:
Реестр геологических станций, выполненный лабораторией
морской геоакустики и петрофизики в Балтийском море
2. Сохраним заготовку таблицы как шаблон; для этого в окне сохранения таблицы в файле в поле “Тип” выберем вариант ШАБЛОН или явно укажем расширение файла как. xlt .
3. После этого файл закрываем.
4. Чтобы заполнить шаблон данными для конкретной станции, открываем файл шаблона (если в окне папки имя данного файла отсутствует, то в окне открытия файла изменяем тип файла на ШАБЛОН).
5. Заполняем таблицу конкретной информацией.
6. Сохраняем файл под другим именем, при этом либо явно указываем расширение. xls , либо устанавливаем тип файла как “Книга Microsoft Excel”.
Практическое задание 6.
Создайте шаблон следующего вида:
Квадрат № ________
Широта__________
Долгота __________
2. Занесите в таблицу названия месяцев и глубины (0, 5,10,15,20, 30, 40, 50, 60, 80, 100, 150).
3. Отформатируйте таблицу по своему желанию.
4. Сохраните таблицу в файле квадраты.xlt, закройте файл шаблона.
5. Откройте файл квадраты.xlt . Занесите в него значения температур, солености и плотности. В заголовок таблицы занесите данные о квадрате.
6. Сохраните файл под именем квадрат1.xls.
Занятие 7. ДЕЙСТВИЯ С РАБОЧИМ ЛИСТОМ
Рабочая книга состоит из нескольких рабочих листов. С рабочим листом можно выполнить следующие действия.
Для добавления в рабочую книгу нового рабочего листа используйте команду Вставка – Лист. Новый лист получит следующий свободный номер. Максимальное количество листов – 256.
Для удаления рабочего листа со всем содержимым выбираем команду Правка – Удалить лист. Рабочий лист удаляется со всем содержимым и восстановлению не подлежит.
Команда Формат – Лист – Переименовать позволяет присвоить рабочему листу новое имя. При этом возле старого имени на корешке листа появляется курсор. Старое имя нужно удалить, ввести новое и нажать клавишу.
Чтобы убрать с экрана корешки рабочих листов, применяется команда Формат – Лист – Скрыть. Обратное действие выполняет команда Формат – Лист – Показать.
Копирование или перенос рабочего листа выполняется командой Правка – Переместить/скопировать лист. При этом действия можно выполнить как в текущей рабочей книге (файле), так и в другом открытом файле. Для выполнения операции копирования необходимо включить соответствующий флажок в окне диалога. Копия получит то же имя, что и исходный файл. В скобках будет указано (2), (3) и т.д.
Упражнение 6.1
Практическое задание 7
На первом рабочем листе создайте таблицу следующего вида:
Основные морфометрические характеристики отдельных морей
Море | Площадь, тыс. км 2 | Объем воды, тыс. км 3 | Глубина, м | |
средняя | наибольшая |
|||
Карибское | 2777 | 6745 | 2429 | 7090 |
Средиземное | 2505 | 3603 | 1438 | 5121 |
Северное | ||||
Балтийское | ||||
Черное | 1315 | 2210 |
Занятие 8. СОЗДАНИЕ БАЗ ДАННЫХ, ИЛИ РАБОТА СО СПИСКАМИ
В Microsoft Excel в качестве базы данных можно использовать список.
Список - это способ представления данных, при котором данные в таблице взаимосвязаны и структура таблицы определяется заранее. При выполнении обычных операций с данными, например, при поиске, сортировке или обработке данных, списки автоматически распознаются как базы данных, при этом курсор должен находиться в любом месте внутри таблицы.
Если у базой считают таблица данных, то:
Все действия со списками (базой данных) выполняет команда главного меню ДАННЫЕ.
1. Размер и расположение списка
2. Заголовки столбцов
Команда ДАННЫЕ ФОРМА
Форма - это способ представления данных из таблицы, когда на экране представлено содержимое только одной записи. Окно формы показано на рис. 8.
С помощью формы можно:
Рис. 8. Окно формы для занесения, просмотра, удаления и поиска записей
Вставка записей с помощью формы
Чтобы добавить запись в список, нажмите клавишу ENTER. По завершении набора последней записи нажмите кнопку Закрыть , чтобы добавить набранную запись и выйти из формы.
Примечание
Если поле списка содержит формулу, то в форме выводится ее результат. Изменять это поле в форме нельзя. При добавлении записи, содержащей формулу, результат формулы не будет вычислен до нажатия клавиши ENTER или кнопки Закрыть . Чтобы отменить добавление записи, нажмите кнопку Вернуть перед нажатием клавиши ENTER или кнопки Закрыть . Microsoft Excel автоматически добавляет запись при переходе к другой записи или закрытии формы.
Поиск записей в списке с помощью формы
Для перемещения на одну запись нажмите на стрелки полосы прокрутки в диалоговом окне. Чтобы переместиться на 10 записей, нажмите полосу прокрутки между стрелками.
Чтобы задать условия поиска или условия сравнения, нажмите кнопку Критерии . Введите критерии в форме. Чтобы найти совпадающие с критериями записи, нажмите кнопки Далее или Назад . Чтобы вернуться к правке формы, нажмите кнопку Правка .
Практическое задание 8
Занятие 9. СОЗДАНИЕ БАЗ ДАННЫХ, ИЛИ РАБОТА СО СПИСКАМИ (ПРОДОЛЖЕНИЕ)
Команда ДАННЫЕ – СОРТИРОВКА
Данная команда позволяет отсортировать данные из списка по одному или нескольким полям. При этом строки таблицы сортируются как записи базы данных. Если указана сортировка по нескольким полям, то первое поле считается основным. В этом случае все записи сортируются по первому указанному для сортировки полю. Поля, в которых первый параметр для сортировки имеет одинаковое значение, внутри данной группы сортируются по второму параметру и т.д.
Чтобы выполнить сортировку списка, делаем активной любую ячейку внутри списка, затем выбираем команду “Данные – сортировка”, определяем поле для сортировки и ее порядок. Возможны два варианта сортировки – по возрастанию и по убыванию . Для текстового поля это означает в алфавитном порядке и наоборот. Окно команды “Данные-Сортировка” представлено на рис. 9.
Рис. 9. Окно сортировки данных в списке
Упражнение 9.1
Список студентов пересортируйте в алфавитном порядке по специальности и фамилиям студентов.
Команда ДАННЫЕ – ИТОГИ
Команда позволяет рассчитать промежуточный и общий итоги. При этом определяется поле, которое будет суммироваться (конечно, операция имеет смысл только для числовой информации). В окне команды определяем операцию (“Сумма”) и поле, под которым будут располагаться значения промежуточных сумм. В нижней части списка будет находиться итоговое значение по выбранному полю.
Рис.10. Окно команды Данные-Фильтр-Автофильтр
Команда ДАННЫЕ – ФИЛЬТР
Команда “Данные-Фильтр” (рис. 10) является удобным инструментом для создания запросов по одному или нескольким критериям. Особенно удобным и наглядным является подрежим “Автофильтр”. При включении данного режима (при вызове его должна быть активна любая ячейка внутри списка) справа от названий полей списка появится раскрывающаяся кнопка со стрелкой, которая содержит перечень всех значений для данного поля. При выборе значения из данного списка на экране остаются только записи, удовлетворяющие данному критерию поиска. Остальные записи скрываются. С результатом запроса можно работать как с обычной таблицей – распечатать, сохранить в отдельном файле, перенести на другой рабочий лист и т.д. Чтобы вернуться к первоначальному виду таблицы, в списке справа от названия поля выбираем вариант “все”.
Упражнение 9.2
В списке студентов, созданном на предыдущем занятии выполните следующие запросы:
Практическое задание 9
Создайте таблицу как базу данных со следующими наименованиями полей:
Определите общее количество книг и их суммарную стоимость.
Выполните следующие запросы:
Занятие 10. МАКРОСЫ
Порядок создания макросов
1. Выберите в главном меню программы команду Сервис – Макрос – Начать запись . На экране появится окно для определения параметров данного макроса, которое представлено на рис. 11.
Рис. 11. Окно для определения параметров макроса
2. Введите в соответствующие поля имя макроса, назначьте макросу комбинацию клавиш для быстрого запуска (буква должна быть латинской), в поле описания можно кратко указать назначение данного макроса. Определите место сохранения макроса – данный файл или “Личная книга макросов” (файл Personal.xls ).
3. Далее выполняйте последовательность действий, которые вы хотите записать в макрос. По окончании работы нажмите кнопку конца записи на панели инструментов макроса или выберите команду Сервис – Макрос – Остановить запись .
4. Для запуска уже записанного макроса достаточно нажать закрепленную за ним комбинацию функциональных клавиш или выбрать в перечне макросов имя макроса, которое будет доступно при выборе команды Сервис – Макрос – Макросы .
5. Последовательность записанных действий автоматически преобразуется в операторы встроенного языка Visual Basic. Для пользователя, имеющего навыки программирования возможно создание более сложных программируемых макросов. Для этого можно воспользоваться командой Сервис – Макрос – Редактор Visual Basic .
Практическое задание 10
Создайте макрос с именем “Шаблон”, который бы работал в пределах данной рабочей книги. Назначьте данному макросу комбинацию функциональных клавиш Ctrl + q. Макрос должен содержать последовательность действий 1 – 5 (см. ниже):
Создайте пустую таблицу следующего вида на первом рабочем листе:
Значения показателя вертикального ослабления
для открытых океанских районов
ПРАКТИЧЕСКИЕ ЗАДАНИЯ
ДЛЯ САМОКОНТРОЛЯ И ЗАЧЕТНОГО ЗАНЯТИЯ
Задание 1
Создайте таблицу следующего вида. Определите итоговые суммы. Выполните форматирование таблицы по своему желанию.
Смета затрат за май 1999 г.
Наименование работы | Стоимость работы, руб. | Стоимость исходного |
1. Покраска дома | 2000 | |
2. Побелка стен | 1000 | |
3. Вставка окон | 4000 | 1200 |
4. Установка сантехники | 5000 | 7000 |
5. Покрытие пола паркетом | 2500 | 10000 |
ИТОГО : |
Задание 2
Создайте таблицу следующего вида как базу данных. Заполнение информации выполните через форму. Определите перечень фильмов определенного года.
Список видеокассет
Номер | Название | Год выпуска | Длительность |
Доберман | 1997 | 1ч 30 мин |
|
Крестный отец | 1996 | 8ч 45 мин |
|
Убрать перископ | 1996 | 1ч 46 мин |
|
Криминальное чтиво | 1994 | 3 ч 00 мин |
|
Кровавый спорт | 1992 | 1 ч 47 мин |
|
Титаник | 1998 | 3 ч 00 мин |
Задание 3
Создайте таблицу следующего вида. Отсортируйте данные в таблице в порядке возрастания количества товара.
Перечень товаров на складе №1
Номер товара | Наименование товара | Количество товара |
Сгущеное молоко, банок | ||
Сахар, кг | ||
Мука, кг | ||
Пиво “Очаковское”, бут. | ||
Водка “Столичная”, бут. |
Задание 4
Создайте таблицу следующего вида. Рассчитайте по формуле данные в последнем столбце.
Номер счета | Наименование вклада | Про-цент | Начальная сумма вклада, руб. | Итоговая сумма вклада, руб. |
Годовой | 5000 | 5400 |
||
Рождественский | 15000 | 17250 |
||
Новогодний | 8500 | 10200 |
||
Мартовский | 11000 | 12430 |
Задание 5
Создайте таблицу следующего вида и постройте 4 диаграммы по всем видам деревьев и итоговым данным.
Данные по Светлогорскому лесничеству (хвойные, тыс. шт.)
Наименование | Молодняки | Средне- | Приспевающие | Всего | ||||
1973 | 1992 | 1973 | 1992 | 1973 | 1992 | 1973 | 1992 |
|
Сосна | 201,2 | 384,9 | 92,7 | |||||
Ель | 453,3 | 228,6 | 19,1 | 1073 | 701,6 |
|||
Пихта | ||||||||
Лиственница | 16,5 | |||||||
ИТОГО: | 657,7 | 1361 | 633,5 | 134,8 | 1822 | 1411,1 |
Задание 6
Создайте таблицу следующего вида, сохраняя установки по форматированию. Рассчитайте данные в последнем столбце по формуле.
Смета затрат
Наименование | Стоимость одного часа | Количество часов | Стоимость | Сумма |
|
Побелка | 10,50р. | 124р. | |||
Поклейка обоев | 12,40р. | 2 399р. | |||
Укладка паркета | 25,00р. | 4 500р. | |||
Полировка паркета | 18,00р. | 500р. | |||
Покраска окон | 12,50р. | 235р. | |||
Уборка мусора | 10,00р. | 0р. | |||
ИТОГО |
Задание 7
Создайте таблицу следующего вида. Рассчитайте данные во втором и третьем столбце по формулам. Процент налога примите равным 12. Определите итоговые данные по столбцам.
ФИО | Должность | Оклад, руб. | Налог, руб. | К выдаче, руб. |
|
Яблоков Н.А. | Уборщик | ||||
Иванов К.Е. | Директор | 2000 | |||
Егоров О.Р. | Зав. тех. отделом | 1500 | |||
Семанин В.К. | Машинист | ||||
Цой А.В. | Водитель | ||||
Петров К.Г. | Строитель | ||||
Леонидов Т.О. | Крановщик | 1200 | |||
8 | Проша В.В. | Зав. складом | 1300 | ||
ИТОГО | 7800 |
Задание 8
Создайте бланк расписания. Сохраните его как шаблон. На основе этого шаблона создайте свое расписание занятий в этом семестре.
РАСПИСАНИЕ
Осенний триместр 2010/2011 учеб. год
Задание 9
Создайте таблицу следующего вида. Пересортируйте данные по дате поставки.Определите суммарный доход.
Район | Поставка, кг | Дата | Количество | Опт. цена, руб. | Розн. | Доход, руб. |
Западный | Мясо | 01.09.95 | 23 | 12 | 15,36 | 353,28 |
Западный | Молоко | 01.09.95 | 30 | 3 | 3,84 | 115,2 |
Южный | Молоко | 01.09.95 | 45 | 3,5 | 4,48 | 201,6 |
Восточный | Мясо | 05.09.95 | 12 | 13 | 16,64 | 199,68 |
Западный | Картофель | 05.09.95 | 100 | 1,2 | 1,536 | 153,6 |
Западный | Мясо | 07.09.95 | 45 | 12 | 15,36 | 691,2 |
Западный | Капуста | 08.09.95 | 60 | 2,5 | 3,2 | 192 |
Южный | Мясо | 08.09.95 | 32 | 15 | 19,2 | 614,4 |
Западный | Капуста | 10.09.95 | 120 | 3,2 | 4,096 | 491,52 |
Восточный | Картофель | 10.09.95 | 130 | 1,3 | 1,664 | 216,32 |
Южный | Картофель | 12.09.95 | 95 | 1,1 | 1,408 | 133,76 |
Восточный | Мясо | 15.09.95 | 34 | 14 | 17,92 | 609,28 |
Северный | Капуста | 15.09.95 | 90 | 2,7 | 3,456 | 311,04 |
Северный | Молоко | 15.09.95 | 45 | 3,4 | 4,352 | 195,84 |
Восточный | Молоко | 16.09.95 | 50 | 3,2 | 4,096 | 204,8 |
Пользователи Excel давно и успешно применяют программу для решения различных типов задач в разных областях.
Excel – это самая популярная программа в каждом офисе во всем мире. Ее возможности позволяют быстро находить эффективные решения в самых разных сферах деятельности. Программа способна решать различного рода задачи: финансовые, экономические, математические, логические, оптимизационные и многие другие. Для наглядности мы каждое из выше описанных решение задач в Excel и примеры его выполнения.
Оптимизационные модели применяются в экономической и технической сфере. Их цель – подобрать сбалансированное решение, оптимальное в конкретных условиях (количество продаж для получения определенной выручки, лучшее меню, число рейсов и т.п.).
В Excel для решения задач оптимизации используются следующие команды:
Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».
Условие. Фирма производит несколько сортов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» - 250 рублей. «3» - 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.
Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:
На основании этих данных составим рабочую таблицу:
Активизируем команду «Поиск решения» и вносим параметры.
После нажатия кнопки «Выполнить» программа выдает свое решение.
Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.
Чаще всего для этой цели применяются финансовые функции. Рассмотрим пример.
Оформим исходные данные в виде таблицы:
Так как процентная ставка не меняется в течение всего периода, используем функцию ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).
Заполнение аргументов:
Вкладчику необходимо вложить эти деньги, поэтому результат отрицательный.
Для проверки правильности решения воспользуемся формулой: ПС = БС / (1 + ставка) кпер. Подставим значения: ПС = 400 000 / (1 + 0,05) 16 = 183245.
Для установления количественных и качественных взаимосвязей применяются математические и статистические методы и модели.
Дано 2 диапазона значений:
Значения Х будут играть роль факторного признака, Y – результативного. Задача – найти коэффициент корреляции.
Для решения этой задачи предусмотрена функция КОРРЕЛ (массив 1; массив 2).
В табличном процессоре есть встроенные логические функции. Любая из них должна содержать хотя бы один оператор сравнения, который определит отношение между элементами (=, >, =,
Ученики сдавали зачет. Каждый из них получил отметку. Если больше 4 баллов – зачет сдан. Менее – не сдан.
Средствами программы можно решать как простейшие математические задачки, так и более сложные (операции с функциями, матрицами, линейными уравнениями и т.п.).
Условие учебной задачи. Найти обратную матрицу В для матрицы А.
Возможности Excel не безграничны. Но множество задач программе «под силу». Тем более здесь не описаны возможности которые можно расширить с помощью макросов и пользовательских настроек.