Обработка информации в списках.

 

1. Сохраните новую книгу на диск С:\Обучение\«ФИО» в папке Excell под именем Пример3
2. Создайте таблицу-список по образцу:

 

Представитель	Регион	Месяц	Объем	Описание
Иванов	Запад	Январь	$500	Звуковые карты
Петров	Север	Январь	$100	Звуковые карты
Сидоров	Юг	Февраль	$600	Звуковые карты
Андреев	Восток	Февраль	$200	Звуковые карты
Петров	Север	Январь	$1500	CD-ROM
Сидоров	Юг	Февраль	$1100	CD-ROM
Петров	Север	Февраль	$800	CD-ROM
Иванов	Запад	Февраль	$1200	Принтеры
Петров	Север	Январь	$2500	Принтеры

 

Выделите заголовки столбцов жирным шрифтом, числовые данные в столбце Объем должны иметь Денежный формат, в долларах США. Примените ко всей таблице автоформат Цветной2 (команда меню ФорматÞАвтоформат)..

 

Форма для ввода данных.

Для ввода данных с помощью формы выделите любую ячейку внутри таблицы и выполните команду меню ДанныеÞФорма. В появившейся форме нажмите кнопку Добавить

Занесите еще две записи в список

Сидоров	Юг	Январь	$120	Звуковые карты
Петров	Север	Февраль	$320	CD-ROM

 

1. Проверка данных при вводе.

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

- Выполните команду меню ДанныеÞПроверка. Перейдите на вкладку «Параметры».

- Установите условие проверки для типа данных «Дата», значения между 01.01.2000 и 01.02.2000.

- Перейдите на вкладку «Сообщение об ошибке», в поле «Сообщение», введите текст “Неправильная дата”.

- Попробуйте ввести в столбец Месяц неправильную дату.

 

1. Сортировка строк и столбцов.

- Выделите любую ячейку в столбце Объем.

- Выполните команду Данные / Сортировка.

- В списке «Сортировать по…» выберите заголовок первого столбца и тип сортировки «По убыванию».

 

1. Автофильтр.

- Выберите любую ячейку в списке. Выполните команду – Данные/Фильтр/Автофильтр. (В верхней ячейке каждого столбца появляется раскрывающийся список.)

- Из раскрывающегося списка в столбце Представитель выберите фамилию Петров. В таблице отобразятся только сведения о его продажах. Чтобы вернуться к полному списку, из раскрывающегося списка выберите (Все).

- Для того, чтобы отфильтровать список по нескольким представителям, выберите из вписка (Условие). В появившемся окне в выберите равно Петров, поставьте переключатель ИЛИ, ниже выберите равно Сидоров. Отсортируйте полученный список по столбцу Представитель по возрастанию.

- В столбце Объем задайте условие, при котором бы отображались объемы продаж на сумму более 120$. Добавьте к условиям сортировки (ДанныеÞСортировка) по Объему по возрастанию.

- Установите условие выбора для отображения сведений о продажах представителя Петров за январь.

 

Отмените все условия сортировки, выбрав из списка в столбце Представитель – (Все) и из вписка в столбце Объем – (Все).

 

1. Вывод промежуточных итогов.

- Отсортируйте список по столбцу «Представитель»

- Выполните команду ДанныеÞ Итоги. Для вывода данных об общей сумме продаж для каждого представителя в появившемся диалоговом окне установите следующие параметры):

 

 

Выделите цветной заливкой строки Всего по каждому представителю и Общий итог. Сохраните изменения в файле.

 

 

Практическое задание №8

 

Обработка данных метеостанции

Задание:

Имеется таблица, содержащая количество осадков в миллиметрах, построенная на основе наблюдений метеостанции г. Екатеринбурга. Наберите эту таблицу в тех же ячейках как на рисунке:

Таблица 1

	A	B	C	D
	Количество осадков (мм) Таблица построена на основании наблюдений Метеостанции г. Екатеринбурга
	Январь	37,2	34,5
	Февраль	11,4	51,3	1,2
	Март	16,5	20,5	3,8
	Апрель	19,5	26,9	11,9
	Май	11,7	45,5	66,3
	Июнь	129,1	71,5
	Июль	57,1	152,9	50,6
	Август	43,8	96,6	145,2
	Сентябрь	85,7	74,8	79,9
	Октябрь		14,5	74,9
	Ноябрь	12,5		56,6
	Декабрь	21,2	22,3	9,4

Определить для всей таблицы в целом:

1) минимальное количество осадков, выпавшее за 3 года;

2) суммарное количество осадков, выпавшее за 3 года;

3) среднемесячное количество осадков по итогам 3-летних наблюдений;

4) максимальное количество осадков, выпавшее за 1 месяц, по итогам 3-летних наблюдений;

5) количество засушливых месяцев за все 3 года, в которые выпало меньше 10 мм осадков.

 

Данные оформить в виде отдельной таблицы:

Таблица 2

	E	F	G
		Данные за 1992-1994 годы
		Макс. кол-во осадков за 3 года (мм)
		Мин. кол-во осадков за 3 года (мм)
		Суммарное кол-во осадков за 3 года (мм)
		Среднемесячное кол-во осадков за 3 года (мм)
		Кол-во засушливых месяцев за 3 года

Те же данные определить для каждого года и оформить в виде отдельной таблицы 3.

Дополнительно для каждого года определить:

1) количество месяцев в году с количеством осадков в пределах (>20; <80) мм;

2) количество месяцев с количеством осадков вне нормы(<10;>100) мм.

 

Для примера в таблице показан результат за 1992 год

 

Таблица 3

	E	F	G
		Данные за 1992-1994 годы
		Введите год:
		Макс. кол-во осадков в году (мм)	129,1
		Мин. кол-во осадков в году (мм)	11,4
		Суммарное кол-во осадков в году (мм)	531,7
		Среднемесячное кол-во осадков в году (мм)	44,308
		Кол-во засушливых месяцев (<10 мм) в году
		Кол-во месяцев в пределах (>20;<80 мм)
		Кол-во месяцев вне нормы (<10;>100 мм)

 

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

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

В нашей задаче область В5:D1б в исходной Таблице 1 можно рассматривать как двумерный массив из 3 столбцов и 12 срок, а данные по каждому году В5:В16; С5:С16; D5:D16 как одномерные массивы по 12 элементов каждый.

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

Ход работы:

1. Заполните Таблицу 1 согласно рисунку, и оформите ее по своему усмотрению.

2. Сохраните файл на диске в каталоге Мои документы под именем Сведения.

3. На том же листе создайте и оформите еще две таблицы, как показано на рис 2. и 3.

4. Произведите вычисления в Таблице 2:

G4=МАКС(B5:D16)

G5=МИН(B5:D16)

G6=СУММ(B5:D16)

G7=СРЗНАЧ(B5:D16)

Для определения количества засушливых месяцев за три года (ячейка G8) воспользуйтесь функцией СЧЕТЕСЛИ, которая подсчитывает количество непустых ячеек, удовлетворяющих заданному критерию внутри интервала.

Формат функции: СЧЕТЕСЛИ(интервал; критерии).

Воспользуйтесь мастером функций, на 2 шаге укажите интервал В5:D16 и критерий <10.

5. В Таблице 3 в зависимости от года, введенного в табл.2 должен обрабатываться тот или иной столбец табл.1. Для решения таких задач применяют логическую функцию ЕСЛИ.

Формат функции: ЕСЛИ(логическое выражение; выражение 1; выражение2)

Логическое выражение (в частном случае условное выражение), которое принимает одно из двух значений «истина» или «ложь».

В первом случае функция ЕСЛИ принимает значение выражение 1, а во втором значение выражение2.

Ячейку G11 отведите для ввода года и присвойте ей имя ГОД (команда Вставка,ÞИмяÞ Присвоить).

В ячейку G12 введите следующую формулу:

 

=ЕСЛИ(ГОД=1992;МАКС($B$5:$B$16);ЕСЛИ(ГОД=1993;МАКС($C$5:$C$16);

ЕСЛИ(ГОД=1994;МАКС($D$5:$D$16);"данные отсутствуют")))

Для вычисления данных в ячейках G13 – G16 скопируйте формулу из ячейки G12 и измените в ней функцию МАКС на требуемые по смыслу. В ячейке G16 смените функцию МАКС на СЧЕТЕСЛИ и добавьте критерий “<10”:

=ЕСЛИ(год=1992;СЧЁТЕСЛИ($B$5:$B$16;"<10");ЕСЛИ(год=1993;СЧЁТЕСЛИ

($C$5:$C$16;"<10");ЕСЛИ(год=1994;СЧЁТЕСЛИ($D$5:$D$16;"<10");"данные

отсутствуют")))

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

Представьте данные из Таблицы 1 в виде диаграммы. Выберите самостоятельно тип и параметры диаграммы и поместите ее на отдельном листе, который назовите Сводные данные.

Для определения количество месяцев в каждом году с количеством осадков в пределах (>20;<80) мм (ячейка G17) и в пределах (<10;>100) мм (G18) создайте вспомогательную таблицу 4 для определения месяцев с количеством осадков в пределах (>20;<80) мм (цифры в таблице набирать не надо, это результаты вычислений для проверки)

 

Таблица 4

	A	B	C	D	E
	Вспомогательная таблица для определения месяцев в году с количеством осадков в пределах (>20; <80)
	Сумма:

 

В ячейку В21 внесите формулу: =ЕСЛИ(И(В5>20;В5<80);1;0)

Заполните этой формулой ячейки В22:B32. В ячейках, где условие выполняется, появится 1.

В ячейке ВЗЗ подсчитайте сумму месяцев за 1992 г., удовлетворяющих этому условию.

Выделите ячейки В21:ВЗЗ и скопируйте формулы в область С21:DЗЗ. В ячейках СЗЗ и DЗЗ получится количество месяцев за 1993 и 1994 гг., удовлетворяющих условию (>20;<80).

Аналогично создайте вспомогательную таблицу для определения числа месяцев с количеством осадков в пределах (<10;>100) мм.

В ячейку G17 занесите формулу:

=ЕСЛИ(год=1992;ВЗЗ;ЕСЛИ(год=1993;СЗЗ;ЕСЛИ(год=1995;D33;"данные отсутствуют")))

Скопируйте эту формулу в ячейку G18 и отредактируйте адреса ячеек.

Проверьте работу всех формул.

Оформите таблицы (границы, заливка, цвет шрифта).