Занятие 10 (2 ч., лабораторное)

    Тема: Работа со списками в Excel.

 

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

1) первая строка списка должна содержать имена полей (не должно быть ни одной пустой ячейки);

2) список должен целиком помещаться на одном листе Excel (следовательно, максимальный размер списка 65535 строк и 256 столбцов);

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

    Среди традиционных операций со списком можно выделить:

1) сортировка записей;

2) подведение итогов;

3) подведение итогов путем консолидации;

4) структурирование списка;

5) фильтрация (отбор) записей.

    Сортировка – это упорядочивание строк списка в соответствии с содержимым определенных столбцов.

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

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

    Структурирование – это группировка строк или столбцов списка.

    Фильтрация – это отбор строк списка, отвечающих определенным критериям (будет рассмотрена на следующем занятии 11).

 

Ход работы

1. Создайте следующую таблицу на Листе1 новой рабочей книги Excel:

 

	A	B	C	D
1	Шифр группы	ФИО студента	Экзаменационная оценка	Дисциплина
2	01АСУ	Гаранина О.И.	4	Математика
3	01АСУ	Косиненко Н.С.	4	Математика
4	01ИСЭ	Козлов М.В.	5	Информатика
5	01ИСЭ	Матросова В.К.	5	Математика
6	00ФиК	Нугуманов Н.Ю.	4	Информатика
7	00ФиК	Одинцов Л.И.	3	Информатика
8	01ИСЭ	Воронин С.Л.	3	Информатика
9	01ИСЭ	Губарева М.В.	5	Математика
10	01АСУ	Степанов М.Ф.	5	Математика
11	01АСУ	Федорова А.Л.	5	Информатика
12	01ИСЭ	Ершов К.Б.	3	Информатика
13	01ИСЭ	Волчек Г.Н.	4	Математика
14	00ФиК	Романов Н.П.	4	Математика
15	00ФиК	Пугачев А.В.	4	Информатика
16	01ИСЭ	Волк М.Д.	3	Математика
17	01АСУ	Борисова В.П.	4	Информатика
18	01ИСЭ	Воронин С.Л.	5	Информатика
19	01АСУ	Носков Р.А.	5	Информатика
20	01АСУ	Орлов В.Д.	5	Математика
21	00ФиК	Сомов М.Ю.	4	Математика
22	00ФиК	Точилин Н.А.	5	Информатика
23	00ФиК	Ушакова А.Д.	3	Математика
24	01ИСЭ	Золотухин Н.Б.	3	Математика
25	01АСУ	Федотов М.К.	5	Математика
26	01ИСЭ	Воробей Е.Н.	2	Информатика
27	01ИСЭ	Иванов И.Л.	4	Математика
28	01ИСЭ	Балык Б.Д.	4	Информатика
29	01ИСЭ	Губарева М.В.	5	Информатика
30	01ИСЭ	Леонидова Л.Г.	4	Информатика
31	00ФиК	Родионов К.И.	4	Математика
32	01АСУ	Петрова В.Э.	3	Математика
33	01ИСЭ	Кочубей И.Г.	5	Математика
34	00ФиК	Галкин С.Л.	5	Математика
35	00ФиК	Громов К.Н.	3	Информатика
36	01АСУ	Мазуров А.З.	5	Информатика
37	01ИСЭ	Соловьев В.В.	4	Информатика
38	01АСУ	Мамонтов Д.В.	4	Математика
39	01АСУ	Клишин Е.Д.	5	Математика
40	00ФиК	Аринцев М.Ю.	4	Информатика
41	01ИСЭ	Пустовенский К.С.	4	Информатика
42	01ИСЭ	Корнева А.А.	3	Информатика
43	01АСУ	Тупикова В.Р.	3	Математика
44	00ФиК	Третьяк С.С.	4	Математика
45	01АСУ	Минский К.С.	5	Информатика
46	00ФиК	Бердников П.Д.	5	Математика
47	00ФиК	Соколов К.Н.	4	Информатика
48	01АСУ	Юрский Н.Е.	5	Математика
49	01ИСЭ	Шолохов К.Н.	3	Математика
50	01ИСЭ	Мишенин Д.В.	4	Информатика
51	00ФиК	Сергеев Н.Т.	3	Информатика

 

2. Присвойте Листу1 текущей рабочей книги имя Список: Фор м ат è Л ист è Пере и меновать.

3. Удалите все другие листы из текущей рабочей книги.

    Для этого:

· перейдите на удаляемый лист;

· для удаления листа используйте команду П равка è Удалить л ист.

4. Сохраните текущую рабочую книгу под именем lesson 6. xls: Ф айл è Сохранить к ак…

5. С использованием команды П равка è Пере м естить/скопировать лист… скопируйте лист Список в конец текущей рабочей книги.

    Для этого:

· перейдите на лист Список;

· выберите команду П равка è Пере м естить/скопировать лист… Отобразится диалоговое окно Переместить или скопировать;

· в диалоговом окне Переместить или скопировать в области П еред листом: выберите (переместить в конец); включите опцию Создавать к опию и нажмите на кнопке OK.

6. Присвойте созданному листу Список (2) текущей рабочей книги имя Сортировка: Фор м ат è Л ист è Пере и меновать.

7. На листе Сортировка осуществите сортировку списка.

    Для этого:

· установите курсор в любую ячейку списка;

· выберите команду Д анные è С о ртировка… Отобразится диалоговое окно Сортировка диапазона;

· в диалоговом окне Сортировка диапазона в области Сортировать по выберите Шифр группы и включите опцию по возраста н ию; в области Затем по выберите Дисциплина и включите опцию по возраста н ию; в области В последнюю очередь, по выберите ФИО студента и включите опцию по возраста н ию; в области Идентифицировать поля по включите опцию п одписям (первая строка диапазона) и нажмите на кнопке OK.

8. Сохраните текущую рабочую книгу: Ф айл è С охранить.

9. С использованием команды П равка è Пере м естить/скопировать лист… скопируйте лист Сортировка в конец текущей рабочей книги.

    Для этого:

· перейдите на лист Сортировка;

· выберите команду П равка è Пере м естить/скопировать лист… Отобразится диалоговое окно Переместить или скопировать;

· в диалоговом окне Переместить или скопировать в области П еред листом: выберите (переместить в конец); включите опцию Создавать к опию и нажмите на кнопке OK.

10. Присвойте созданному листу Сортировка (2) текущей рабочей книги имя Итоги: Фор м ат è Л ист è Пере и меновать.

11. На листе Итоги с использованием команды Д анные è И тоги… рассчитайте средние экзаменационные оценки по каждой группе и среднюю экзаменационную оценку по всем группам.

    Для этого:

· установите курсор в любую ячейку списка;

· выберите команду Д анные è И тоги… Отобразится диалоговое окно Промежуточные итоги;

· в диалоговом окне Промежуточные итоги в раскрывающемся списке П ри каждом изменении в: выберите Шифр группы; в раскрывающемся списке О перация: выберите Среднее; в списке До б авить итоги по: выберите Экзаменационная оценка; выключите опции З аменить текущие итоги и К онец страницы между группами; включите опцию Ито г и под данными и нажмите на кнопке OK.

    Слева от таблицы на служебном поле появятся символы структуры:

· показа деталей (кнопка «плюс»);

· скрытия деталей (кнопка «минус»);

· уровней структуры (кнопки «1», «2», «3» и т. д.

12. Дополнительно рассчитайте средние экзаменационные оценки по каждому предмету по каждой группе.

    Для этого:

· установите курсор в любую ячейку списка;

· выберите команду Д анные è И тоги… Отобразится диалоговое окно Промежуточные итоги;

· в диалоговом окне Промежуточные итоги в раскрывающемся списке П ри каждом изменении в: выберите Дисциплина; в раскрывающемся списке О перация: выберите Среднее; в списке До б авить итоги по: выберите Экзаменационная оценка; выключите опции З аменить текущие итоги и К онец страницы между группами и нажмите на кнопке OK.

13. Сохраните текущую рабочую книгу: Ф айл è С охранить.

14. С использованием команды П равка è Пере м естить/скопировать лист… скопируйте лист Сортировка в конец текущей рабочей книги.

    Для этого:

· перейдите на лист Сортировка;

· выберите команду П равка è Пере м естить/скопировать лист… Отобразится диалоговое окно Переместить или скопировать;

· в диалоговом окне Переместить или скопировать в области П еред листом: выберите (переместить в конец); включите опцию Создавать к опию и нажмите на кнопке OK.

15. Присвойте созданному листу Сортировка (2) текущей рабочей книги имя Консолидация: Фор м ат è Л ист è Пере и меновать.

16. На листе Консолидация с использованием команды Д анные è К онсолидация… рассчитайте количество записей по каждой группе и средние экзаменационные оценки по каждой группе.

    Для расчета количества записей по каждой группе:

· установите курсор в ячейку A53;

· выберите команду Д анные è К онсолидация… Отобразится диалоговое окно Консолидация;

· в диалоговом окне Консолидация в раскрывающемся списке Ф ункция: выберите Кол-во значений; в поле Сс ы лка: введите ссылку на диапазон ячеек A2:D51 листа Консолидация; в рамке Использовать в качестве имен: включите опцию значения л евого столбца и нажмите на кнопке OK.

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

· установите курсор в ячейку A57;

· выберите команду Д анные è К онсолидация… Отобразится диалоговое окно Консолидация;

· в диалоговом окне Консолидация в раскрывающемся списке Ф ункция: выберите Среднее; в списке С писок диапазонов: выберите ссылку на диапазон ячеек A2:D51 листа Консолидация в результате чего в поле Сс ы лка: появится ссылка на диапазон ячеек A2:D51 листа Консолидация; в рамке Использовать в качестве имен: включите опцию значения л евого столбца и нажмите на кнопке OK.

17. Сохраните текущую рабочую книгу: Ф айл è С охранить.

18. С использованием команды П равка è Пере м естить/скопировать лист… скопируйте лист Сортировка в конец текущей рабочей книги.

    Для этого:

· перейдите на лист Сортировка;

· выберите команду П равка è Пере м естить/скопировать лист… Отобразится диалоговое окно Переместить или скопировать;

· в диалоговом окне Переместить или скопировать в области П еред листом: выберите (переместить в конец); включите опцию Создавать к опию и нажмите на кнопке OK.

19. Присвойте созданному листу Сортировка (2) текущей рабочей книги имя Структура: Фор м ат è Л ист è Пере и меновать.

20. На листе Структура с использованием команды Вст а вка è Стро к и вставьте разделяющие строки для учебных групп.

21. Создайте структурные части таблицы для учебных групп.

    Для этого:

· выделите ячейки, относящиеся к первой группе (A2:D16);

· выберите команду Д анные è Г руппа и структура è Г руппировать… Отобразится диалоговое окно Группирование;

· в диалоговом окне Группирование включите опцию с т роки и нажмите на кнопке OK.

    Аналогичным образом создайте структурные части для ячеек таблицы, относящихся ко второй (A18:D32) и третьей (A34:D53) группе.

22. Сохраните текущую рабочую книгу: Ф айл è С охранить.