Опис завдання
Підготувати документ про склад населення 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 найбільших містах і процентний вираз цієї кількості по відношенню до населення Росії.
Рішення даного завдання буде дуже простим. Для початку, підрахуємо сумарна кількість жителів за даними нашої таблиці. Для цього виділимо комірку E 19 і введемо в неї формулу «= СУММ (D6: D15)». Дана формула наводиться з урахуванням адресації комірок, прийнятої в таблиці 1 цього звіту. Вона, що нетривіально, дозволяє зробити підрахунок суми елементів, ув'язнених у круглих дужках (у даному випадку, це стовпець «Кількість жителів» таблиці 1). Для пояснення отриманого значення, в клітинку D19 вводимо рядок «Сумарна кількість жителів ΣN:» (символ «Σ», як і всі наступні, вводиться викликом діалогового вікна «Вставка» → «Символ» з головного меню Excel), і встановимо вирівнювання по правому краю. Для розрахунку процентного відношення отриманої суми до загальної чисельності населення РФ, з'ясуємо загальну чисельність на все тому ж інтернет ресурсі, що і всі інші дані з поточної роботи. Це число введемо в клітинку E20. Підписом послужить рядок «Населення Російської федерації ΣN РФ:» у клітинці D20 (вирівнювання по правому краю). Для визначення відсоткового співвідношення ми маємо два варіанти:
1. Введення в клітинку E21 формули «= E19/E20 * 100», що дозволить отримати частку у відсотках числа в E19 від числа E 20.
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 (а). Вибираємо пункт «Гістограма», «Об'ємний варіант звичайної гістограми», і натискаємо на кнопку «Далі». У вікні переходимо на вкладку «Ряд», де задаємо імена рядів (окремі осередки діапазону C 6: C 15) і значення (окремі осередки діапазону D 6: D 15), відповідно. Це складний спосіб побудови діаграми, однак, результат себе виправдовує: набірні діаграми часто виглядають набагато краще автоматично сформованих. Після правильного формування списку, вікно прийме вигляд, відображений на малюнку 1 (б) даного звіту. У цьому випадку, натискаємо кнопку «Далі», і вводимо назву діаграми «Діаграма 1: Число жителів по містах». Потім натискаємо на кнопку «Готово», і діаграма розміщується на нашому робочому аркуші. Для покращення візуального ефекту проведена наступна маніпуляція: всі парні стовпчики звернені в циліндри за допомогою контекстного меню «Формат рядів даних ...» → «Постать». Отримана таким чином діаграма наводиться на малюнку 2 цього звіту.
Рисунок 2 - Отримана діаграма 1 за завданням
Пункт 3
Основне завдання: побудувати діаграму, що відображає віковий склад населення Петербурга у відсотках.
Для виконання даного завдання проведемо підготовчий етап. Якщо користувач впорядкує дані у вихідній таблиці, то діаграма, яка будувалася для Санкт-Петербурга, може виявитися побудованої вже для Москви або будь-якого іншого міста. Щоб уникнути цього, зробимо вибірку рядки вихідної таблиці в окрему ділянку листа таким чином, щоб при переміщенні позиції «Санкт-Петербург» у вихідній базі даних, Excel автоматично визначав її позицію і виводив відповідні дані, і вже для цього ділянки листа будемо будувати діаграму. Для виконання поставленого перед собою умови, вдамся до формул Excel. Для початку скопіюємо «шапку» вихідної таблиці в діапазон B 50: I 51. Після цієї шапки створимо одну порожню (поки що) рядок, в яку і передбачається робити вибірку.
У полі «Назва міста» (осередок C 52) скопіюємо назву «Санкт-Петербург». У даній автовиборке це поле буде ключовим. Далі вводимо в усі інші комірки рядка в переділах шапки таблиці формулу «= ІНДЕКС (B6: I15; ПОІСКПОЗ (C52; C6: C15; 0); XX)», де XX - номер стовпця, дані для якого ми хочемо помістити в поточну клітинку (число від 1 до 8, минаючи 2). Пояснимо структуру застосованих формул, простіше кажучи, їх синтаксис:
ПОІСКПОЗ (lookup_value; просматріваемий_массів; тіп_сопоставленія)
іскомое_значеніе - це значення, яке зіставляється зі значеннями в аргументі просматріваемий_массів, який містить деякий діапазон комірок. Аргумент тіп_сопоставленія задає тип логічного порівняння елементів.
У наведеному прикладі використання, дана функція повертає з масиву 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
Для побудови діаграми тепер залишається просто виділити діапазон E 51: I 52, і провести побудова кругової діаграми за допомогою «Майстра діаграм». Результат описаних дій наведено на малюнку 3 звіту.
Рисунок 3 - Отримана діаграма 2 за завданням
Пункт 4
Основне завдання: упорядкувати документ за кількістю населення.
Для здійснення сортування скористаємося стандартним вікном «Сортування» електронних таблиць Excel. Для цього виділяємо діапазон C6: I15 і переходимо з головного меню «Дані» → «Сортування». У діалоговому вікні в пункті «Ідентифікувати діапазон даних по» вибираємо «позначенням стовпців аркуша», після чого в пункті «Сортувати за» вибираємо «Стовпець D». Далі відзначаємо бажаний вид сортування (за зростанням / спаданням) і натискаємо на кнопку «Ок». Дані у вихідній таблиці будуть відсортовані, що, нагадаю, не вплине на побудовану раніше діаграму 2. Результат цих дій наведено в таблиці 4 звіту.
Таблиця 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 незалежних списку).
Для підготовки даного пункту визначимо середні значення по першій і п'ятій віковими групами за допомогою вбудованої функції СРЗНАЧ (), і запишемо їх в осередки E 16 і I 16 відповідно (E 16 = СРЗНАЧ (E6: E15) і I 16 = СРЗНАЧ (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 |
1 червня | Середні значення: | 590,3 | 468,3 |
Далі потрібно зробити виділення діапазону E 5: I 5, і перейти до пункту меню «Дані» → «Фільтр» → «Автофільтр». В осередках обраного діапазону з'являться списки, що випадають. Після Для фільтра по полю «Група 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