Деректерді талдау
Блогер парақшасына пост көбейген сайын оқырман да көбейеді. Сұрақ: пост 120-ға жеткенде оқырман қанша болады? «Білмеймін, көреміз» деме — Excel мұны ЕСЕПТЕЙ алады. Иә, Excel болашақты болжайды! Бүгінгі сабақ — деректерді талдау өнері туралы: сандар үйіндісінен мағына табу.
Елестет: жиһаз кәсіпорны 10 түрлі өнім шығарады. Тоқсандық сату кестесінде — жүздеген сан. Директор сұрайды: «Нені көбірек шығарайық?» Кестеге қарап отырып жауап беру қиын. Ал сол деректерден ГИСТОГРАММА салсақ — жауап бір секундта көрінеді: орындық 1 — 505 дана (ең көп!), үстел 2 — 55 дана (ең аз). Шешім дайын: орындық өндірісін ұлғайту, үстел 2-ні азайту.
Талдау — деректерден ШЕШІМ шығару. Excel-де бұған арналған құралдар: диаграммалар (гистограмма — бағанмен, сызықтық — көлденең жолақпен, дөңгелек — үлеспен), сұрыптау мен сүзгі, топтау, консолидация, аралық нәтижелер, жиынтық кестелер.
Спарклайн — ұяшықтың ІШІНДЕ орналасатын миниграфик: деректердің өзгеру процесін (баға, сату көлемі) көрнекі қадағалайды, ең үлкен/ең кіші нүктелерді көрсетеді.
Спарклайнның артықшылығы — жылдамдығы: Кірістіру → Спарклайндар → График, деректер диапазоны мен орнын көрсет — болды. Балама тәсіл — шартты форматтаудың Түстік шкаласы: сандар мәніне қарай түспен боялады — «жылу картасы» шығады.
Дүкенде үш сатушы үш ай бойы тауар сатты — журналда 9 жазба. Директор сұрайды: «Әр сатушы барлығы қаншаға сатты?» Қолмен қоссақ болады... 9 жазбада. Ал 9 000 жазбада ше? Осы жерде Excel-дің ең қуатты талдау құралы шығады.
Жиынтық кесте (Сводная таблица) — кестелік деректер негізінде құрастырылатын есеп; үлкен көлемді деректерді жинақтап талдауға арналған.
Құру реті: деректердің ішіне тұрып → Кірістіру → Жиынтық кесте → диапазон мен орынды көрсету. Одан кейін Алаңдар тізімі ашылады, әр алаңды төрт аймақтың біріне сүйрейсің: Жолдар атауы (мысалы, сатушылар), Бағандар атауы (айлар), Мәндер (қосынды, орташа, сан), Есеп сүзгісі (сүзгі шарты).
Мысалда: жолдарға — фамилияларды, мәндерге — соманы саламыз. Нәтиже бір секундта: Алиева — 23 000, Петров — 32 000, Сатыбалдина — 31 000, жалпы — 86 000. Бір де бір формула жазған жоқпыз!
Талдаудың «қара жұмысын» математикалық функциялар атқарады. Өткен сабақтағыларға (СУММ, КОРЕНЬ, СТЕПЕНЬ) төрт жаңасын қосамыз:
| Функция | Қызметі | Мысал |
|---|---|---|
| ОСТАТ(сан;бөлгіш) | Бөлуден қалған қалдық | =ОСТАТ(8;3) → 2 |
| ОКРУГЛ(сан;разряд) | Дөңгелектеу | =ОКРУГЛ(7,38;1) → 7,4 |
| ЦЕЛОЕ(сан) | Ең жақын кіші бүтінге | =ЦЕЛОЕ(7,5) → 7 |
| ЧАСТНОЕ(сан;бөлгіш) | Бөліндінің бүтін бөлігі | =ЧАСТНОЕ(8,3;5) → 1 |
ОСТАТ жұп/тақты тексеруге таптырмайды: ОСТАТ(x;2)=0 — жұп! ОКРУГЛ-дың разряды 0 — бүтінге (88,5 → 89), разряд −1 — ондыққа (888 → 890). Қосындыға жылдам жол — Σ Автосумма батырмасы.
Күнделікті деректердің көбі УАҚЫТ бойынша тізіледі: күн сайынғы температура, ай сайынғы сату. Мұндай тізбек динамикалық қатар деп аталады. Оның ТРЕНДІН — негізгі даму бағытын — тапсақ, болашаққа СОЗУҒА болады!
Excel-де болжау тренд сызығы арқылы жасалады (регрессиялық талдау). Тренд сызығының 6 түрі бар: сызықтық, логарифмдік, полиномдық, дәрежелік, жылжымалы орташа, экспоненциалдық. Ең қарапайымы — сызықтық тренд.
Алдымен екі сұраққа жауап керек: 1) Байланыс бар ма? — КОРРЕЛ функциясы өлшейді: R > 0 — бірге өседі, R < 0 — бірі өссе екіншісі кемиді; |R| 1-ге жақын — байланыс тығыз. 2) Болжам сенімді ме? — R² (сенімділік коэффициенті): 1-ге жақын болса, тренд деректерге дәл келеді.
ПРЕДСКАЗ(x; y-белгілі аралық; x-белгілі аралық) — сызықтық тренд бойынша болжам жасайтын статистикалық функция.
Қадам 1: =КОРРЕЛ(A3:A10;B3:B10) → R = 0,967 — байланыс айқын.
Қадам 2: нүктелік диаграмма салып, тренд сызығын қосамыз (сызықтық). Нәтиже: y = 2,257x − 16,23; R² = 0,935.
Қадам 3 (1-әдіс): x=120 → y = 2,257·120 − 16,23 ≈ 254,6 мың; x=150 → ≈322 мың.
Қадам 4 (2-әдіс): =ПРЕДСКАЗ(120;B3:B10;A3:A10) → 254,65 — екі әдіс сәйкес келді!
Жауабы: 120 постта ≈254 мың, 150 постта ≈322 мың танысушы.
Болжау тізбегі әрқашан бірдей: динамикалық қатар → КОРРЕЛ → тренд сызығы (теңдеу + R²) → теңдеуге қою немесе ПРЕДСКАЗ.
- Көрнекілік үшін: диаграммалар, спарклайндар, түстік шкалалар.
- Жинақтау үшін: сұрыптау, сүзгі, топтау, консолидация, жиынтық кесте.
- Есептеу үшін: СУММ, ОСТАТ, ОКРУГЛ, ЦЕЛОЕ, ЧАСТНОЕ.
- Болжау үшін: динамикалық қатар → КОРРЕЛ → тренд сызығы (y = kx + b, R²) → ПРЕДСКАЗ.