Использование видеоуроков при изучении работы со списками в электронных таблицах Excel

Список предприятий, подлежащих реконструкции

Аэропорт

Бытовое обслуживание

№

Организация

Вид деятельности

Площадь

1

ООО Успех

Химчистка

120

2

ЗАО Удача

Парикмахерская

200

3

ООО Игрок

Химчистка

300

Торговля

1

ООО Фиалка

Цветы

30

2

ЗАО Бублик

Хлеб

100

3

ООО Ирис

Цветы

20

Сокол

Бытовое обслуживание

1

ООО Ботинок

Ремонт обуви

30

2

ООО Волос

Парикмахерская

50

Торговля

1

ООО Мороз

Бытовая техника

300

2

ЗАО Калач

Хлеб

100

3

ООО Роман

Книги

200

4

ЗАО Селедка

Рыба

100

Ш 1. Создайте список товаров и оформите ее по образцу.

Изделия

Категория

Цена

Количество

Стоимость

Примечание

Кресло

Мебель

782.00р.

2.0

Картофель

Продукты

10.75р..

95.0

Кофеварка

Техника

218.42р

4.0

Тумбочка

Мебель

427.60р.

3.0

Утюг

Техника

284.30р

4.0

Вермишель

Продукты

19.44р.

12.0

Стул

Мебель

111.45р.

4.0

Сахар

Продукты

24.80р.

16.8

Калькулятор

Техника

333.33р.

3.0

Капуста

Продукты

54.70р.

14.4

Паяльник

Техника

74.90р.

6.0

Стол

Мебель

542.00р.

2.0

2. Заполните столбец Стоимость.

3. Выполните сортировку списка в порядке возрастания сначала по столбцу Категория, затем по столбцу Изделия, а потом по столбцу Стоимость.

4. Отфильтруйте те изделия, которые начинаются на букву К и имеют цену, большую 300р.

5. Скопируйте отфильтрованные данные на лист Фильтр.

6. Проведите промежуточные итоги по Категории, вычислив общую стоимость.

Тестирование по теме: "Электронная таблица Microsoft Excel".

I вариант

1.Укажите правильный адрес ячейки:

А) А12С

Б) В1256

В) 123С

Г) В1А

2. В электронных таблицах выделена группа ячеек А1:В3. Сколько ячеек входит в этот диапазон?

А) 6

Б) 5

В) 4

Г) 3

3.Результатом вычислений в ячейке С1 будет:

А

В

С

1

5

=А1*2

=А1+В1

А) 5

Б) 10

В) 15

Г) 20

4.В ЭТ нельзя удалить:

А) столбец

Б) строку

В) имя ячейки

Г) содержимое ячейки

5.Основным элементом ЭТ является:

А) ячейка

Б) строка

В) столбец

Г) таблица

6.Укажите неправильную формулу:

А) А2+В4

Б) =А1/С453

В) =С245*М67

Г) =О89-К89

7.При перемещении или копировании в ЭТ абсолютные ссылки:

А) не изменяются;

Б) преобразуются вне зависимости от нового положения формулы;

В) преобразуются в зависимости от нового положения формулы;

Г) преобразуются в зависимости от длины формулы.

8. Диапазон - это:

А) все ячейки одной строки;

Б) совокупность клеток, образующих в таблице область прямоугольной формы;

В) все ячейки одного столбца;

Г) множество допустимых значений.

9. Электронная таблица - это:

А) прикладная программа для обработки кодовых таблиц;

Б) устройство персонального компьютера, управляющее его ресурсами;

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

Г) системная программа, управляющая ресурсами персонального компьютера при обработке таблиц.

10. Какая формула будет получена при копировании в ячейку D3, формулы из ячейки D2:

А) =А2*$С$2;

Б) =$A$2*C2;

В) =A3*$C$2;

Г) = A2*C3.

Тестирование по теме: "Электронная таблица Microsoft Excel".

II вариант

1. Укажите правильный адрес ячейки:

А) 12А

Б) В89К

В) В12С

Г) О456

2. В электронных таблицах выделена группа ячеек А1:С2. Сколько ячеек входит в этот диапазон?

А) 6

Б) 5

В) 4

Г) 3

2. Результатом вычислений в ячейке С1 будет:

А

В

С

1

5

=А1*3

=А1+В1

А) 5

Б) 10

В) 15

Г) 20

4.В ЭТ формула не может включать в себя:

А) числа

Б) имена ячеек

В) текст

Г) знаки арифметических операций

5.В ЭТ имя ячейки образуется:

А) из имени столбца

Б) из имени строки

В) из имени столбца и строки

Г) произвольно

6.Укажите неправильную формулу:

А) =О45*В2

Б) =К15*В1

В) =12А-В4

Г) А123+О1

7.При перемещении или копировании в ЭТ относительные ссылки:

А) не изменяются;

Б) преобразуются вне зависимости от нового положения формулы;

В) преобразуются в зависимости от нового положения формулы;

Г) преобразуются в зависимости от длины формулы.

8. Активная ячейка - это ячейка:

А) для записи команд;

Б) содержащая формулу, включающую в себя имя ячейки, в которой выполняется ввод данных;

В) формула в которой содержит ссылки на содержимое зависимой ячейки;

Г) в которой выполняется ввод данных.

9. Электронная таблица предназначена для:

А) обработки преимущественно числовых данных, структурированных с помощью таблиц;

Б) упорядоченного хранения и обработки значительных массивов данных;

В) визуализации структурных связей между данными, представленными в таблицах;

Г) редактирования графических представлений больших объемов информации.

10. Какая формула будет получена при копировании в ячейку D3, формулы из ячейки D2:

А) =А2*С2;

Б) =$A$2*C3;

В) =$A$2*$C$3;

Г) = A2*C3.

Автор

Год

Название

Музей

Страна

1

Э. Мане

1863

Завтрак на траве

Орсе

Франция

2

А.Саврасов

1871

Грачи прилетели

Третьяковская

галерея

Россия

3

И.Репин

1879

Царевна Софья

Третьяковская

галерея

Россия

4

В.Васнецов

1881

Аленушка

Третьяковская

галерея

Россия

5

П.Ренуар

1881

Девушка с веером

Эрмитаж

Россия

6

П.Пикассо

1937

Генрика

Прадо

Испания

7

И.Репин

1870

Бурлаки на Волге

Русский музей

Россия

8

Э.Мане

1863

Олимпия

Орсе

Франция

Название

Площадь,

тыс. км2

Население,

тыс. чел

Столица

Население

столицы, тыс. чел

1

Болгария

110,9

8470

София

1100

2

Венгрия

93

10300

Будапешт

2000

3

Греция

132

10300

Афины

748

4

Испания

504

39100

Мадрид

3100

5

Люксембург

2,6

392

Люксембург

75

6

Хорватия

56,6

4800

Загреб

707

7

Словакия

4,9

5800

Братислава

441

8

Словения

20,3

1990

Любляна

323

Фамилия

Страна

Год присуждения

Область деятельности

1

Э. Резерфорд

Великобритания

1908

Физика

2

Ж.Алферов

Россия

2001

Физика

3

Л.Ландау

СССР

1962

Физика

4

И.Мечников

Россия

1908

Физиология

5

М.Шолохов

СССР

1965

Литература

6

В.Гейзенберг

Германия

1932

Физика

7

Н.Семенов

СССР

1956

Химия

8

Б.Шоу

Великобритания

1925

Литература

Наименование

продукта

Белки

Жиры

Углеводы

Количество

калорий

1

Хлеб ржаной

5,5

0,6

39,3

190,0

2

Говядина

16,0

4,3

0,5

105,0

3

Судак свежий

10,4

0,2

0

44,0

4

Картофель св.

1,0

0,1

13,9

63,0

5

Капуста св.

0,9

0,1

3,5

20,0

6

Белые грибы

33,0

13,6

26,3

224,2

7

Рыжики сол.

21,85

3,75

47,75

183,7

Название

Вид

Страна

Скорость

км/ч

Масса,

т

Дальн.

км

1

Апач

Вертолет

США

310

8,0

690

2

КА 50 «Черная акула»

Вертолет

СССР

390

10

460

3

Мираж 2000

Истребитель

Франция

2450

7,4

3900

4

F -4E Фантом

Истребитель

США

2300

13,7

4000

5

Хок

Штурмовик

Великобритания

1000

3,6

3150

6

Мираж 4А

бомбардировщик

Франция

2350

14,5

4000

7

Торнадо

Истребитель

ФРГ-Великобритания

2350

14,09

5000

8

К52 «Аллигатор»

Вертолет

Россия

350

10,04

520

Название

Символ

Год

открытия

Автор

Место открытия

1

Америций

Am

1945

Г. Сиборг

США

2

Дубний

Db

1970

Г.Н.Флёров

СССР

3

Германий

Ge

1886

К. Винклер

Германия

4

Полоний

Po

1898

Склодовская-Кюри

Франция

5

Рутений

Ru

1844

К.Клаус

Россия

6

Галлий

Ga

1875

Ф. Лекон де Буабодран

Франция

7

Водород

H

1766

Кавендиш

Англия

8

Радий

Ra

1998

Склодовская-Кюри

Франция

вопрос

варианты

1

2

3

4

5

6

1

г

а

а

а

б

г

2

б

г

б

в

б

в

3

г

в

а

д

г

а

4

в

г

в

г

г

г

5

1,8,6,7,

4,2,3,5

1,4,5,8,

6,2,7,3

5,8,1,2,

3,6,4,7

5,3,4,2,

7,1,6

7,3,6,1,

4,2,8,5

5,8,4,2,

3,6,7,1

№ этапа

Содержание этапа

Дозировка (мин)

1

Организационный момент

Здравствуйте. Сегодня мы продолжим изучать особенности программы MS Excel, познакомимся с такими понятиями как сортировка списков, их фильтрование и подведение промежуточных итогов. И в ходе работы выполним несколько практических заданий.

3

2

Объяснение нового материала

Программа MS Excel позволяет автоматически сортировать данные, фильтровать, подводить промежуточные итоги, проводить консолидацию списков.

Списки (слайд 2)

Практически вся информация, с которой вы работаете в электронных таблицах, представляет собой разнообразные списки. (Слайд 3) Под списками понимаются таблицы, содержащие. Как правило, однотипные данные. Например, список телефонов сотрудников, список студентов и их анкетные данные, список товаров.

Правила ведения списков: (слайд 4)

1) все столбцы должны иметь заголовки.

Каждый заголовок должен отличаться от основного содержания таблицы внешним оформлением (цветом, шрифтом, начертанием).

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

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

Таким образом, программа MS Excel позволяет автоматизировать работу. Если какое-то правило нарушено, то программа не выполнит конкретную работу со списками.

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

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

Сортировка (слайд 5)

Программа MS Excel предоставляет возможность сортировать данные внутри таблицы по вашему усмотрению. Сортировка - это упорядочение данных по возрастанию или по убыванию. (Слайд 6)

Порядок сортировки (слайд 7)

При сортировке по возрастанию в Microsoft Excel используется следующий порядок (при сортировке по убыванию этот порядок заменяется на обратный за исключением пустых ячеек, которые всегда помещаются в конец списка):

1) Числа сортируются от наименьшего отрицательного до наибольшего положительного числа.

2)даты - сортируются от самой ранней даты до самой поздней.

3) Текст, в том числе содержащий числа, сортируется в следующем порядке: Числа, Знаки, Латинский алфавит, Русский алфавит

0 1 2 3 4 5 6 7 8 9 ' - (пробел) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я

4)Пустые ячейки всегда помещаются в конец списка.

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

Сортировка может быть простейшей и усложненной. (Слайд 8)

Для простейшей сортировки необходимо: (слайд 9)

1. выделить ячейку в том столбце, по которому необходимо сортировать.

2. щелкнуть по кнопке (распологаются на панели инструментов «Стандартная»).

Теперь вместе выполним простейшую сортировку. Нам необходимо отсортировать студентов по столбцу Фамилия по возрастанию. Выделяем ячейку Фамилия и нажимаем на кнопку сортировать по возрастанию. Тоже самое можно проделать и с другими столбцами. Обратите внимание, что содержание каждой строки сохраняется, т. е если первоначально Виноградов учился в 36 группе на специальности Информатика, то и после сортировки эти данные сохраняются.

Усложненная сортировка предполагает до трех уровней сортировки. Для выполнения этого вида сортировки необходимо: (слайд 10)

1. Укажите любую ячейку в сортируемом списке.

2. Выберите команду Сортировка в меню Данные.

3. Укажите столбцы сортировки в полях Сортировать по, Затем по и В последнюю очередь, по.

Снова вернемся к нашему списку и теперь отсортируем его по 3 критериям: Фамилия, Имя, Отчество. Это необходимо тогда, когда данные в столбцах повторяются. Итак, выделяем 1 ячейку, даем команду Данные - Сортировка и выбираем необходимые настройки. Все столбцы сортируем по убыванию. Нажимаем ОК. что у нас получилось? Яблоков Павел первый в списке, Шишкин Тимофей -2. Шишкин Александр Константинович - 3, Шишкин Александр Викторович - 4 и так до конца в порядке уменьшения.

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

Для выполнения такой сортировки необходимо, чтобы ячейки в каждой строке имели одинаковый формат. (Слайд 11)

1. Укажите ячейку в сортируемом списке.

2 .Выберите команду Сортировка в меню Данные.

3. Нажмите кнопку Параметры. В окне Параметры сортировки в поле Сортировка по первому ключу можно выбрать требуемый порядок сортировки.

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

5. Выберите строки, по которым требуется отсортировать столбцы, в полях Сортировать по и Затем по в диалоговом окне Сортировка диапазона.

Показать на примере этой же таблицы в соответствии с алгоритмом.

Практическая работа

Садитесь за компьютеры. Открывайте файл Работа со списками - лист Учет товаров на складе. И выполняйте задание, которое приведено здесь же. Анализ выполненной работы.

Фильтры (слайд 12)

Садитесь за парты.

В электронных таблицах можно осуществлять поиск данных в соответствии с заданными условиями. Такие условия называются фильтром. (слайд 13) Простейшее средство фильтрации - автофильтр. Использование автофильтра позволяет легко и быстро найти для работы данные в диапазоне ячеек или столбце таблицы. Для его запуска (слайд 14) нужно выделить первую строку таблицы с заголовками столбцов и выполнить команду Данные - Фильтр - Автофильтр. После этого в ячейках, содержащих заголовки полей, появляются раскрывающие кнопки.

Показать на примере. Лист Фильтрование в соответствии с алгоритмом.

Щелчок на такой кнопке открывает доступ к списку вариантов фильтрации. Записи, не удовлетворяющие условию фильтрации, не отображаются. Если вы хотите отобразить на экране всех Шишкиных, то можно выбрать строку Шишкин. Команда Данные - Фильтр - Отобразить все позволяет отобразить все записи. Чтобы создать произвольный фильтр, следует в раскрывающемся списке выбрать пункт Условие. При этом откроется диалоговое окно «Пользовательский автофильтр» позволяющее задать более сложное условие фильтрации.

Виды условий отбора (слайд 15)

При настройке автофильтра можно использовать перечисленные ниже элементы условий:

ь Последовательность символов

ь Знаки подстановки

ь Значения сравнения

Последовательности символов (слайд 16)

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

При использовании текстовой константы в качестве условия отбора будут отобраны все строки с ячейками, содержащими текст, начинающийся с заданной последовательности символов. Например, при вводе условия «Ш» будут отобраны строки с ячейками, содержащими слова Шишкин и Шубин. Обращайте внимание на регистр символов.

Знаки подстановки (слайд 17)

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

Чтобы найти

Используйте знак

Пример

Любой символ в той же позиции, что и знак вопроса

? (знак вопроса)

д?м задает поиск "дым" и "дом"

Любую последовательность символов в той же позиции, что и звездочка

* (звездочка)

*ино задает поиск "Люблино" и "Выхино"

Значения сравнения (слайд 18)

Чтобы отобрать строки с ячейками, имеющими значения в заданных границах, следует использовать оператор сравнения: Равно, меньше, больше, Меньше или равно, больше или равно. Например, чтобы отобрать строки, имеющие значения ячеек большие или равные 15, введите условие отбора >=15. Для текстовых величин возможны операции сравнения равно, начинается с (сравниваются первые символы), заканчивается на (сравниваются последние символы), содержит (сравниваются символы в любой части текста). Например, нужно отобрать фамилии, которые не содержат буквы И. Выбираем условие Не содержит и рядом в поле вводим и. Нажимаем Ок. Программа выдает фамилии не содержащие буквы И. Обратите внимание на условие И/ИЛИ. Оператор И используется, когда необходимо чтобы выполнялись оба условия, а ИЛИ - хотя бы одно из двух. Нам нужно выбрать фамилии, которые содержат Букву И и букву Е одновременно. Результат: Елкин и Березкин. А если выберем условие ИЛИ, то получим совершенно другой результат.

Отобранные записи - После применения фильтра в списке остаются только строки, содержащие указанное значение. В Microsoft Excel фильтрованные объекты выделяются специальным образом. Номера отфильтрованных строк становятся голубыми. Стрелки автофильтра в столбцах, по значению которых отбирались строки, также выделяются голубым.

Чтобы отменить использование автофильтра, надо повторно дать команду Данные - Фильтр - Автофильтр. (слайд 19)

Практическая работа.

Теперь попробуйте выполнить фильтрование списков самостоятельно по заданию на листе Характеристики планет. Не забывайте при переходе к следующему заданию выбирать команду отобразить все. Анализ выполненной работы.

Подведение промежуточных итогов (слайд 20)

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

Алгоритм подведения итогов:

1) отсортировать таблицу

2) выделить ячейку в списке

3) дать команду Данные - Итоги

4) выбрать необходимые параметры

5) нажать ОК

Показать на примере таблицы Учет товаров на складе в соответствии с алгоритмом. Предварительно отсортировать столбцы Организация и товар по возрастанию.

Далее в диалоговом окне Промежуточные итоги мы указываем имя столбца по которому подводим итоги - При каждом изменении в (Организация), выбираем операцию, причем программа уже сама предлагает набор стандартных операций (сумма) и указываем в каких столбцах добавить итоги (Цена, Стоимость). Флажки не менять. После подведения итогов обратите на поле слева. Там появились + и -. Где вы уже сталкивались с подобным явлением? (иерархическая структура). Если вам необходимо просмотреть только итоги, удобно воспользоваться этой функцией. Итоги можно копировать и вставить на другие листы.

Практическая работа.

Выполните задание. Приведенное на листе Данные об учащихся. Анализ выполненной работы