Сервис/Параметры/Вид/Формулы

Файл/Параметры страницы/размер бумаги/Альбомная

4) Уменьшите ширину столбцов, чтобы все столбцы таблицы помещались на странице. (внимание на штрих -пунктирную линию)

5) Проверьте результаты работы на первом и втором листе рабочей книги

Файл/Предварительный просмотр или кнопка

6) Сохраните созданную электронную книгу на диске Н.Файл/Сохранить как Имя файла –Практическая работа №15 (Вы сохранили первый и второй лист книги практической работы №15)

 

Практическая работа № 16
Работа с базой данных в MS EXCEL

 

Цели: Изучение информационной технологии работы с базой данных в MS EXCEL: Сортировка данных в таблице по одному ключу, работа с несколькими ключами, работа с АВТОФИЛЬТРОМ Составление итоговых отчетов

Задание 1.Создайте таблицу.

Заполните столбец «средний балл», воспользовавшись функцией СРЗНАЧ категории СТАТИСТИЧЕСКИЕ мастера функций.

№ п/п	Фамилия, И.О.	Код специаль-ности	Необходи-мость общежития	Ино-стран-ный язык	Матема-тика	Русск. яз	Средний балл	Результат зачисления
	Трофимова Л.Я.		да	фран
	Антонова М.И.		нет	нем
	Глебова М.Ф.		нет	фран
	Карасева В.Н.		да	фран
	Ямова У.Р.		да	нем
	Хромов К.Т.		нет	нем
	Уфимцев А.А.		нет	нем
	Леднев В.В.		да	англ
	Борисова И.Н.		нет	англ
	Мартов М.И.		нет	англ
	Золотов К.И.		нет	фран
	Чернов У.Г.		да	англ
	Николаева М.В.		да	фран
	Смирнов С.Ю.		нет	англ
	Петров Л.А.		нет	нем
	Иванов А.А.		да	англ
	Евдокимова Н.Ю.		нет	англ
	Цапник А.Д.		да	нем
	Буров К.К.		да	англ
	Власова Н.И.		да	нем

 

 

Задание 2. Сортировка по одному ключу

Отсортируйте данные в таблице по фамилиям (по возрастанию)

1) Выделите таблицу, включая заголовки

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

3) В раскрывающемся списке Сортировать по выберите Фамилия И.О.

4) Переключатель поставьте в положение По возрастанию

5) Нажмите ОК

 

 

Задание 3. Самостоятельно отсортируйте по иностранному языку (по возрастанию).

1) Скопируйте отсортированную таблицу на второй лист

2) Вставьте строку над скопированной таблицей и введите название таблицы Сортировка по иностранному языку

 

Задание 4. Использование автофильтра

Заполните столбец «результаты зачисления», считая проходным 3,5 балла

MS EXCEL позволяет производить выбор данных с помощью автофильтра

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

2) В меню Данные выберите команду Фильтр и в дополнительном меню Автофильтр. В каждой ячейке первой строки появится стрелка, обозначающая раскрывающийся список

3) Щелкните по стрелке Средний балл

4) В раскрывающемся списке выберите Условие

5) В окне диалога Пользовательский фил ьтр установите больше или равно 3,5.

6) В столбце «Результат зачисления» внесите зачислен.

7) Распространите запись с помощью маркера заполнения

8) Завершите работу Автофильтра (Для завершения работы Автофильтра выберите в меню Данные команду Фильтр и дополнительное меню Автофильтр (отожмите кнопку))

9) В пустые ячейки столбца «Результат зачисления» внесите незачислен (или воспользуйтесь Автофильтром. Выделите таблицу на листе 1 Данные/Фильтр/Автофильтр. Средний балл Условие/меньше 3,5. Внесите незачислен и распространите. Завершите работу Автофильтра.)

 

Задание 5. Используя Автофильтр получите список учащихся, сдавших математику на «5».

1) Результат скопируйте на второй лист и введите название таблицы Сдавшие на 5

 

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

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

1) Постройте таблицу «Итоговый отчет» на листе 3

 

Итоговый отчет

Код специальности	К-во чело-век	Ср. балл

2) Отсортируйте исходную таблицу на листе 1 в порядке возрастания кода специальности.

3) Выделите отсортированную таблицу на листе1.

4) В меню Данные выберите Итоги. Откроется диалоговое окно Промежуточные итоги.

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

6) Убедитесь, что в поле Операция выбрано количество.

7) Для того, чтобы найти количество поступающих на каждую специальность в поле Добавить итоги по установите флажок напротив код специальности (УБЕРИТЕ НЕНУЖНЫЕ ФЛАЖКИ).

8) Проверьте, что напротив строк Заменить текущие итоги и Итоги под данными установлены флажки.

9) Нажмите ОК

10) Скопируйте таблицу с итоговым отчетом по количеству поступающих на лист 3. Введите название таблицы Итоговый отчет количество поступающих

11) Внесите полученные промежуточные итоги в таблицу «Итоговый отчет» на листе 3

12) Удалите введенные промежуточные итоги в исходной таблице на листе 1 Для этого выделите исходную таблицу, выберите Данные-Итоги и щелкните кнопку Убрать все

Задание 7. Составьте итоговый отчет по среднему баллу по каждой специальности.

1) Выделите таблицу на листе1

Подсказка: Данные- Итоги -Промежуточные итоги. При каждом изменении Код специальности операция среднее. Добавить итоги по средний балл.

Проверить, что напротив строк Заменить текущие итоги и Итоги под данными установлены флажки. Нажать ОК. Скопировать таблицу с итоговым отчетом по среднему баллу на лист 3. Ввести название таблицы Итоговый отчет по среднему баллу

Внести полученные промежуточные итоги в таблицу «Итоговый отчет» на листе 3. Удалить введенные промежуточные итоги в исходной таблице на листе 1. Для этого выделить исходную таблицу, выбрать Данные-Итоги и щелкнуть кнопку Убрать все.

Задание 8. Используя Автофильтр создать список зачисленных.

1) Вставьте лист 4

2) В исходной таблице на листе 1 получите список зачисленных Данные/Фильтр/Автофильтр

3) Скопируйте и поместите на лист 4. Полученную на четвертом листе таблицу озаглавьте«Список зачисленных». (Выделить первую строку- Вставка/Строка)

Задание 9. Сортировка по нескольким ключам

MS EXCEL позволяет сортировать по нескольким ключам

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

1) Выделите таблицу «Список зачисленных» на листе 4, включая заголовки

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

3) В окне диалога Сортировка выберите первый ключ сортировки (Сортировать по) код специальности (по возрастанию) и второй ключ (Затем по) – средний балл (по убыванию).

4) Вставьте лист 5

5) Скопируйте отсортированную таблицу на лист 5. Ведите название таблицы Сортировка по двум ключам

 

Задание 10.Отсортируйте таблицу «Список зачисленных» на листе 4 по трем ключам:

1 ключ -код специальности (по возрастанию), 2 ключ —Иностранный язык (по возрастанию), 3 ключ -Фамилия И.О (по возрастанию).

1) Скопируйте отсортированную таблицу на лист 5. Ведите название таблицы Сортировка по трем ключам

Задание 11. С помощью Автофильтра создать и распечатать список нуждающихся в общежитии (из таблицы зачисленных на листе4), расположив их в алфавитном порядке (В полученном после Автофильтра списке сделать сортировку по фамилиям по возрастанию по первому ключу, по второму и третьему ключу- не сортировать).

1) Скопируйте отсортированную таблицу на лист 5. Ведите название таблицы Список нуждающихся в общежитии

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

1) Вставьте лист 6

2) Постройте таблицу Распределение по языкам на листе 6

Распределение по языкам

Иностранный язык	К-во человек
Английский
Немецкий
Французский

 

3) Отсортируйте Список зачисленных на листе 4 по иностранному языку

4) Подведите итоги. Данные/Итоги. При каждом изменении иностранный язык, операция -количество, добавить итоги по иностранный язык.

5) Скопируйте таблицу с итогами распределения по языкам на лист 6 Введите название таблицы Итоги распределения по языкам

6) Внесите полученные результаты в таблицу Распределение по языкам

Задание 13. Постройте круговую диаграмму, отражающую информацию таблицы «Распределение по языкам» на листе 6

На третьем шаге построения диаграммы в закладке Подписи данных поставьте переключатель на доли

Задание 14. Проверьте результаты работы на всех листах рабочей книги

Файл/Предварительный просмотр или кнопка (только таблицы третьего листа выходят за пределы страницы)

Сохраните созданную электронную книгу на диске Н.Файл/Сохранить как Имя файла –Практическая работа №16 (Вы сохранили шесть листов книги практической работы №16)

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