Описание задачи
Подготовить документ о составе населения 10 крупнейших городов Российской Федерации, включающий информацию о количестве жителей, возрастном составе (5 групп). Подсчитать суммарное количество жителей в 10 крупнейших городах и процентное выражение этого количества по отношению к населению России. Построить диаграмму, отражающее число жителей в каждом из городов, и диаграмму, отражающую возрастной состав населения Петербурга в процентах. Упорядочить документ по числу населения.
Вывести на печать списки городов, количество жителей в которых в самой младшей и самой старшей возрастных группах превышает среднее количество по соответствующей группе (2 независимых списка).
Работу выполнить в электронных вычислительных таблицах Microsoft Excel.
Решение задачи
Подготовка документа
Для подготовки документа воспользуемся ресурсами глобальной сети Интернет, а конкретно сайтом «http://ru.wikipedia.org», из которого почерпнём интересующую нас информацию о десяти крупнейших городах Российской Федерации. Полученную информацию распределим на листе электронной книги Microsoft Excel таким образом, как это сделано в таблице 1 данного отчёта. Заметим, что в данной таблице данные размещены хаотично, а левый столбец и верхняя строка используются для адресации (фактически, это названия столбцов и номера строк в созданной ранее электронной книге).
Таблица 1 – Общий вид базы данных «10 крупнейших городов РФ»
B |
C |
D |
E |
F |
G |
H |
I |
|
4 |
№пункта |
Название города |
Количество Жителей [тыс. чел.] |
Возрастной состав |
||||
5 |
Группа I до 14 лет |
Группа II от 14 до 25 лет |
Группа III от 26 до 45 лет |
Группа IV от 46 до 70 лет |
Группа V свыше 70 лет |
|||
6 |
1 |
Казань |
1116 |
492 |
277 |
247 |
540 |
458 |
7 |
2 |
Челябинск |
1092 |
771 |
2952 |
1274 |
445 |
895 |
8 |
3 |
Ростов-на-Дону |
1052 |
223 |
2523 |
1088 |
479 |
222 |
9 |
4 |
Санкт-Петербург |
4571 |
164 |
918 |
1123 |
212 |
952 |
10 |
5 |
Нижний Новгород |
1278 |
977 |
2514 |
1195 |
647 |
86 |
11 |
6 |
Самара |
1139 |
790 |
1215 |
253 |
392 |
544 |
12 |
7 |
Омск |
1135 |
922 |
1020 |
1941 |
1363 |
287 |
13 |
8 |
Москва |
10443 |
196 |
2800 |
1851 |
552 |
929 |
14 |
9 |
Новосибирск |
1392 |
336 |
3050 |
2075 |
1083 |
33 |
15 |
10 |
Екатеринбург |
1315 |
1032 |
1473 |
441 |
285 |
277 |
Как видно из приведённой таблицы 1, возрастной состав населения городов разбит на 5 возрастных групп по признаку «от X до Y», где X и Y некоторые «граничные» возраста для каждой отдельно взятой группы. При помощи стандартных средств контекстного меню Excel (Пункт «Формат ячеек»), мы добились наиболее эргономичного отображения введённой информации, так например «Группа I» в исполнении шрифта Courier New из стандартной поставки Windows выглядит значительно лучше: «Группа I».
На этом завершим подготовительный этап и перейдём непосредственно к решению поставленных перед нами задач.
Пункт 1
Основная задача: подсчитать суммарное количество жителей в 10 крупнейших городах и процентное выражение этого количества по отношению к населению России.
Решение данной задачи будет весьма простым. Для начала, подсчитаем суммарное количество жителей по данным нашей таблицы. Для этого выделим ячейку E19 и введём в неё формулу «=СУММ(D6:D15)». Данная формула приводится с учётом адресации ячеек, принятой в таблице 1 данного отчёта. Она, что нетривиально, позволяет произвести подсчёт суммы элементов, заключённых в круглых скобках (в данном случае, это столбец «Количество жителей» таблицы 1). Для пояснения полученного значения, в ячейку D19 вводим строку «Суммарное количество жителей ∑N:» (символ «∑», как и все последующие, вводится вызовом диалогового окна «Вставка» → «Символ» из главного меню Excel), и установим выравнивание по правому краю. Для расчёта процентного отношения полученной суммы к общей численности населения РФ, выясним общую численность на всё том же интернет ресурсе, что и все прочие данные из текущей работы. Это число введём в ячейку E20. Подписью послужит строка «Население Российской федерации ∑NРФ
:» в ячейке D20 (выравнивание по правому краю). Для определения процентного соотношения мы имеем два варианта:
1. Ввод в ячейку E21 формулы «=E19/E20*100», что позволит получить долю в процентах числа в E19 от числа E20.
2. Прибегая к изменению формата ячейки E21 на «Процентный», вводим в неё формулу «=E19/E20», и получаем значение, уже переведённое в проценты, и, к тому же с дописанным символом «%» в конце.
В данной работе я склонна прибегнуть ко второму варианту решения задачи. Комментарием к полученному результату послужит строка «Процентное выражение ∑N в ∑NРФ
:» в ячейке D21 (выравнивание по правому краю). Хотелось бы отметить что установленные нами нижние индексы в ячейках D20 и D21 отформатированы при помощи контекстного меню «Формат ячеек». Результат описанных выше операций приведён в отчёте в виде таблицы 2.
Таблица 2 – Решение задачи о подсчёте итоговых показателей
C |
D |
E |
|
18 |
Итоговая информация: |
||
19 |
Суммарное количество жителей ∑N: |
24533 |
|
20 |
Население Российской федерации ∑NРФ
|
141 888,90 |
|
21 |
Процентное выражение ∑N в ∑NРФ
|
17,3% |
Пункт 2
Основная задача: построить диаграмму, отражающую число жителей в каждом из городов.
Рисунок 1 – Общий вид окна «Мастер диаграмм» (а) и набранные исходные данные диаграммы 1 (б)
Для выполнения первой операции вызываем пункт главного меню «Вставка» → «Диаграмма…», и попадаем в главное окно мастера диаграмм от Microsoft, приведённое на рисунке 1 (а). Выбираем пункт «Гистограмма», «Объёмный вариант обычной гистограммы», и нажимаем на кнопку «Далее». В появившемся окне переходим на вкладку «Ряд», где задаём имена рядов (отдельные ячейки диапазона C6:C15) и значения (отдельные ячейки диапазона D6:D15), соответственно. Это сложный способ построения диаграммы, однако, результат себя оправдывает: наборные диаграммы зачастую выглядят гораздо лучше автоматически сформированных. После правильного формирования списка, окно примет вид, отображённый на рисунке 1 (б) данного отчёта. В этом случае, нажимаем кнопку «Далее», и вводим название диаграммы «Диаграмма 1: Число жителей по городам». Затем нажимаем на кнопку «Готово», и диаграмма размещается на нашем рабочем листе. Для улучшения визуального эффекта произведена следующая манипуляция: все чётные столбики обращены в цилиндры посредством контекстного меню «Формат рядов данных…» → «Фигура». Полученная таким образом диаграмма приводится на рисунке 2 данного отчёта.
Рисунок 2 – Полученная диаграмма 1 по заданию
Пункт 3
Основная задача: построить диаграмму, отражающую возрастной состав населения Петербурга в процентах.
Для выполнения данного задания проведём подготовительный этап. Если пользователь упорядочит данные в исходной таблице, то диаграмма, которая строилась для Санкт-Петербурга, может оказаться построенной уже для Москвы или любого другого города. Чтобы избежать этого, произведём выборку строки исходной таблицы в отдельный участок листа таким образом, чтобы при перемещении позиции «Санкт-Петербург» в исходной базе данных, Excel автоматически определял её позицию и выводил соответствующие данные, и уже для этого участка листа будем строить диаграмму. Для выполнения поставленного перед собой условия, прибегну к формулам Excel. Для начала скопируем «шапку» исходной таблицы в диапазон B50:I51. После этой шапки создадим одну пустую (пока) строку, в которую и предполагается производить выборку.
В поле «Название города» (ячейка C52) скопируем название «Санкт-Петербург». В данной автовыборке это поле будет являться ключевым. Далее вводим во все остальные ячейки строки в переделах шапки таблицы формулу «=ИНДЕКС(B6:I15;ПОИСКПОЗ(C52;C6:C15;0);XX)», где XX – номер столбца, данные для которого мы хотим поместить в текущую ячейку (число от 1 до 8, минуя 2). Поясним структуру применённых формул, проще говоря, их синтаксис:
ПОИСКПОЗ(искомое_значение; просматриваемый_массив; тип_сопоставления)
искомое_значение – это значение, которое сопоставляется со значениями в аргументе просматриваемый_массив, который содержит некий диапазон ячеек. Аргумент тип_сопоставления задаёт тип логического сравнения элементов.
В приведённом примере использования, данная функция возвращает из массива C6:C15 относительный номер строки, в которой встречается элемент C52.
ИНДЕКС(массив;номер_строки;номер_столбца)
массив – это ссылка на некий диапазон ячеек. Аргументы номер_строки и номер_столбца определяют выходные данные.
В приведённом примере использования, данная функция возвращает из массива C6:C15 элемент, лежащий в строке с номером, определённым функцией ПОИСКПОЗ(), и столбце, введённым нами вручную.
Построенная таблица приведена в данном отчёте как таблица 3.
Таблица 3 – Выборка по ключевому полю «Название города»
B |
C |
D |
E |
F |
G |
H |
I |
|
50 |
№ пункта |
Название города |
Количество Жителей [тыс. чел.] |
Возрастной состав |
||||
51 |
Группа I до 14 лет |
Группа II от 14 до 25 лет |
Группа III от 26 до 45 лет |
Группа IV от 46 до 70 лет |
Группа V свыше 70 лет |
|||
52 |
4 |
Санкт-Петербург |
4571 |
164 |
918 |
1123 |
212 |
952 |
Для построения диаграммы теперь остаётся просто выделить диапазон E51:I52, и произвести построение круговой диаграммы при помощи «Мастера диаграмм». Результат описанных действий приведён на рисунке 3 отчёта.
Рисунок 3 – Полученная диаграмма 2 по заданию
Пункт 4
Основная задача: упорядочить документ по числу населения.
Для осуществления сортировки воспользуемся стандартным окном «Сортировка» электронных таблиц Excel. Для этого выделяем диапазон C6:I15 и переходим из главного меню «Данные» → «Сортировка». В появившемс
Таблица 4 – Общий вид базы данных «10 крупнейших городов РФ». Произведена сортировка.
B |
C |
D |
E |
F |
G |
H |
I |
|
4 |
№ пункта |
Название города |
Количество Жителей [тыс. чел.] |
Возрастной состав |
||||
5 |
Группа I до 14 лет |
Группа II от 14 до 25 лет |
Группа III от 26 до 45 лет |
Группа IV от 46 до 70 лет |
Группа V свыше 70 лет |
|||
6 |
1 |
Ростов-на-Дону |
1052 |
223 |
2523 |
1088 |
479 |
222 |
7 |
2 |
Челябинск |
1092 |
771 |
2952 |
1274 |
445 |
895 |
8 |
3 |
Казань |
1116 |
492 |
277 |
247 |
540 |
458 |
9 |
4 |
Омск |
1135 |
922 |
1020 |
1941 |
1363 |
287 |
10 |
5 |
Самара |
1139 |
790 |
1215 |
253 |
392 |
544 |
11 |
6 |
Нижний Новгород |
1278 |
977 |
2514 |
1195 |
647 |
86 |
12 |
7 |
Екатеринбург |
1315 |
1032 |
1473 |
441 |
285 |
277 |
13 |
8 |
Новосибирск |
1392 |
336 |
3050 |
2075 |
1083 |
33 |
14 |
9 |
Санкт-Петербург |
4571 |
164 |
918 |
1123 |
212 |
952 |
15 |
10 |
Москва |
10443 |
196 |
2800 |
1851 |
552 |
929 |
Пункт 5
Основная задача: вывести на печать списки городов, количество жителей в которых в самой младшей и самой старшей возрастных группах превышает среднее количество по соответствующей группе (2 независимых списка).
Для подготовки данного пункта определим средние значения по первой и пятой возрастным группам при помощи встроенной функции СРЗНАЧ(), и запишем их в ячейки E16 и I16 соответственно (E16 = СРЗНАЧ(E6:E15) и I16 = СРЗНАЧ(I6:I15)). Подпишем полученную строку как «Средние значения:» в ячейке D16. Таблица примет вид, показанный в таблице 5 отчёта.
Таблица 5 – Вычисление средних показателей по полям «Группа I» и «Группа V».
B |
C |
D |
E |
F |
G |
H |
I |
|
4 |
№ пункта |
Название города |
Количество Жителей [тыс. чел.] |
Возрастной состав |
||||
5 |
Группа I до 14 лет |
Группа II от 14 до 25 лет |
Группа III от 26 до 45 лет |
Группа IV от 46 до 70 лет |
Группа V свыше 70 лет |
|||
6 |
1 |
Ростов-на-Дону |
1052 |
223 |
2523 |
1088 |
479 |
222 |
7 |
2 |
Челябинск |
1092 |
771 |
2952 |
1274 |
445 |
895 |
8 |
3 |
Казань |
1116 |
492 |
277 |
247 |
540 |
458 |
9 |
4 |
Омск |
1135 |
922 |
1020 |
1941 |
1363 |
287 |
10 |
5 |
Самара |
1139 |
790 |
1215 |
253 |
392 |
544 |
11 |
6 |
Нижний Новгород |
1278 |
977 |
2514 |
1195 |
647 |
86 |
12 |
7 |
Екатеринбург |
1315 |
1032 |
1473 |
441 |
285 |
277 |
13 |
8 |
Новосибирск |
1392 |
336 |
3050 |
2075 |
1083 |
33 |
14 |
9 |
Санкт-Петербург |
4571 |
164 |
918 |
1123 |
212 |
952 |
15 |
10 |
Москва |
10443 |
196 |
2800 |
1851 |
552 |
929 |
16 |
Средние значения: |
590,3 |
468,3 |
Далее требуется произвести выделение диапазона E5:I5, и перейти к пункту меню «Данные» → «Фильтр» → «Автофильтр». В ячейках выбранного диапазона появятся выпадающие списки. После Для фильтра по полю «Группа I», кликаем мышкой по соответствующему списку, выбираем пункт «Условие», и в появившемся диалоговом окне задаём [«Больше» … «YY»], где YY – среднее значение по данному полю. Для печати полученного урезанного списка, выделяем полученную таблицу, и переходим из главного меню «Файл» → «Область печати» → «Задать». После чего общаемся с диалоговым окном «Файл» → «Печать…», указывая способ распечатки (желательно, «уместить на одной странице») и способ ориентации бумаги (произвольно). Аналогичным образом поступаем и с полем «Группа V», тоже выведя результат на печать. Чтобы не тратить бумагу на лишние отпечатки, ввиду небольших размеров обеих полученных списков, я привожу их в отчёте отдельной страницей, просто произведя копирование после сортировки (без вывода на печать).
Список литературы
1. Вероника Михеева, Ирина Харитонова./Microsoft Excel 2003 в подлиннике/«БХВ – Петербург»/2004 г. /1069 стр.
2. Януш Третьин./Решение задач с помощью Excel для студентов и школьников/«Питер»/2006 г.
3. Материалы интернет-ресурса http://ru.wikipedia.org.
Приложение 1
№пункта |
Название города |
Количество жителей [тыс. чел.] |
Возрастной состав |
||||
Группа I до 14 лет |
Группа II от 14 до 25 лет |
Группа III от 26 до 45 лет |
Группа IV от 46 до 70 лет |
Группа V свыше 70 лет |
|||
2 |
Челябинск |
1092 |
771 |
2952 |
1274 |
445 |
895 |
4 |
Омск |
1135 |
922 |
1020 |
1941 |
1363 |
287 |
5 |
Самара |
1139 |
790 |
1215 |
253 |
392 |
544 |
6 |
Нижний Новгород |
1278 |
977 |
2514 |
1195 |
647 |
86 |
7 |
Екатеринбург |
1315 |
1032 |
1473 |
441 |
285 |
277 |
№ пункта |
Название города |
Количество жителей [тыс. чел.] |
Возрастной состав |
||||
Группа I до 14 лет |
Группа II от 14 до 25 лет |
Группа III от 26 до 45 лет |
Группа IV от 46 до 70 лет |
Группа V свыше 70 лет |
|||
2 |
Челябинск |
1092 |
771 |
2952 |
1274 |
445 |
895 |
5 |
Самара |
1139 |
790 |
1215 |
253 |
392 |
544 |
9 |
Санкт-Петербург |
4571 |
164 |
918 |
1123 |
212 |
952 |
10 |
Москва |
10443 |
196 |
2800 |
1851 |
552 |
929 |