Итог заполнения бланка 1. ⇐ ПредыдущаяСтр 8 из 8 Рис. 41 Обратите внимание, что если для построения выражения социальной группы использовать возраст с учётом даты (ДАТА), т.е. столбец F, то ничего не получится, так как тип данных в этом столбце не является числом.     Самостоятельная работа 9. Задание. В таблице есть три отдельных столбца с фамилиями, именами и отчествами сотрудников. Создать ещё один столбец, в котором средствами мастера функций  автоматически будут формироваться фамилии с инициалами (табл. 6). Таблица 6 №пп Фамилия	Имя	Отчество	ФИО
1	Иванов	Иван	Иванович	Иванов И.И.
2	Петров	Пётр	Петрович	Петров П.П.
3	Никитин	Николай	Иванович	Никитин Н.И.
4	Николаева	Людмила	Петровна	Николаева Л.П.
5	Федосова	Нина	Петровна	Федосова Н.П.

Пояснения к выполнению.

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

Логика формирования записи в столбце ФИО следующая:

=Фамилия &” “&ЛЕВСИМВ(Имя, 1)&”.“&ЛЕВСИМВ(Отчество, 1)&”.”

Знак & означает сложение строк. В кавычках записаны пробел и точки соответственно.

Фильтры.

    Фильтрация относится к процедурам анализа баз данных, т.е. позволяет из основной таблицы (базы данных) создавать вторичные таблицы, содержащие записи, удовлетворяющие заданным условиям. Чтобы войти в режим фильтрации в EXCEL, нужно выделить область заголовков, затем выбрать пункт меню ДАННЫЕ - ФИЛЬТР – АВТОФИЛЬТР. Следует иметь в виду, что если таблица имеет сложную многоуровневую шапку, то нужно выделять область нижних заголовков.

    Инструмент АВТОФИЛЬТР позволяет фильтровать только по одному столбцу (полю). При необходимости фильтровать по нескольким столбцам, следует использовать расширенный фильтр. Но всё-таки для сложного анализа баз данных лучше использовать специальную программу Access.

Упражнение 8. «Аукцион».

Задание. Создать таблицу по образцу рис. 42.

Выполнить следующие фильтрации :

1. Создать фильтр, в котором отображался список только покупателей из Киева скопировать фильтр под основной таблицей. Дать заголовок новой таблице «Фильтр по городу Киев».

2. Создать фильтр, в котором отображался список всех покупателей, принявших участие в торгах до 01.07, скопировать этот фильтр под предыдущим. Дать имя таблице «Фильтр по дате».

3. Сделать сводку по продаже книг №3. Дать имя «Фильтр по книге №3».

4. Создать сводку клиентов, сделавших самые большие закупки (Сумма>15 000) .Скопировать и дать имя «Фильтр по сумме».

5. Оформить таблицу с помощью команды автоформат (стиль Объёмный 2).

6. Отсортировать таблицу по алфавиту городов, а, в свою очередь, каждый город по дате.

7. Создать условное форматирование столбца "Сумма" по принципу: если сумма>10000,то цвет шрифта красный, а цвет фона жёлтый.

 

Рис. 42

1. Чтобы задать режим фильтрации, нужно выделить заголовки таблицы (область А2:Н2) и выбрать пункт меню ДАННЫЕ – ФИЛЬТР – АВТОФИЛЬТР. В результате в заголовках появятся стрелочки с разворачивающимися списками (рис. 43). Следует иметь в виду, что  если таблица имеет сложную шапку, то нужно выделять нижние заголовки.

Рис. 43

2. Чтобы выполнить фильтр по городу Киев, нужно развернуть список критериев в столбце «Город» и выбрать Киев. Скопировать новую таблицу ниже на листе под основной и дать имя «Фильтр по городу Киев».

3. Для выполнения фильтра по дате следует развернуть список критериев в столбце «Дата» и выбрать строку УСЛОВИЕ. Заполнить бланк условия в соответствии с рис. 44.

 

Рис. 44

4. Скопировать фильтр ниже по листу. Назвать таблицу «Фильтр по дате».

5. Фильтр по книге №3 выполнить аналогично фильтру по городу.

6. Фильтр по сумме выполнить с помощью бланка условия :

БОЛЬШЕ 15000

7. Скопировать фильтр ниже по листу. В результате получится список фильтров как на рис. 45.

8. Закончить фильтрацию. Выделить область А2:Н2 и выбрать пункт меню ДАННЫЕ – ФИЛЬТР – АВТОФИЛЬТР. При этом исчезнут галочки в шапке основной таблицы.

9. Выделить область А2:Н15 и пойти в пункт меню ФОРМАТ – АВТОФОРМАТ. Выбрать стиль «Объёмный 2».

10. Для выполнения сортировки выделить область А2:Н15 и выбрать пункт меню ДАННЫЕ – СОРТИРОВКА. Убедиться, что сортировка будет производиться по городу, а затем по дате. Так как выделена была вся таблица, перестроится не только столбец «Город» и «Дата», но и другие данные (рис. 46).

11. Для выполнения условного форматирования нужно выделить область Н2:Н15 и выбрать пункт меню ФОРМАТ – УСЛОВНОЕ ФОРМАТИРОВАНИЕ. Заполнить бланк условного форматирования, щёлкнув по кнопке Формат… . В разделе «Шрифт» задать красный цвет шрифта, а в разделе «Вид» заказать желтую заливку (рис. 47):

 

Рис. 45

 

Рис. 46

Рис. 47

Самостоятельная работа 10.

Задание. Составить таблицу в соответствии с табл. 7.

Создать фильтры (табл.8):

1. Всех товаров, поступивших после 01.04.

2. Всех товаров, имеющих цену прихода менее 20 р.

3. Товаров полностью реализованных, т.е. имеющих количество остатка 0.

4. Прибыльных товаров, имеющих прибыль более 200 р.

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

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

Пояснения к выполнению.

1. Сумма расхода подсчитывается следующим образом:

(Цена расхода)*(Количество расхода)

2. Количество остатка подсчитывается как:

(Количество прихода)-(Количество расхода)

3. Формула для суммы остатка:

(Количество остатка)*(Цена расхода)

4. Прибыль считается как :

(Сумма расхода )-(Сумма прихода)

5. При выполнении условного форматирования применить два условия.

№

НАИМЕНОВАНИЕ ТОВАРА

ЕДИНИЦА ИЗМЕРЕНИЯ

 

 

Дата поступления товара

ПРИХОД

РАСХОД

ОСТАТОК

ПРИБЫЛЬ

ЦЕНА ПРИХОДА КОЛ-ВО ПРИХОДА Сумма прихода ЦЕНА РАСХОДА КОЛ-ВО РАСХОДА Сумма расхода КОЛ-ВО ОСТАТКА СУММА ОСТАТКА

1

Зефир

упак.

10.янв

30 р.

15

450

 34

15

510 р.

0

0

60

2

Молоко

упак.

01.апр

15 р.

50

750

 21

43

 903р.

7

147

 153

3

Колбаса

кг

10.апр

140 р.

10

1 400

 164

9

1 476 р.

1

164

76

4

Сосиски

кг

12.апр

80 р.

12

960

97 

10

970 р.

2

194

10 

5

Пепси-кола

бут.

01.фев

27 р.

32

864

 34 

25

850 р.

7

238

- 14

6

Сметана

упак.

12.апр

12,00р.

20

240

 17,50

20

350 р.

0

0

 110

7

Песок сахарный

кг

15.мар

15,45р.

50

773

22,00

45

990 р.

5

110

 218

8

Сигареты "Прима"

упак.

25.мар

 5,00р.

100

500

8,00р

90

720 р.

10

80

220

 

 

 

 

 

 

 

 

 

 

 

Итого

832,5

 

 

Таблица 7

Таблица 8

Фильтр по дате
№	НАИМЕНОВАНИЕ ТОВАРА	ЕДИНИЦА ИЗМЕРЕНИЯ	Дата поступ-ления товара	ЦЕНА ПРИХОДА	КОЛ-ВО ПРИХОДА	Сумма прихода	ЦЕНА РАСХОДА	КОЛ-ВО РАСХОДА	Сумма расхода	КОЛ-ВО ОСТАТКА	СУММА ОСТАТКА	ПРИБЫЛЬ
3	Колбаса	кг	10.апр	140р.	10	1 400	164	9	1 476р.	1	164	76,0
4	Сосиски	кг	12.апр	80р.	12	960	97	10	970р.	2	194	10,0
6	Сметана	упак.	12.апр	12,00р.	20	240	17,5	20	350р.	0	0	110,0
Дешёвые товары
2	Молоко	упак.	01.апр	15р.	50	750	21	43	903р.	7	147	153,0
6	Сметана	упак.	12.апр	12,00р.	20	240	17,5	20	350р.	0	0	110,0
7	Песок сахарный	кг	15.мар	15,45р.	50	773	22	45	990р.	5	110	218,0
8	Сигареты "Прима"	упак.	25.мар	5,00р.	100	500	8,00р	90	720р.	10	80	220
Проданные товары
1	Зефир	упак.	10.янв	30р.	15	450	34	15	510р.	0	0	60,0
6	Сметана	упак.	12.апр	12,00р.	20	240	17,5	20	350р.	0	0	110,0
Прибыльные товары
7	Песок сахарный	кг	15.мар	15,45р.	50	773	22	45	990р.	5	110	218,0
8	Сигареты "Прима"	упак.	25.мар	5,00р.	100	500	8,00р	90	720р.	10	80	220

 

Дополнительные задания к лабораторной работе 2.

Самостоятельная работа 11.

Задание. Составить таблицу успеваемости учеников (табл. 9).

Создать подсчёт:

o успевающих на 4 и 5.

o количество отличников.

o хорошистов с одной 4 .

o успевающих без двоек,  с одной 3.

o количество двоечников.

Таблица 9

A

B

С

D

E

F

G

H

I

К

L

М

1

№пп

Фамилия

Математика

Физика

Русский

Химия

Физ-ра

Отличники

Хорошисты

Хорошисты с 1 четвёркой

⇐ Предыдущая12345678

Поделиться: