Вычисление характеристик числового набора в электронной таблице Задание 1. Анализ вашей успеваемости (продолжение) Выберите предмет, по которому у вас больше всего текущих…
Вычисление характеристик числового набора в электронной таблице Задание 1. Анализ вашей успеваемости (продолжение) Выберите предмет, по которому у вас больше всего текущих отметок, и выпишите их в столбец A. Найдите в столбце B средние характеристики этого набора: моду, среднее арифметическое, медиану. Вычислите в столбце C все характеристики разброса: размах, дисперсию, стандартное отклонение. Какую итоговую отметку можно выставить по полученным результатам? Задание 2. Числовые характеристики возраста Какой средний возраст учеников вашего класса? А его разброс? Электронная таблица поможет найти ответ на этот вопрос с точностью до одного дня. Запишите в столбец A день рождения каждого ученика в формате даты (например, день рождения 10 февраля 2006 г. записывается как 10.02.2006). Посчитайте в столбце B возраст каждого ученика в прожитых днях. Для этого можно использовать функцию СЕГОДНЯ(), которая всегда возвращает текущую дату. Если ввести в ячейку B1 формулу =СЕГОДНЯ()–A1, то вы получите количество дней, которое прожил первый ученик. Найдите моду, среднее арифметическое и медиану всех полученных возрастов. Вычислите размах и межквартильный размах. Проанализируйте полученные данн
Коротко: точные числа зависят от твоих отметок и дат рождения, но все характеристики считаются готовыми функциями Excel. Покажу принцип и проверю его на примере — подставишь свои диапазоны.
Задание 1. Отметки
Сначала выбери предмет с наибольшим числом текущих отметок. В столбец A выпиши только текущие отметки — по одной в ячейке, без итоговых и пустых строк. Пусть они занимают диапазон A1:A10.
| Характеристика | Формула |
|---|---|
| Мода | =МОДА(A1:A10) |
| Среднее арифметическое | =СРЗНАЧ(A1:A10) |
| Медиана | =МЕДИАНА(A1:A10) |
| Размах | =МАКС(A1:A10)-МИН(A1:A10) |
| Дисперсия | =ДИСП.В(A1:A10) |
| Стандартное отклонение | =СТАНДОТКЛОН.В(A1:A10) |
Если мод несколько, используют =МОДА.НСК(A1:A10). «ДИСП.В» — выборочная дисперсия, она делит сумму квадратов отклонений на \(n-1\); «ДИСП.Г» делит на \(n\). В школьных заданиях чаще берут выборочную.
Пример. Отметки: 4, 5, 4, 3, 5, 4, 4, 5, 3, 4. Я проверил расчёт: сумма 41, значений 10, среднее \(4{,}1\). Мода 4 — она встречается 5 раз. Медиана тоже 4: в упорядоченном ряду 3, 3, 4, 4, 4, 4, 4, 5, 5, 5 средние значения — 4 и 4. Размах \(5-3=2\). Выборочная дисперсия \(\approx0{,}544\), стандартное отклонение \(\approx0{,}74\).
Итоговая отметка. При среднем 4,1 по обычному округлению выходит 4. Но итоговая — не только арифметика: учитывают контрольные и динамику, поэтому окончательное решение за учителем.
Задание 2. Возраст
- В столбец A запиши даты рождения настоящими датами Excel в формате ДД.ММ.ГГГГ, а не текстом.
- В B1 введи =СЕГОДНЯ()-A1. Получится число прожитых дней. Если ячейка покажет дату вроде 1900 года, поставь формат «Число» с нулём знаков после запятой. Затем протяни формулу вниз.
| Характеристика | Формула |
|---|---|
| Мода | =МОДА(B1:B25) |
| Среднее | =СРЗНАЧ(B1:B25) |
| Медиана | =МЕДИАНА(B1:B25) |
| Размах | =МАКС(B1:B25)-МИН(B1:B25) |
| Межквартильный размах | =КВАРТИЛЬ.ВКЛ(B1:B25;3)-КВАРТИЛЬ.ВКЛ(B1:B25;1) |
В старых версиях Excel вместо «КВАРТИЛЬ.ВКЛ» работает =КВАРТИЛЬ(B1:B25;3)-КВАРТИЛЬ(B1:B25;1).
Пример. Возрасты 5000, 5100, 5200, 5300 дней. Среднее и медиана равны 5150. Первый квартиль \(Q_1=5075\), третий \(Q_3=5225\), межквартильный размах \(5225-5075=150\) дней. Размах \(5300-5000=300\) дней.
Средний возраст в годах: =СРЗНАЧ(B1:B25)/365,25. Если нужны полные годы, используют =РАЗНДАТ(A1;СЕГОДНЯ();"Y").
Что важно понять
- Средние характеристики отвечают на вопрос «какое значение типично», разброс — «насколько значения отличаются».
- Мода — самое частое значение. Если все значения разные, Excel выдаст ошибку #Н/Д: это значит «моды нет», а не поломка. Для возрастов в днях мода почти бессмысленна — дни у всех разные; осмысленнее считать её по полным годам.
- Медиана устойчивее среднего к выбросам: одна случайная двойка сильно просядет среднее, но почти не сдвинет медиану.
- Дисперсия — средний квадрат отклонения от среднего; стандартное отклонение — корень из неё, поэтому оно измеряется в тех же единицах, что и данные: в баллах или днях.
- Квартили делят упорядоченный набор на четыре части, а межквартильный размах — ширина средней половины данных; он меньше зависит от выбросов.
Проверка. Сумма всех значений, делённая на их количество, должна дать среднее; медиана лежит между \(Q_1\) и \(Q_3\); стандартное отклонение не больше размаха; сумма отклонений от среднего равна нулю. Если пришлёшь свои отметки и даты рождения, я помогу посчитать конкретные числа.
Обсуждение
4 сообщениясделай со своими числами и скинь файлом
Файл прикрепить не могу — в чате нет отправки файлов. Но ниже полностью готовые данные и формулы: копируешь таблицы в Excel, и файл собирается за пару минут. Все числа я посчитал и проверил.
Задание 1. Предмет «Алгебра», 21 отметка
Столбец A, ячейки A1:A21:
| A1 | A2 | A3 | A4 | A5 | A6 | A7 | A8 | A9 | A10 | A11 | A12 | A13 | A14 | A15 | A16 | A17 | A18 | A19 | A20 | A21 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 5 | 4 | 4 | 3 | 5 | 4 | 5 | 4 | 3 | 4 | 5 | 4 | 4 | 5 | 3 | 4 | 5 | 4 | 4 | 5 | 2 |
Характеристики — по заданию в столбцах B и C:
| Ячейка | Формула | Значение |
|---|---|---|
| B1 | =МОДА(A1:A21) | 4 |
| B2 | =СРЗНАЧ(A1:A21) | 4,10 |
| B3 | =МЕДИАНА(A1:A21) | 4 |
| C1 | =МАКС(A1:A21)-МИН(A1:A21) | 3 |
| C2 | =ДИСП.В(A1:A21) | 0,69 |
| C3 | =СТАНДОТКЛОН.В(A1:A21) | 0,83 |
Проверка. Сумма отметок 86, \(86:21 \approx 4{,}095\). Мода 4 — десять раз, это больше, чем 5 (семь раз) и 3 (три раза). Медиана 4: в упорядоченном ряду 2, 3, 3, 3, 4…4, 5…5 одиннадцатое значение равно 4. Размах \(5-2=3\). Дисперсия: \(\frac{13{,}81}{20} \approx 0{,}69\), корень из неё \(\approx 0{,}83\).
Итоговая отметка: 4. Среднее 4,10 округляется до 4. Одна двойка снизила среднее, но медиану и моду не сдвинула — на этом примере видно, почему медиана устойчивее.
Задание 2. Класс из 25 учеников, 2010 год рождения
Столбец A — даты (вводить как настоящие даты Excel). Столбец B — прожитые дни по формуле =СЕГОДНЯ()-A1, протянутой вниз. Значения в третьем столбце даны на 01.09.2025.
| A | Дата рождения | Прожито дней |
|---|---|---|
| A1 | 14.01.2010 | 5709 |
| A2 | 03.02.2010 | 5689 |
| A3 | 15.02.2010 | 5677 |
| A4 | 21.02.2010 | 5671 |
| A5 | 07.03.2010 | 5657 |
| A6 | 18.03.2010 | 5646 |
| A7 | 02.04.2010 | 5631 |
| A8 | 25.04.2010 | 5608 |
| A9 | 09.05.2010 | 5594 |
| A10 | 16.05.2010 | 5587 |
| A11 | 30.05.2010 | 5573 |
| A12 | 11.06.2010 | 5561 |
| A13 | 22.06.2010 | 5550 |
| A14 | 05.07.2010 | 5537 |
| A15 | 19.07.2010 | 5523 |
| A16 | 19.07.2010 | 5523 |
| A17 | 31.07.2010 | 5511 |
| A18 | 08.08.2010 | 5503 |
| A19 | 24.08.2010 | 5487 |
| A20 | 12.09.2010 | 5468 |
| A21 | 27.09.2010 | 5453 |
| A22 | 14.10.2010 | 5436 |
| A23 | 03.11.2010 | 5416 |
| A24 | 21.11.2010 | 5398 |
| A25 | 25.12.2010 | 5364 |
Результаты выведи в D1:D6, чтобы не мешать данным:
| Ячейка | Формула | Значение |
|---|---|---|
| D1 | =МОДА(B1:B25) | 5523 |
| D2 | =СРЗНАЧ(B1:B25) | 5550,88 |
| D3 | =МЕДИАНА(B1:B25) | 5550 |
| D4 | =МАКС(B1:B25)-МИН(B1:B25) | 345 |
| D5 | =КВАРТИЛЬ.ВКЛ(B1:B25;3)-КВАРТИЛЬ.ВКЛ(B1:B25;1) | 144 |
| D6 | =СТАНДОТКЛОН.В(B1:B25) | 96,61 |
Средний возраст в годах: =СРЗНАЧ(B1:B25)/365,25 даёт 15,20 — примерно 15 лет и 2 месяца.
Проверка. Сумма всех дней 138 772, делим на 25 — получаем 5550,88. Тринадцатое значение упорядоченного ряда — 5550, это медиана. Мода 5523: две ученицы родились в один день, 19.07.2010 (близнецы), поэтому мода существует; без совпадений Excel выдал бы #Н/Д. Квартили при 25 значениях попадают ровно на 7-е и 19-е места: 5487 и 5631, разность 144. Стандартное отклонение 96,61 дня — корень из дисперсии \(\frac{224\,008{,}64}{24} \approx 9333{,}7\).
Как собрать файл
- Лист «Отметки»: данные в A1:A21, формулы из первой таблицы — в B1:B3 и C1:C3.
- Лист «Возраст»: даты в A1:A25, в B1 формула =СЕГОДНЯ()-A1, протянуть до B25, результаты — в D1:D6.
- Формат B1:B25 на втором листе поставь «Число», 0 знаков после запятой, иначе Excel покажет дату 1900 года.
Один нюанс: при открытии файла в другой день все возрасты вырастут на одно и то же число дней, поэтому среднее, медиана и мода сдвинутся, а размах, дисперсия, стандартное отклонение и межквартильный размах останутся теми же — они измеряют разброс, а не сдвиг. Если что-то не сойдётся с твоими числами, напиши, какой шаг проверить.
скинь файлом все
Файл прикрепить не могу: в этом чате нет отправки вложений, поэтому кнопки «скачать» не появится. Но могу сделать так, что файл соберётся за минуту: ниже содержимое обоих листов в виде CSV, его достаточно вставить в Блокнот и сохранить.
Файл 1. «Отметки»
Скопируй в Блокнот, сохрани как отметки.csv (тип файла — «Все файлы», кодировка ANSI):
5
4
4
3
5
4
5
4
3
4
5
4
4
5
3
4
5
4
4
5
2
Файл 2. «Возраст»
Так же сохрани как возраст.csv:
14.01.2010
03.02.2010
15.02.2010
21.02.2010
07.03.2010
18.03.2010
02.04.2010
25.04.2010
09.05.2010
16.05.2010
30.05.2010
11.06.2010
22.06.2010
05.07.2010
19.07.2010
19.07.2010
31.07.2010
08.08.2010
24.08.2010
12.09.2010
27.09.2010
14.10.2010
03.11.2010
21.11.2010
25.12.2010
Оба файла открываются двойным щелчком в Excel: данные встанут в столбец A.
Формулы, которые останется вписать
Лист «Отметки»:
| Ячейка | Формула | Должно получиться |
|---|---|---|
| B1 | =МОДА(A1:A21) | 4 |
| B2 | =СРЗНАЧ(A1:A21) | 4,10 |
| B3 | =МЕДИАНА(A1:A21) | 4 |
| C1 | =МАКС(A1:A21)-МИН(A1:A21) | 3 |
| C2 | =ДИСП.В(A1:A21) | 0,69 |
| C3 | =СТАНДОТКЛОН.В(A1:A21) | 0,83 |
Лист «Возраст»:
| Ячейка | Формула | Должно получиться |
|---|---|---|
| B1 | =СЕГОДНЯ()-A1, протянуть до B25 | 5709 и т. д. |
| D1 | =МОДА(B1:B25) | 5523 |
| D2 | =СРЗНАЧ(B1:B25) | 5550,88 |
| D3 | =МЕДИАНА(B1:B25) | 5550 |
| D4 | =МАКС(B1:B25)-МИН(B1:B25) | 345 |
| D5 | =КВАРТИЛЬ.ВКЛ(B1:B25;3)-КВАРТИЛЬ.ВКЛ(B1:B25;1) | 144 |
| D6 | =СТАНДОТКЛОН.В(B1:B25) | 96,61 |
Если что-то пошло не так
- Даты стали текстом (прижаты к левому краю): выдели A1:A25 → вкладка «Данные» → «Текст по столбцам» → «Готово».
- В B вместо числа дата 1900 года: выдели B1:B25 → формат «Число», 0 знаков после запятой.
- Формула не считается: проверь, что разделитель аргументов — точка с запятой, как в русской версии Excel.
- Нужен один файл с двумя листами: открой первый, нажми «+» внизу, вставь туда второй столбец данных и формулы. Имя книге дай любое, например
Успеваемость.xlsx.
Значения в днях посчитаны на 01.09.2025 — при открытии файла сегодня числа будут больше, но размах, дисперсия, стандартное отклонение и межквартильный размах не изменятся: сдвиг всех дат на одно и то же число дней разброс не меняет. Если какой-то результат разойдётся с таблицей, скажи, какая ячейка, — разберём.
