Дані під контролем: чистка таблиці й формули

Що робимо сьогодні

Модуль I · IT Skills (Cloud Services & Design) · 120 хвилин · тема 3, урок 1 з 2

Мета. У тебе вже є бриф стартапу. У ньому написано «конкуренти дорогі», «аудиторія велика», «люди готові платити».

Сьогодні кожне з цих речень перетворюється на число, яке можна перевірити. Числа складуться у файл data/market.csv. З нього на наступному уроці виростуть діаграми й розрахунок прибутку.

Після уроку ти вмієш

• знаходити в таблиці дублікати, пропуски, викиди й чотири написання одного й того самого слова;
• рахувати поруч AVERAGE і MEDIAN і, коли вони розійшлися більш ніж на чверть, ставити в текст обидва числа;
• ставити $ у формулі так, щоб вона не ламалася при протягуванні вниз;
• записувати в журнал чистки кожне рішення: що зробив, скільки рядків прибрав і чому;
• здавати data/market.csv, де в кожного числа є джерело в колонці source.

Слова «репрезентативність», «викид» і XLOOKUP поки нічого не означають — розберемо кожне тоді, коли воно знадобиться.

Хід уроку

Звідки беруться дані для стартапу

Чотири джерела. Кожне відповідає на своє питання і бреше по-своєму.

У брифі з уроку 2 є речення на кшталт «підлітки готові платити за це 150 гривень». Поки за ним нічого немає, це побажання. Дані — це спосіб перетворити побажання на твердження, яке можна спростувати.

«Я спитав трьох друзів» — чому це не дослідження

Спитай трьох людей. «Так» скажуть нуль, один, двоє або всі троє — інших варіантів просто немає. У відсотках це чотири числа: 0 %, 33 %, 67 % і 100 %. Між ними порожньо: значення 45 % три відповіді дати не можуть.

Припустимо, ти отримав 67 %. Скільки людей за межами цієї трійки сказали б «так» — 20 зі ста чи 90 зі ста? Обидва варіанти однаково можливі. Три відповіді не звужують здогадку взагалі.

Смугу, у якій може лежати справжнє значення, називають інтервалом довіри. Тут вона розтягнута на всю шкалу від 0 до 100 %. Рахувати таку смугу ми навчимося на наступному кроці.

Друга біда трьох друзів — вони твої друзі. Вони знають, що ти робиш проєкт, і хочуть, щоб ти не засмутився. Питання «ти б платив 150 грн?» отримує «ну, мабуть» від людини, яка ніколи цього не зробить. Тому в опитуваннях питають про минуле («скільки ти витратив на це за останній місяць»), а не про майбутнє.

Кого насправді описує твоє опитування

Кількість відповідей і спосіб їх зібрати — це дві різні речі, і лікуються вони по-різному.

Ти виклав форму опитування в сторіс і зібрав 18 відповідей. Головне питання не в тому, скільки їх. Головне питання — хто взагалі міг на цю форму натрапити.

Сторіс бачать твої підписники. Це переважно люди твого віку, з твоєї школи, з твого району. Однокласниця із сусіднього мікрорайону, яка на тебе не підписана, форми не побачила жодного разу. Вона не відмовилася відповідати — у неї не було такої можливості.

Схема нижче показує це на сотні умовних людей.

У правому стовпчику схеми стоїть скорочення в. п. Це відсоткові пункти. Ними міряють різницю між двома відсотками.

Дивись на верхній рядок. У місті таких підлітків 30 %, у твоїй вибірці — 83 %. Різниця між ними — 53 відсоткові пункти, скорочено 53 в. п.

Чому не «на 53 %»? Бо «на 53 %» означало б зростання від 30 % до 46 %. Це зовсім інше число. Різницю двох відсотків завжди рахують у пунктах — інакше читач не зрозуміє, про що йдеться. Далі на цій сторінці в. п. стоїть у кожній таблиці похибки.

Тепер про саму схему. Те, що ти на ній побачив, має назву. Вибірку звуть репрезентативною, якщо кожна людина з групи, про яку ти робиш висновок, мала шанс у неї потрапити. Не «однаковий шанс» у побутовому сенсі — просто ненульовий і відомий.

Дві нижні групи на схемі мали шанс рівно нуль. Тому ця вибірка не репрезентативна: вона зміщена. Слово робоче, воно трапиться сьогодні ще не раз.

І найважливіше про зміщення: кількістю воно не лікується. Ще чотириста відповідей із тих самих сторіс не додадуть жодної людини з двох нижніх груп.

Зміщення на побутових прикладах

Скільки відповідей потрібно, щоб числу можна було вірити

Тут працює правило, яке зазвичай дивує. Похибка не залежить ані від розміру міста, ані від розміру країни. Тільки від кількості відповідей. Тисяча опитаних дає однакову точність і для Ніжина, і для всієї України.

Рахують її однією формулою: похибка = 1,96 · √(0,25 / n). Тут n — скільки людей відповіли. Підстав своє n — отримаєш свій рядок таблиці нижче.

У таблиці показано найгірший випадок. Це коли думки розділилися приблизно навпіл, 50 на 50: саме тоді похибка найбільша. Її беруть із запасом, щоб не пообіцяти більше, ніж дані витримають.

Ще один підпис до таблиці — рівень довіри 95 %. Означає він ось що. Повтори те саме опитування сто разів із новими людьми — і приблизно у 95 випадках зі ста справжнє значення потрапить у показану смугу. У решті п’яти не потрапить. Стовідсоткової гарантії опитування не дає ніколи.

Чистка даних для аналізу

Чотири типові біди — і чому кожна ламає саме той висновок, який ти хочеш зробити.

Файл market-raw.csv, який ти сьогодні отримав, зібраний як справжній: щось із форми, щось із сайтів конкурентів, щось дописане вручну. Тому в ньому є все, що буває в живих даних.

Жодна з бід нижче не оголошує про себе. Файл відкриється, формули порахують, червоних помилок не буде. Просто числа вийдуть не ті.

Тому чистка — це не прибирання заради краси. Це умова, без якої висновок не має сили.

Останній рядок таблиці вартий окремої зупинки. Числа-текст — найпідступніша з п’яти бід, бо вона нічого не ламає видимо.

Клітинка з написом 150 грн для тебе число. Для таблиці це слово: у ньому є літери. AVERAGE таке слово мовчки пропустить — ні помилки, ні попередження.

Ти побачиш красиве середнє, пораховане не по всіх рядках, а тільки по тих, які програма визнала числами. І не дізнаєшся про це ніколи, поки хтось не перерахує вручну.

Перевіряється це за секунду. Числа притискаються до правого краю клітинки, текст — до лівого. Якщо в колонці цін частина значень стоїть ліворуч, це не числа.

Порядок дій, який працює

1. Зроби копію аркуша й назви її raw. Чистиш на копії — сирі дані має бути з чим звірити.
2. TRIM по всіх текстових колонках: невидимі пробіли з кінця псують і порівняння, і COUNTIF, і автоперевірку.
3. Приведи написання до одного вигляду (LOWER або ручний довідник значень).
4. Прибери дублікати — і полічи, скільки їх було.
5. Розберися з пропусками: знайшов значення — дописав із джерелом, не знайшов — рядок геть.
6. Розглянь викиди поштучно. Викид — це не «велике число», це число, яке з іншої історії.

Журнал чистки: чому рішення треба записувати

Історія версій показує, що змінилося. Журнал відповідає на питання чому.

Кожна дія на попередньому кроці зменшує таблицю. Через тиждень ти дивишся на 96 рядків і не пам’ятаєш, звідки бралися 120. А на Demo Day питання «чому ви викинули четверту частину даних» звучить обов’язково — і відповідь «здалося зайвим» коштує дорого.

Формат запису

Журнал — це аркуш log у твоїй таблиці або розділ у CHANGELOG.md. Один рядок — одне рішення, чотири поля:

Медіана і середнє: яке число брати для цін

Для цін, доходів і чеків майже завжди беруть медіану. І це не питання смаку.

Середнє ділить суму на кількість: кожне значення тягне на себе. Медіана — це значення посередині відсортованого ряду: половина менша, половина більша. Одне велике число зсуває середнє на скільки завгодно, а медіану — щонайбільше на один крок ряду.

У прикладі вище викид 900 грн підняв середнє з 169 до 315 гривень — на 146 гривень. Медіана зрушила зі 165 до 190, тобто на 25. Якщо ти ставиш ціну «трохи нижче за середню по ринку», то з першим числом це 300 грн, а з другим — 180. Це два різні продукти.

Робоче правило. Порахуй обидва числа. Якщо вони відрізняються більш ніж на чверть — у даних сидить викид.

Тоді в текст ідуть обидва. Наприклад так: «медіанна ціна 180 грн, середня 315 — середню тягне один репетитор за 900».

Формули, які реально потрібні

Шість функцій закривають майже всю аналітику стартапу на цьому етапі.

XLOOKUP і що робити з помилкою

Уяви два аркуші. На першому — список конкурентів, і в кожного стоїть код сегмента: shkolyari, studenty. На другому — довідник: який код що означає.

Переписувати назви руками ти не будеш: рядків шістдесят, і на третьому десятку почнуться описки. XLOOKUP робить це сам. Він бере код із першого аркуша, знаходить його в довіднику й повертає назву з сусідньої колонки.

Тепер про четвертий аргумент. Коду може не знайтися — ти помилився в написанні або довідник неповний. Що показати в такому разі, вирішуєш ти: =XLOOKUP(A2;E:E;F:F;"немає у довіднику"). Текст у дужках наприкінці і є той четвертий аргумент.

Без нього в клітинці з’явиться #N/A. Так таблиця каже «не знайшла». Один #N/A ще читається, а колонка з двадцяти таких лякає і ховає за собою все інше.

Тут багато хто хапається за IFERROR: він замінює будь-яку помилку на що завгодно, хоч на порожнечу. Спокуса зрозуміла, наслідок поганий. IFERROR глушить усі помилки одразу — і ділення на нуль, і зіпсоване посилання теж.

Таблиця після цього виглядає чистою, а рахує неправильно. Четвертий аргумент XLOOKUP прибирає рівно один випадок, «не знайшлося», і лишає решту помилок на видноті.

Знак $: найчастіша помилка при копіюванні

Візьми клітинку з формулою, схопи її за правий нижній кут і потягни вниз. Це і зветься «протягнути формулу»: таблиця сама заповнить усю колонку.

При цьому кожне посилання у формулі зсувається на стільки ж рядків. Був =C2-D2 — у наступному рядку стане =C3-D3. Зазвичай саме це й потрібно: кожен рядок має рахуватися по своїх даних.

Біда починається, коли у формулі є посилання на одну спільну для всіх клітинку. Курс валюти, комісія, постійні витрати — таке значення лежить в одному місці, і адреса в нього мінятися не повинна.

Але вона поїде разом з усіма. Було B8 з комісією — у другому рядку стане B9, у третьому B10. А там порожньо. Множення на порожню клітинку дає нуль.

Лікується це двома символами. Постав долар перед буквою й перед цифрою — $B$8 — і адреса примерзне на місці. Долар означає рівно одне: «цю частину адреси при копіюванні не чіпай».

Тренажер формул

Справжній обчислювач: пишеш формулу — сторінка рахує її по цій таблиці й показує помилки так само, як показала б Google Sheets.

Дані внизу — приклад для тренування, не твій ринок. Колонка D навмисно містить один дорогий рядок: подивись, що станеться з AVERAGE і MEDIAN.

Джерело кожного числа

Колонка source — не бюрократія. Це те, що відрізняє аналітику від вигадування.

Правило модуля просте: число без джерела не йде в презентацію. Не тому, що так суворо, а тому, що на Demo Day перше питання після будь-якої цифри — «звідки вона». Якщо відповідь «здається, десь бачив», далі можна не продовжувати: разом із числом сумнівною стає вся решта роботи.

На схемі видно розподіл праці між двома файлами. Розберемо, навіщо їх саме два.

Автоперевірка читає market.csv посимвольно й чекає рівно шість колонок. Колонка source була б сьомою — вона туди не вміщається.

Викинути джерела зовсім теж не можна. Тоді зникає єдине, чим ти підтвердиш будь-яке число зі своєї таблиці.

Тому джерела живуть окремо, у market-sources.csv. Один рядок цього файлу — одне число з таблиці: який сегмент, яке поле, звідки взято, якою датою. Автоперевірка цього файлу не вимагає. Учитель на Demo Day спитає саме про нього.

Що писати в source

Спільне в правій колонці одне: за таким записом число можна знайти вдруге. Є адреса сайту й дата — відкрив і звірив. Є кількість опитаних і хто вони — видно, чого вартий відсоток. Є рік у статистиці — видно, наскільки число застаріле.

Слово «оцінка» в цьому списку не соромне. Соромно видавати оцінку за виміряне число. Напиши прямо: «оцінка: 30 % від числа Х» — і претензій не буде.

Перевір свою таблицю прямо на цій сторінці

Встав свій CSV — сторінка знайде те саме, що шукав би вчитель, і покаже, як від кожного виправлення поїдуть середнє й медіана.

Правила ми розібрали. Тепер найшвидший спосіб побачити, що з твоєю таблицею не так, — показати її сторінці.

У полі вже лежить навчальний брудний приклад. Натисни «Перевірити» й подивись на список.

Далі вставляй свій файл — той самий, з якого робиш market.csv. Дані нікуди не надсилаються: увесь розбір відбувається у твоєму браузері.

Практика: data/market.csv

Виконуй по черзі. Галочки зберігаються — сторінку можна закрити.

Як має виглядати результат

Приклад заповнення: числа тут ілюстративні, у тебе будуть свої. Постійні витрати в цьому прикладі — 4000 грн/міс, тому break_even_units = CEILING(4000 / margin): 4000/110 = 36,4 → 37.

Автоперевірка check11 3

Перша частина теми 3. Друга частина (analytics.html) — на уроці 4. Спроби не обмежені.

Демонстраційний режим: результат згенеровано для показу. На сервері ця кнопка викликає check11 3, який парсить твій CSV модулем csv, звіряє заголовок і перераховує margin для кожного рядка.

Що вчитель дивиться на екрані

• сама таблиця, а не CSV: показники пораховані формулами — учитель просить змінити одну ціну й дивиться, чи перерахувалося все;
• абсолютні посилання там, де посилання спільне: $H$1, а не H1;
• окремий аркуш raw із сирими даними і аркуш log із журналом чистки;
• ти вголос називаєш джерело будь-якого числа, на яке вкаже вчитель.

Шкала виконання

Це те, що бачить учитель, коли виставляє оцінку.

Сім пасток цього уроку

1. Показники вписані руками замість формул — при зміні ціни нічого не перераховується.
2. Забутий $: перший рядок правильний, решта колонки — нулі.
3. Експорт із локаллю, де роздільник ;, а десятковий — кома.
4. BOM на початку файлу: заголовок виглядає правильним, а звірка падає.
5. Пробіл у кінці значення сегмента — COUNTIF рахує окремо, автоперевірка бачить дублікат.
6. Викид видалений мовчки — і на Demo Day нема чим пояснити, чому ринок «дешевий».
7. Середнє замість медіани в описі цін — і ціна продукту вибудувана навколо одного дорогого конкурента.

Домашнє завдання

1. Довести market.csv до стану, коли всі пункти автоперевірки зелені.
2. Дописати в CHANGELOG.md датований запис із журналом чистки: скільки рядків було, скільки лишилося, три головні рішення й причина кожного.
3. Додати data/market-sources.csv: для кожного числа з market.csv — рядок «сегмент, поле, джерело, дата».
4. Порахувати для колонки цін і середнє, і медіану. Якщо вони відрізняються більш ніж на чверть — знайти, який саме рядок це робить, і написати про нього одне речення.

Звідки взяті факти

Усе, що можна перевірити, — з документації, стандартів і опублікованих методологій. Перевіряти дозволено й корисно.

• Похибка вибірки для 2000 респондентів — методологія омнібусу Київського міжнародного інституту соціології. kiis.com.ua · загальна інформація
• Формула похибки частки 1,96 · √(p(1−p)/n) і найгірший випадок при p = 0,5 — довідник NIST/SEMATECH. itl.nist.gov · confidence interval for a proportion
• Середня і медіанна заробітна плата публікуються окремо — Державна служба статистики України. ukrstat.gov.ua
• Відкриті дані України — єдиний державний портал. data.gov.ua
• Правило 1,5 × IQR для позначення викидів — розділ про «box plot» у довіднику NIST/SEMATECH. itl.nist.gov · box plots
• Формат CSV: заголовок, кома як роздільник, лапки — RFC 4180. datatracker.ietf.org/doc/html/rfc4180
• BOM в UTF-8 і чому його краще не ставити — Unicode FAQ. unicode.org · UTF and BOM FAQ
• SUM, AVERAGE, MEDIAN, COUNTIF, IF, XLOOKUP — довідка Google Таблиць. support.google.com · список функцій
• XLOOKUP і аргумент «якщо не знайдено» — довідка Microsoft. support.microsoft.com · XLOOKUP
• Відносні, абсолютні й змішані посилання — довідка Microsoft про переміщення й копіювання формул. support.microsoft.com · посилання на клітинки
• Значення помилок #DIV/0!, #VALUE!, #NAME?, #N/A — довідка Microsoft. support.microsoft.com · виправлення помилок у формулах
• PowerShell 5.1 пише перенаправлення в UTF-16 — документація про кодування у Windows PowerShell. learn.microsoft.com · about_character_encoding

Повний список із поясненнями, що саме взято з кожного джерела, — у файлі urok-03-джерела.md поруч із цією сторінкою. Назви компаній, ціни конкурентів і числа опитувань тут — навчальний приклад, складений автором уроку. Перевіряються тільки формули й методи, а не самі числа.