Цель работы
Изучить основные правила работы с электронными таблицами MS EXCEL:
Ø ввод данных и простейшие операции с данными;
Ø использование списков, функций;
Ø использование надстроек с дополнительными функциями;
Ø построение диаграмм и графиков в EXCEL.
Порядок выполнения лабораторной работы
1. Создать книгу MS EXCEL и сохранить ее под своей фамилией.
2. В книге создать 7 страниц (по числу заданий). Страницы назвать — «Задание_1», «Задание_2», «Задание_3» и т.д.
3. На каждом листе оформить заголовок текущего задания.
4. Используя функцию «СЕГОДНЯ()» проставить текущую дату (на каждом задании).
5. Заполнить начальные данные — значения, выделенные на рисунках жирным шрифтом. Запрещается полностью копировать таблицы из данного методического пособия (при наличии такой таблицы на странице, задание не зачитывается!).
6. Используя различные функции, абсолютную и относительную адресации, если необходимо дополнительные функции, создать формулы и заполнить таблицы по заданию.
7. Отформатировать соответствующим образом столбцы (в расчетных значениях должно быть отображено два десятичных знака после запятой).
8. Оформить таблицы — цвет, заливка, границы, …
9. Построить, где необходимо, графики или диаграммы, зависящие от содержимого ячеек соответствующей таблицы.
10. Предъявить выполненную работу преподавателю. Письменный отчет НЕ оформляется. При защите данной лабораторной работы решающую роль играет уровень владения табличным процессором MS Excel.
Не забывайте периодически сохранять выполненные операции!
Задание 1
1. Создать «Таблицу поставок» используя рис.3.
2. Заполнить столбцы «Район» и «Поставка». Для повторяющихся элементов таблицы можно воспользоваться функцией копирования.
3. Столбец «Дата поставки» заполнить с помощью функции СЛУЧМЕЖДУ из раздела Математические функции. (см.рис.1).
Рис.1.
4. Столбцы «Количество» «Опт.цена» так же заполнить случайными числами. Столбец «Количество» — Интервал распределения: последние две цифры № студенческого — последние две цифры № студенческого + 20, а столбец «Опт.цена» — последние две цифры № студенческого – 20 — последние две цифры № студенческого.
5. Столбцы «Розничная цена» … «Остаток» заполнить при помощи формул и используя абсолютную адресацию. Процент надбавки на розничную цену, процент отчислений от дохода и процент налога от дохода должны быть выписаны в отдельные от таблицы ячейки. При использовании этих процентов в расчётах, к ним должна применяться абсолютная адресация.
Ø «Розничная цена» — число большее оптовой цены на Процент надбавки на розничную цену;
15.09.2007.
| Процент надбавки на розничную цену | 20% |
| Процент отчислений от дохода | 48% |
| Процент налога от дохода | 10% |
| Район | Поставка | Дата поставки | Количество | Опт.цена | Розн.цена | Доход | Отчисления | Налог | Остаток |
| Западный | Мясо | 01.сен | 23 тонн | 12 т.руб | 14,4 т.руб | 331,2 т.руб | 159, т.руб | 33,1 т.руб | 139,1 т.руб |
| Южный | Молоко | 02.сен | 30 тыс.литр. | 3 т.руб | 3,6 т.руб | 108, т.руб | 51,8 т.руб | 10,8 т.руб | 45,4 т.руб |
| Восточный | Капуста | 03.сен | 32 тонн | 62 т.руб | 74,4 т.руб | 2 380,8 т.руб | 1 142,8 т.руб | 238,1 т.руб | 999,9 т.руб |
| Северный | Картофель | 04.сен | 95 тонн | 95 т.руб | 114, т.руб | 10 830, т.руб | 5 198,4 т.руб | 1 083, т.руб | 4 548,6 т.руб |
| Западный | Морковь | 05.сен | 46 тонн | 76 т.руб | 91,2 т.руб | 4 195,2 т.руб | 2 013,7 т.руб | 419,5 т.руб | 1 762, т.руб |
| Южный | Мясо | 03.сен | 65 тонн | 32 т.руб | 38,4 т.руб | 2 496, т.руб | 1 198,1 т.руб | 249,6 т.руб | 1 048,3 т.руб |
| Восточный | Молоко | 07.сен | 79 тыс.литр. | 64 т.руб | 76,8 т.руб | 6 067,2 т.руб | 2 912,3 т.руб | 606,7 т.руб | 2 548,2 т.руб |
| Северный | Капуста | 05.сен | 23 тонн | 62 т.руб | 74,4 т.руб | 1 711,2 т.руб | 821,4 т.руб | 171,1 т.руб | 718,7 т.руб |
| Западный | Картофель | 06.сен | 13 тонн | 45 т.руб | 54, т.руб | 702, т.руб | 337, т.руб | 70,2 т.руб | 294,8 т.руб |
| Южный | Морковь | 04.сен | 46 тонн | 62 т.руб | 74,4 т.руб | 3 422,4 т.руб | 1 642,8 т.руб | 342,2 т.руб | 1 437,4 т.руб |
| Восточный | Мясо | 01.сен | 95 тонн | 31 т.руб | 37,2 т.руб | 3 534, т.руб | 1 696,3 т.руб | 353,4 т.руб | 1 484,3 т.руб |
| Северный | Молоко | 02.сен | 76 тыс.литр. | 32 т.руб | 38,4 т.руб | 2 918,4 т.руб | 1 400,8 т.руб | 291,8 т.руб | 1 225,7 т.руб |
| Западный | Капуста | 01.сен | 32 тонн | 64 т.руб | 76,8 т.руб | 2 457,6 т.руб | 1 179,6 т.руб | 245,8 т.руб | 1 032,2 т.руб |
| Южный | Картофель | 04.сен | 54 тонн | 32 т.руб | 38,4 т.руб | 2 073,6 т.руб | 995,3 т.руб | 207,4 т.руб | 870,9 т.руб |
| Восточный | Морковь | 02.сен | 65 тонн | 62 т.руб | 74,4 т.руб | 4 836, т.руб | 2 321,3 т.руб | 483,6 т.руб | 2 031,1 т.руб |
| ИТОГО | Доход | |
 Остаток
|
Рис.3. Поставки по районам
Ø «Доход» — общая сумма, полученная за весь продукт, проданный по розничной цене;
Ø «Отчисления» — сумма, определяемая с помощью Процента отчислений от дохода;
Ø «Налог» — часть дохода, вычисляемая Процентом налога от дохода;
Ø «Остаток» — сумма, равная разности дохода и вычетов налога и отчислений.
6. Используя «Формат по образцу» (
) указать денежные единицы.
7. Посчитать общий Доход и Остаток.
Задание 2
1. Используя данные рис.4 составить таблицу «Отчет по фирме».
15.09.07
| Товар 1 | Товар 2 | |||||
| Месяц | Цена | Продано | Итого | Цена | Продано | Итого |
| Январь | $15 | 10 шт. | $150 | $20 | 100 шт. | $2 000 |
| Февраль | $20 | 16 шт. | $320 | $20 | 90 шт. | $1 800 |
| Март | $25 | 22 шт. | $550 | $20 | 80 шт. | $1 600 |
| Квартал 1 | 48 шт. | $1 020 | 270 шт. | $5 400 | ||
| Апрель | $30 | 28 шт. | $840 | $20 | 70 шт. | $1 400 |
| Май | $35 | 34 шт. | $1 190 | $20 | 60 шт. | $1 200 |
| Июнь | $40 | 40 шт. | $1 600 | $20 | 50 шт. | $1 000 |
| Квартал 2 | 102 шт. | $3 630 | 180 шт. | $3 600 | ||
| Июль | $45 | 46 шт. | $2 070 | $30 | 40 шт. | $1 200 |
| Август | $50 | 52 шт. | $2 600 | $30 | 50 шт. | $1 500 |
| Сентябрь | $55 | 58 шт. | $3 190 | $30 | 60 шт. | $1 800 |
| Квартал 3 | 156 шт. | $7 860 | 150 шт. | $4 500 | ||
| Октябрь | $60 | 64 шт. | $3 840 | $30 | 70 шт. | $2 100 |
| Ноябрь | $65 | 70 шт. | $4 550 | $30 | 80 шт. | $2 400 |
| Декабрь | $70 | 76 шт. | $5 320 | $30 | 90 шт. | $2 700 |
| Квартал 4 | 210 шт. | $13 710 | 240 шт. | $7 200 | ||
| Итого за год | 516 шт. | $26 220 | 840 шт. | $20 700 |
Рис.4 Отчет по фирме Север за 2005 год
2. Таблица заполняется с использованием функции MS EXCEL Автозаполнение с последующей вставкой строк.
(Подробнеео функции Автозаполнение сказано в Приложении к данной лабораторной работе — раздел Основные сведения об MS EXCEL пункт 3. Ввод и редактирование данных.)
3. Итоги по кварталам заполняются суммированием соответствующих ячеек по месяцам.
Рис. 5.
4. Создать 2 круговых диаграммы – по каждому товару (см.рис.5).
Задание 3
1. Выделив отдельную ячейку для начального капитала создать таблицу, аналогичную рис.6.
2. Автозаполнением заполнить столбец года.
3. Продлить таблицу до прошлого календарного года.
4. Величина капитала на первый рабочий год равна начальному капиталу.
5. Размер капитала на начало остальных периодов равен величине остатка на предыдущий отчетный год.
6. Столбцы «Доход» и «Расход» заполняются с использованием функции Прогрессия, находящейся в Правке\Заполнить. Шаг прогрессии взять равным («+» или «-») последним двум цифрам № студенческого.
7. Столбец «Остаток» формируется из капитала в начале года, добавляется доход за год и вычитается расход.
8. Заполнить нижние строки при помощи стандартных функций.
9. Отформатировать столбцы по своему вкусу.
10. Построить Гистограмму, отражающую рост капитала по годам.
15.09.07
| Начальный капитал | $10 000 |
| капитал | доход | расход | остаток | |
| 1992 год | $10 000 | $500 | $300 | $10 200 |
| 1993 год | $10 200 | $600 | $280 | $10 520 |
| 1994 год | $10 520 | $700 | $260 | $10 960 |
| 1995 год | $10 960 | $800 | $240 | $11 520 |
| 1996 год | $11 520 | $900 | $220 | $12 200 |
| 1997 год | $12 200 | $1 000 | $200 | $13 000 |
| 1998 год | $13 000 | $1 100 | $180 | $13 920 |
| 1999 год | $13 920 | $1 200 | $160 | $14 960 |
| 2000 год | $14 960 | $1 300 | $140 | $16 120 |
| 2001 год | $16 120 | $1 400 | $120 | $17 400 |
| 2002 год | $17 400 | $1 500 | $100 | $18 800 |
| 2003 год | $18 800 | $1 600 | $80 | $20 320 |
| Максимум | $18 800 | $1 600 | $300 | $20 320 |
| Среднее | $13 300 | $1 050 | $190 | $14 160 |
| Минимум | $10 000 | $500 | $80 | $10 200 |
Рис.6 Общая таблица доходов по годам
Задание 4
Данное задание состоит из 4 упражнений на использование Логических функций (см.рис.8).
Создав таблицы и заполнив данные значения, создать формулы для вычисляемых столбцов. На весь столбец должна быть одна формула.
В Упражнении №1 (см.рис.8-а) столбцы «Доход» и «Расход» заполняются, используя функцию генерация случайных чисел. Интервал распределения — последние две цифры № студенческого 
10.
Столбец «Рабочие дни» заполняется при помощи автозаполнения.
15.09.07
Упражнение №1.
| Рабочие дни | Доход | Расход | Прибыль | Убыток |
| 01.04.2006 | 12 руб.. | 22 руб. | 10 руб. | |
| 02.04.2006 | 23 руб. | 11 руб. | 12 руб. | |
| 03.04.2006 | 32 руб. | 55 руб. | 23 руб. | |
| 04.04.2006 | 97 руб. | 66 руб. | 31 руб. | |
| 05.04.2006 | 65 руб. | 88 руб. | 23 руб. | |
| 08.04.2006 | 32 руб. | 33 руб. | 1 руб. | |
| 09.04.2006 | 65 руб. | 44 руб. | 21 руб. | |
| 10.04.2006 | 54 руб. | 55 руб. | 1 руб. | |
| 11.04.2006 | 87 руб. | 22 руб. | 65 руб. | |
| 12.04.2006 | 65 руб. | 77 руб. | 12 руб. |
Рис.8-а
Упражнение №2.
| Цена изделия | Скидка 10% | Стоим. покупки |
| 45 руб. | да | 41 руб. |
| 65 руб. | нет | 65 руб. |
| 32 руб. | нет | 32 руб. |
| 54 руб. | да | 49 руб. |
| 87 руб. | нет | 87 руб. |
| 32 руб. | нет | 32 руб. |
| 42 руб. | да | 38 руб. |
Рис.8-б
В Упражнениях №2-№4 (см.рис.8-б-г) столбец «Цена изделия» заполняется аналогично столбцу «Количество» из Задания 1.
Упражнение №3.
| Цена изделия | Кол-во изделий | Процент скидки | Сумма скидки | Стоим. покупки |
| 45 руб. | 1 шт. | 0% | 0 руб. | 45 руб. |
| 65 руб. | 3 шт. | 0% | 0 руб. | 195 руб. |
| 32 руб. | 5 шт. | 3% | 5 руб. | 155 руб. |
| 54 руб. | 11 шт. | 5% | 30 руб. | 564 руб. |
| 32 руб. | 2 шт. | 0% | 0 руб. | 64 руб. |
| 42 руб. | 4 шт. | 3% | 5 руб. | 163 руб. |
Рис.8-в
В Упражнении №3 процент скидки зависит от кол-ва, покупаемых изделий: до 3 шт. — 0%, от 4 до 5 — 3%, свыше 5 шт. — 5% и вычисляется с помощью функции ЕСЛИ().
Упражнение №4.
| Цена изделия | Кол-во изделий | Процент скидки | Сумма скидки | Стоим. покупки |
| 45 руб. | 1 шт. | 0% | 0 руб. | 45 руб. |
| 65 руб. | 3 шт. | 5% | 9,75 руб. | 185,25 руб. |
| 32 руб. | 5 шт. | 5% | 8 руб. | 152 руб. |
| 54 руб. | 11 шт. | 5% | 29,7 руб. | 564,3 руб. |
| 87 руб. | 8 шт. | 5% | 34,8 руб. | 661,2 руб. |
| 32 руб. | 2 шт. | 0% | 0 руб. | 64 руб. |
| 42 руб. | 4 шт. | 5% | 8,4 руб. | 159,6 руб. |
Рис.8-г
В Упражнении №4 (см.рис.8-г) процент скидки зависит от стоимости покупаемых изделий: до 100 руб. — 0%, свыше — 5%.
Задание 5
1. Создать «Ведомость заработной платы преподавателей» (см.рис.9). Заполнить исходные данные.
2. Не забудьте вставить перед таблицей текущую дату.
15.09.07
| № | Ф.И.О. | Должность | Дата приема | Стаж | Оплата за час | Нагрузка |
| Иванов И.И. | преподаватель | 01.07.1999 | 6 лет | 70 руб. | 60 ак.час. | |
| Петров А.Г. | ст.преподаватель | 04.11.1995 | 5 лет | 75 руб. | 50 ак.час. | |
| Сидоров К.П. | ассистент | 11.06.2001 | 7 лет | 60 руб. | 65 ак.час. | |
| Федоров А.Л. | профессор | 25.03.1985 | 16 лет | 110 руб. | 40 ак.час. | |
| Васильев Е.О. | преподаватель | 04.07.1990 | 11 лет | 70 руб. | 60 ак.час. | |
| Гигорева А.О. | ст.преподаватель | 04.04.2000 | 15 лет | 75 руб. | 50 ак.час. | |
| Зорина П.Л. | ассистент | 23.03.1992 | 9 лет | 60 руб. | 65 ак.час. | |
| Жуков Н.Д. | профессор | 16.05.1991 | 10 лет | 110 руб. | 40 ак.час. | |
| Сурков Р.Д. | преподаватель | 05.12.1990 | 10 лет | 70 руб. | 60 ак.час. | |
| Барсуков Е.Л. | ст.преподаватель | 08.12.1989 | 11 лет | 75 руб. | 50 ак.час. | |
| Тюрин Н.Л. | ассистент | 08.06.2003 | 4 лет | 60 руб. | 65 ак.час. |
Продолжение таблицы
| Зарплата | Процент надбавки | Сумма надбавки | Итого | Сумма налога | На руки |
| 900 руб. | 15% | 135 руб. | 1 035 руб. | 135 руб. | 900 руб. |
| 1 000 руб. | 15% | 150 руб. | 1 150 руб. | 150 руб. | 1 001 руб. |
| 650 руб. | 15% | 98 руб. | 748 руб. | 97 руб. | 650 руб. |
| 2 400 руб. | 25% | 600 руб. | 3 000 руб. | 390 руб. | 2 610 руб. |
| 900 руб. | 25% | 225 руб. | 1 125 руб. | 146 руб. | 979 руб. |
| 1 000 руб. | 25% | 250 руб. | 1 250 руб. | 163 руб. | 1 088 руб. |
| 650 руб. | 15% | 98 руб. | 748 руб. | 97 руб. | 650 руб. |
| 2 400 руб. | 25% | 600 руб. | 3 000 руб. | 390 руб. | 2 610 руб. |
| 900 руб. | 25% | 225 руб. | 1 125 руб. | 146 руб. | 979 руб. |
| 1 000 руб. | 25% | 250 руб. | 1 250 руб. | 163 руб. | 1 088 руб. |
| 650 руб. | 25% | 163 руб. | 813 руб. | 106 руб. | 707 руб. |
Рис.9 Ведомость расчета заработной платы
3. «Дата Приема» — генерация случайных чисел (разумные пределы).
4. Стаж вычисляется, используя разницу между датой приема и текущей датой (данный результат MS EXCEL вычисляет в сутках). Полное число лет вычисляется функцией ЦЕЛОЕ().
5. Зарплата зависит от оплаты за час и количества отработанных часов.
6. Процент надбавки зависит от стажа:
до 3 лет — 0%; до 5 лет — 10 %; от 5 до 10 лет — 15 %; свыше 10 лет — 25 %.
7. Сумма надбавки = Зарплата * Процент надбавки.
8. Сумма налога зависит от итога: до 4300 руб. — 13%; свыше — 30%.
9. Таблицу необходимо отсортировать по алфавиту преподавателей (и/или по году приема на работу и по нагрузке). Обратить внимание на нумерацию строк в таблице — порядок должен остаться прежним.
10. Для представления преподавателю данного задания разделить окно таблицы (Окно\Разделить) на две части, выделив три первых столбца.
Задание 6
1. Таблица «Сметная ведомость» (см.рис.10) формируется из данных предыдущего задания.
2. Столбец «Ф.И.О.» копируется из Задания 5.
| Дата | 15.09.07 | ||
| Стоимость БТ | 1700 руб. | ||
| № | Ф.И.О. | Стаж | Тип полиса | Доплата за стоматологию | Доплата за стационар | Сумма |
| Иванов И.И. | 6 лет | БТС | 1 000 руб. | 0 руб. | 2 700 руб. | |
| Петров А.Г. | 5 лет | БТ | 0 руб. | 0 руб. | 1 700 руб. | |
| Сидоров К.П. | 7 лет | БТС | 1 000 руб. | 0 руб. | 2 700 руб. | |
| Федоров А.Л. | 16 лет | БТСС | 1 000 руб. | 2 500 руб. | 5 200 руб. | |
| Васильев Е.О. | 11 лет | БТСС | 1 000 руб. | 2 500 руб. | 5 200 руб. | |
| Гигорева А.О. | 15 лет | БТСС | 1 000 руб. | 2 500 руб. | 5 200 руб. | |
| Зорина П.Л. | 9 лет | БТС | 1 000 руб. | 0 руб. | 2 700 руб. | |
| Жуков Н.Д. | 10 лет | БТСС | 1 000 руб. | 2 500 руб. | 5 200 руб. | |
| Сурков Р.Д. | 10 лет | БТСС | 1 000 руб. | 2 500 руб. | 5 200 руб. | |
| Барсуков Е.Л. | 11 лет | БТСС | 1 000 руб. | 2 500 руб. | 5 200 руб. | |
| Тюрин Н.Л. | 14 лет | БТСС | 1 000 руб. | 2 500 руб. | 5 200 руб. | |
Рис.10 Сметная ведомость по медицинскому страхованию для сотрудников
3. Столбец «Стаж» определяется ссылкой на соответствующий столбец в предыдущем задании.
4. Столбец «Тип страхового полиса», выдаваемого сотруднику, зависит от стажа работы: стаж до 5 лет — тип полиса БТ, до 10 лет — БТС, 10 и выше — БТСС (использовать функцию ЕСЛИ()).
5. Для типов полисов БТС и БТСС доплата за услуги стоматолога 1000 руб.
6. Для типа БТСС дополнительно доплата за лечение в стационаре 2,5 тыс.руб.
7. Данную таблицу так же необходимо уметь отсортировывать по различным показателям.
Задание 7
Используя данные таблицы «Поставки» из Задания 1, составить сводные таблицы по образцам на рис.11. для выполнения задания использовать «Данные\Мастер сводных таблиц».
| Сумма по полю Розн.цена | Поставка | |||||
| Дата поставки | Капуста | Картофель | Молоко | Морковь | Мясо | Общий итог |
| 01.09.2003 | 76,8 т.руб | 0 т.руб | 0 т.руб | 0 т.руб | 51,6 т.руб | 128,4 т.руб |
| 02.09.2003 | 0 т.руб | 0 т.руб | 42, т.руб | 74,4 т.руб | 0 т.руб | 116,4 т.руб |
| 03.09.2003 | 74,4 т.руб | 0 т.руб | 0 т.руб | 0 т.руб | 38,4 т.руб | 112,8 т.руб |
| 04.09.2003 | 0 т.руб | 152,4 т.руб | 0 т.руб | 74,4 т.руб | 0 т.руб | 226,8 т.руб |
| 05.09.2003 | 74,4 т.руб | 0 т.руб | 0 т.руб | 91,2 т.руб | 0 т.руб | 165,6 т.руб |
| 06.09.2003 | 0 т.руб | 54, т.руб | 0 т.руб | 0 т.руб | 0 т.руб | 54, т.руб |
| 07.09.2003 | 0 т.руб | 0 т.руб | 76,8 т.руб | 0 т.руб | 0 т.руб | 76,8 т.руб |
| Общий итог | 225,6 т.руб | 206,4 т.руб | 118,8 т.руб | 240, т.руб | 90, т.руб | 880,8 т.руб |
Рис.11-а Изменение розничных цен по отдельным категориям продуктов
| Сумма по полю Доход | Поста вка | |||||
| Район | Капуста | Картофель | Молоко | Морковь | Мясо | Общий итог |
| Восточный | 2 380,8 т.руб | 0 т.руб | 6 067,2 т.руб | 4 836, т.руб | 3 534, т.руб | 16 818, т.руб |
| Западный | 2 457,6 т.руб | 702, т.руб | 0 т.руб | 4 195,2 т.руб | 331,2 т.руб | 7 686, т.руб |
| Северный | 1 711,2 т.руб | 10 830, т.руб | 2 918,4 т.руб | 0 т.руб | 0 т.руб | 15 459,6 т.руб |
| Южный | 0 т.руб | 2 073,6 т.руб | 108, т.руб | 3 422,4 т.руб | 2 496, т.руб | 8 100, т.руб |
| Общий итог | 6 549,6 т.руб | 13 605,6 т.руб | 9 093,6 т.руб | 12 453,6 т.руб | 6 361,2 т.руб | 48 063,6 т.руб |
Рис.11-б Доходы районов по отдельным видам поставок
| Сумма по полю Количество | Поставка | |||||
| Дата поставки | Капуста | Картофель | Молоко | Морковь | Мясо | Общий итог |
| 01.09.2003 | 32 тонн | 0 тонн | 0 тыс.литр. | 0 тонн | 118 тонн | 150 тонн |
| 02.09.2003 | 0 тонн | 0 тонн | 106 тыс.литр. | 65 тонн | 0 тонн | 171 тонн |
| 03.09.2003 | 32 тонн | 0 тонн | 0 тыс.литр. | 0 тонн | 65 тонн | 97 тонн |
| 04.09.2003 | 0 тонн | 149 тонн | 0 тыс.литр. | 46 тонн | 0 тонн | 195 тонн |
| 05.09.2003 | 23 тонн | 0 тонн | 0 тыс.литр. | 46 тонн | 0 тонн | 69 тонн |
| 06.09.2003 | 0 тонн | 13 тонн | 0 тыс.литр. | 0 тонн | 0 тонн | 13 тонн |
| 07.09.2003 | 0 тонн | 0 тонн | 79 тыс.литр. | 0 тонн | 0 тонн | 79 тонн |
| Общий итог | 87 тонн | 162 тонн | 185 тыс.литр. | 157 тонн | 183 тонн | 774 тонн |
Рис.11-в Изменение количества поставок по видам продукции
| Сумма по полю Налог | Район | ||||
| Дата | Восточный | Западный | Северный | Южный | Общий итог |
| 01.09.2003 | 353,4 т.руб | 278,9 т.руб | 0 т.руб | 0 т.руб | 632,3 т.руб |
| 02.09.2003 | 483,6 т.руб | 0 т.руб | 291,8 т.руб | 10,8 т.руб | 786,2 т.руб |
| 03.09.2003 | 238,1 т.руб | 0 т.руб | 0 т.руб | 249,6 т.руб | 487,7 т.руб |
| 04.09.2003 | 0 т.руб | 0 т.руб | 1 083, т.руб | 549,6 т.руб | 1 632,6 т.руб |
| 05.09.2003 | 0 т.руб | 419,5 т.руб | 171,1 т.руб | 0 т.руб | 590,6 т.руб |
| 06.09.2003 | 0 т.руб | 70,2 т.руб | 0 т.руб | 0 т.руб | 70,2 т.руб |
| 07.09.2003 | 606,7 т.руб | 0 т.руб | 0 т.руб | 0 т.руб | 606,7 т.руб |
| Общий итог | 1 681,8 т.руб | 768,6 т.руб | 1 546, т.руб | 810, т.руб | 4 806,4 т.руб |
Рис.11-г Налоговые отчисления по районам
Приложение к лабораторной работе «Работа с электронными таблицами MS EXCEL»
Основные сведения об MS EXCEL
1. Структура экрана
Окно приложения MS Excel обладает всеми элементами окон Windows: строка заголовка окна, кнопки управления размерами окна, системное меню. В главном меню Файл, Правка и т.д. функционально скомпонованы команды, необходимые для работы с электронными таблицами. Пункт меню Вид содержит список команд, определяющих внешний вид экрана. Команда Панели инструментов... позволяет вывести на экран/убрать с экрана панели инструментов: стандартную, форматирования, рисования и т.д. Вывод на экран Строки формул и Строки состояния также управляется из меню Вид. В режиме Полный экран на экране остается только строка главного меню. Команда Масштаб позволяет увеличить (напр., 150%) или уменьшить (напр., 75%) изображение на экране.
Рабочая область окна MS Excel представляет собой таблицу, состоящую из строк и столбцов, на пересечение которых находятся ячейки. Строки заголовков содержат наименования столбцов (А, В, С...) и строк (1, 2, 3...). Количество строк и столбцов в электронной таблицу ограничено только ресурсами компьютера. Для просмотра таблицы используются вертикальная и горизонтальная полосы прокрутки. Стиль адресных ссылок, наличие или отсутствие полос прокрутки, сетка и т.д. задаются командой Параметры из меню Сервис.
2. Рабочая книга
Документ, создаваемый в MS Excel — файл рабочей книги. Рабочая книга состоит из заданного по умолчанию (Сервис\Параметры\Основные) количества листов, ярлыки которых присутствуют на экране. Переход по листам осуществляется с помощью мыши. Состав рабочей книги может изменяться: листы добавляются (Вставка\Рабочий лист), удаляются (Правка\Удалить лист), переименовываются (Формат\Лист). Вы можете скопировать, переместить их в пределах данной рабочей книги или в другую книгу — Правка\Переместить или Скопировать. Команда Формат\Лист\Скрыть или Показать позволяет не выводить рабочий лист на экран или, наоборот показать его. Список этих команд приведен также в контекстном меню, которое вызывается щелчком правой кнопки мыши на ярлыке листа (см. Рис.1).
Рис.1.
3. Ввод и редактирование данных
Курсор экрана в электронной таблице имеет форму прямоугольника и указывает на активную ячейку. В ряде случаев приходится иметь дело не с одной ячейкой, а с интервалом ячеек. Выделение фрагмента рабочего листа выполняется с помощью мыши (при этом цвет первой ячейки интервала в отличие от остальных не изменяется). Выделение несмежных ячеек выполняется при нажатой клавише Ctrl.
Для ввода данных необходимо установить курсор экрана в соответствующую ячейку, ввести данные с клавиатуры и закончить ввод нажатием клавиши Enter. Одни и те же данные можно ввести сразу в несколько ячеек: выделив нужные ячейки, введите данные в одну из них, а затем нажмите Enter. Для ввода данных в интервал ячеек выделите интервал. Затем последовательно вводите данные в ячейки этого интервала.
Некоторые данные можно заполнить автоматически (определенный ряд чисел, дни недели, названия месяцев). Для заполнения ряда чисел набираются первых два, выделяются левой кнопкой мыши и протягивают за левый нижний угол выделенной области (см.рис.2-а) на необходимое число ячеек. Для заполнения стандартных рядов необходимо набрать только одно значение (см.рис.2-б).
а) б)
Рис.2.
Преобразование данных
1. Формулы
Формулы являются удобным средством работы с данными электронной таблицы. С их помощью можно выполнять различные операции над данными, такие как сложение, умножение, сравнение и т.д. Простейшая формула — это комбинация постоянных значений и операторов, которая вводится в ячейку и предназначается для получения нового результирующего значения. Ввод формулы всегда начинается со знака равенства — «=».
При написании формулы используются:
Ø арифметические операторы («+» — сложение, «-» — вычитание, * умножение, «/» — деление, «%» — процент, «^» — возведение в степень),
Ø операторы сравнения («<» — меньше, «>» — больше, «=» — равно, «<>» — неравно, «<=» — меньше или равно, «>=» — больше или равно),
Ø оператор текста &, объединяющий два или более текстовых значения в единое текстовое значение.
В формуле MS Excel операторы с одинаковым приоритетом выполняются слева направо. Изменять порядок вычислений можно, объединяя выражения в формуле круглыми скобками. При выводе на экран MS Excel заполняет ячейки, содержащие формулы, их результирующими значениями. Формула отображается в строке формул в том случае, если ячейка выделена.
Если MS Excel не в состоянии произвести правильное вычисление формулы, в ячейку выводится ошибочное значение. Ошибочные значения всегда начинаются со знака # (см.табл.1):
Таблица 1
| Сообщение на экране | Ошибка в записи формулы |
| #ДЕЛ/0! | Деление на нуль |
| #Н/Д | Ссылка на недоступное значение |
| #ИМЯ? | Использование имени, нераспознаваемого MS Excel |
| #ПУСТО! | Неверное пересечение двух областей |
| #ЧИСЛО! | Неправильное использование числа |
| #ССЫЛКА! | Ссылка на недопустимую ячейку |
| #ЗНАЧЕН! | Использование неправильного аргумента или операнда |
| ##### | Результат вычислений не помещается в ячейке, необходимо расширить столбец |
При вычислении формул с использованием дат MS Excel производит расчет в сутках.
2. Ссылки
Ссылки на ячейки применяются для обозначения отдельных ячеек или групп ячеек и указывают, в каких ячейках искать значения, нужные для вычисления формулы. Существуют три типа ссылок (адресации): относительные, абсолютные и смешанные. Относительная ссылка (А1) указывает, как найти другую ячейку, начиная поиск с ячейки, в которой расположена формула. Абсолютная ссылка ($A$1) указывает точное месторасположение ячейки на рабочем листе. Смешанные ссылки (A$1 или $A1) указывают, как найти другую ячейку на основе сочетания абсолютной ссылки на столбец и относительной ссылки на строку или наоборот. При вводе в формулу тип ссылки можно изменять, нажимая клавишу F4. Оператор ссылки позволяет задать в формуле ссылку на группу ячеек. Существуют три типа операторов ссылок:
Ø оператор интервала (двоеточие) определяет одну ссылку на все ячейки, расположенные между первой и последней ссылками;
Ø оператор объединения (точка с запятой) определяет одну ссылку, объединяя ячейки, на которые указывают две введенные ссылки;
Ø оператор пересечения (пробел) определяет одну ссылку на ячейки, общие для двух ссылок.
Ссылки на ячейки в формулу можно вводить как с клавиатуры, так и с помощью мыши, что является более удобным — щелчок в ячейке или выделение фрагмента подвижной рамкой при протаскивании указателя мыши через интервал ячеек. В формулу можно ввести ссылки на ячейки, расположенные на любом рабочем листе (=Лист3!А5), а также сформировать внешнюю ссылку на ячейки другой рабочей книги (=’[EX.XLS]Лист1’!$C$11). Объемная ссылка - это интервал, охватывающий два или более листа рабочей книги.
3. Функции
Функция — это специальная, заранее созданная в MS Excel формула, которая выполняет операции над заданными значениями и возвращает одно или несколько значений (см.рис.3).
Рис.3.
Функции можно использовать в качестве составных частей сложных формул. Применение функций на рабочих листах упрощает и сокращает длинные формулы. Функцию можно вводить в формулу непосредственно с клавиатуры, однако удобнее выполнять эту операцию с помощью Мастера функций 
.
Все встроенные функции MS Excel разделены на категории, названия которых соответствуют типу включенных в них функций (см.рис.4).
Рис.4.
Мастер функций, позволяющий в пошаговом режиме ввести функцию в формулу, дает пояснения о назначении функции, ее аргументах.
