Установите в ячейках D3:D7 формат Процентный

2. Поместите курсор в ячейку D3 листа Премия и введите формулу: =ЕСЛИ(С3<5000;100%;ЕСЛИ(С3<10000;70%;50%)).

3. Скопируйте формулу в ячейки D4:D7.

Заполните ячейки столбца Начислено с помощью формулы: =C3*D3. Вычислите подоходный налог с помощью формулы: =Е3*0,13. Рассчитайте сумму премии в столбце К выдаче как разность между ячейками столбца Начислено и столбца Подоходный налог. Подсчитайте общую сумму премии. После этого ваша ведомость примет вид, показанный на рис. 1.3.

 

 

Рис.1.3.

 

А теперь рассчитаем количество сотрудников, получивших 100% от среднего оклада, 70% и 50%. Для этого используем дополнительные столбцы H, I, J. Выполните следующие действия:

1. Ведите в ячейку Н2 – 100%, I2 – 70%, J2 – 50%.

2. В ячейку Н3 введите формулу: =ЕСЛИ(D3=100%;1;0).

3. Скопируйте формулу в ячейки Н4:Н7.

4. В ячейку I3 введите формулу: =ЕСЛИ(D3=70%;1;0).

5. Скопируйте формулу в ячейки I4:I7.

6. В ячейку J3 введите формулу: =ЕСЛИ(D3=50%;1;0).

7. Скопируйте формулу в ячейки J4:J7.

8. Ячейки В10:В12 и D10:D12 заполните текстом, как показано на рисунке 4.

9. Присвойте имена блокам ячеек: Н3:Н7 – премия100, I3:I7 – премия70, J3-J7 – премия50.

10. В ячейку С10 введите формулу: =СУММ(премия100). При вводе операнда используйте команду Вставка/Имя/Вставить. Аналогичные формулы введите в ячейки С11:С12.

После этого ведомость начисления премии примет вид, показанный на рис. 1.4.

Рис. 1.4.

 

Индивидуальные задания.

Задание 1. При поступлении в институт абитуриенты сдают три экзамена. Результаты экзаменов оцениваются по пятибалльной системе. В институт зачисляют тех абитуриентов, у которых сумма баллов больше 12.

Необходимо подсчитать сумму баллов, набранную каждым абитуриентом, а также количество будущих студентов.

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

Задание 2. Школьники сдают ЕГЭ по четырем предметам. Результаты оцениваются по пятибалльной системе.

Необходимо вычислить средний балл каждого школьника, а также количество отличников.

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

Задание 3. В коммерческом вузе на первом курсе обучаются 4 группы студентов. В сессию студенты сдают 4 экзамена. Создать сводную ведомость, в которой рассчитать средний балл каждого студента. Примерный вид таблиц показан ниже.

№ группы

№п/п	Ф.И.О.	Эк.1	Эк.2	Эк.3	Эк.4

Сводная ведомость

№группы	№группы	№группы	№группы
№ п/п	Ф.И.О.	Сред. балл	№ п/п	Ф.И.О.	Сред. балл	№ п/п	Ф.И.О.	Сред. балл	№ п/п	Ф.И.О.	Сред. балл

Задание 4. В коммерческом вузе на первом курсе обучаются 3 группы студентов. Подсчитать количество отличников и количество неуспевающих студентов в каждой группе. Неуспевающим считается студент, имеющий неудовлетворительную оценку хотя бы по одному предмету. При решении задачи использовать логическую формулу ЕСЛИ вида: =ЕСЛИ(ИЛИ(А1=2;А2=2;А3=2);1;0). Эта формула поможет определить количество неуспевающих студентов. Для определения количества отличников вместо функции ИЛИ следует использовать функцию И.

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

№ группы

№п/п	Ф.И.О.	Эк.1	Эк.2	Эк.3	Эк.4

Количество отличников

Количество неуспевающих студентов

 

Сводная ведомость

№ группы	Количество отличников	Количество неуспевающих студентов

 

Задание 5. В фирме ежемесячно создаются отчеты о деятельности предприятия по показателям:

· Объем продаж, измеряемый в штуках.

· Цена изделия, выпускаемого фирмой в рублях.

· Доход, вычисляемый как произведение объема продаж на цену изделия.

· Расход, определяемый в рублях.

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

Определить суммарные значения показателей за квартал.

 

Задание 6. Магазин имеет 3 филиала. Каждый из филиалов ежемесячно присылает по электронной почте отчеты о своей деятельности в виде таблицы следующего вида:

 

Файлы, присылаемые магазинами, имеют соответственно имена: М1, М2, М3.

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

 

Задание 7. В спортивной школе занимаются 3 группы спортсменов. Определить количество спортсменов, имеющих первый, второй, третий разряд, а также количество мастеров спорта и кандидатов в мастера спорта.

Задание 8. В производственной фирме выпускают 5 видов изделий и отпускают их оптовым покупателям. Товары у фирмы приобретают 3 покупателя. Ведомость учета продаж имеет следующий вид:

 

 

Определить суммарное количество изделий, проданное каждой фирме за месяц.

 

Задание 9. В детской спортивной школе проводили соревнования по легкой атлетике. Каждый из участников набирал определенную сумму баллов по десятибалльной шкале за прыжки в длину, прыжки в высоту и бег на 100 метров. Результаты фиксировались в ведомостях следующего вида:

 

 

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

 

Задание 10. Предприятие изготавливает и продает товары оптовым покупателям и фиксирует продажи в ведомостях следующего вида:

 

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

 

 

Основы работы с таблицами как с базой данных.

Составление итоговых отчетов на основе технологии

табличного процессора Excel

 

В состав табличного процессора входит группа команд для работы с электронной таблицей как с базой данных:

· Данные/Сортировка

· Данные/Фильтр

· Данные/Итоги

· Данные/Сводная таблица.

Базы данных – это большие таблицы, состоящие из однородных строк.

Команда Сортировка предназначена для упорядочивания таблицы по значениям выбранного пользователем столбца. Текстовые данные при этом сортируются по алфавиту или в обратном порядке, числовые данные – в порядке убывания или возрастания. Блок сортировки указывается пользователем путем его выделения.

Команда Фильтрация служитдля поиска определенных строк по критерию, задаваемому пользователем.

Команды Итоги служит для автоматического подведения итогов.

Команда Сводная таблица предназначена для формирования сводных отчетов.

Упражнение 1.5. Создать базу данных поступления товаров от фирм: АЛЬФА, БЭТТА, ГАММА, как показано на рис. 1.5. Подготовить отчет поступления товаров по фирмам за январь 2002 года, как показано на рис. 1.6.

 

№ накладной

Дата

Фирма

Товар

Кол-во

Цена

Сумма

01.01.02

альфа

блокнот

30,00р.

300,00р.

01.01.02

альфа

зап.книжка

20,00р.

400,00р.

5/11

20.01.02

бэтта

кнопки

25,00р.

750,00р.

5/11

20.01.02

бэтта

ежедневник

50,00р.

250,00р.

25/11

25.01.02

гамма

скрепки

10,00р.

500,00р.

25/11

25.01.02

гамма

тетрадь

20,00р.

600,00р.

25/11

25.01.02

гамма

кнопки

35,00р.

525,00р.

25.01.02

бэтта

клей

20,00р.

200,00р.

25.01.02

бэтта

ежедневник

40,00р.

800,00р.

02.02.02

бэтта

кнопки

35,00р.

350,00р.

02.02.02

бэтта

ежедневник

55,00р.

550,00р.

25/0

05.02.02

гамма

тетрадь

20,00р.

500,00р.

25/0

05.02.02

гамма

кнопки

35,00р.

350,00р.

13/1

15.02.02

альфа

блокнот

30,00р.

150,00р.

13/1

15.02.02

альфа

зап.книжка

35,00р.

525,00р.

20.02.02

гамма

кнопки

40,00р.

200,00р.

Рис. 1.5.

 

Приход товаров за январь 2002 года

№ накладной

Дата

Фирма

Товар

Кол-во

Цена

Сумма

01.01.02

альфа

блокнот

30,00р.

300,00р.

01.01.02

альфа

зап.книжка

20,00р.

400,00р.

альфа Всего

700,00р.

5/11

20.01.02

бэтта

кнопки

25,00р.

750,00р.

5/11

20.01.02

бэтта

ежедневник

50,00р.

250,00р.

25.01.02

бэтта

клей

20,00р.

200,00р.

25.01.02

бэтта

ежедневник

40,00р.

800,00р.

бэтта Всего

2 000,00р.

25/11

25.01.02

гамма

скрепки

10,00р.

500,00р.

25/11

25.01.02

гамма

тетрадь

20,00р.

600,00р.

25/11

25.01.02

гамма

кнопки

35,00р.

525,00р.

гамма Всего

1 625,00р.

Общий итог

4 325,00р.

Всего на сумму четыре тысячи триста двадцать пять рублей 00 копеек

Рис. 1.6.

Технология работы.

1. Создайте файл (рабочую книгу) с именем Отчет.

2. Назовите Лист1 именем База данных.

3. Создайте шапку таблицы, как показано на рис.1.5.

4. Отформатируйте столбцы листа База данных:

· Столбцы A, C,D – текстовый;

· Столбец В – дата;

· Столбец Е – числовой (целые числа);

· Столбцы F,G – денежный (точность 2 знака после запятой).

5. Заполните базу данных как показано на рис.1.5. В окне Сервис/Параметры/Правка задайте команду Автозаполнение значений ячеек. Столбец СУММА заполните с помощью формулы умножения цены на количество.