Статистикалық мәліметтер. Сілтемелер
Елестет: сен дүкен есепшісісің. Кестеде 500 тауар бар, әрқайсысының долларда бағасы жазылған. Тапсырма: бәрін теңгеге айналдыру. 500 формуланы қолмен жазасың ба? Әрине, жоқ — БІР формула жазып, қалғанына көшіресің. Бірақ көшіргенде бір қызық мәселе шығады: формуладағы адрестер «жылжып» кетеді. Кейде бұл — керемет ыңғайлы, кейде — апат. Осы «жылжуды» басқару — бүгінгі сабақтың тақырыбы: салыстырмалы, абсолютті және аралас сілтемелер. Бұл — Excel-дің ең маңызды тетігі және ҰБТ-ның сүйікті сұрағы.
Ежелгі мемлекеттердің өзі халқын, әскерге жарайтындарын, малы мен жерін санап отырған — салық жинау мен соғысқа дайындық үшін. XVII ғасырдың ортасында Ұлыбританияда «Саяси арифметика» бағыты, Германияда «Міндетті статистика» мектептері пайда болып, бұлардан статистика ғылымы туды (лат. status — жай-күй).
Статистикалық мәліметтер — статистикалық зерттеулер (бақылау және ғылыми өңдеу) негізінде алынған сандық сипаттамалардың жиынтығы.
Мұндай деректер әрқашан КЕСТЕ түрінде ұйымдастырылады. Кестелермен жұмыстың ең қуатты құралы — Excel кестелік процессоры. Еске түсірейік: ұяшық — жол мен бағанның қиылысы; адресі баған аты + жол нөмірі (A1, C18); формула «=» белгісінен басталады; тінтуір тұрған ұяшық — белсенді ұяшық. Формуласы бар ұяшық «тәуелді» деп аталады — оның мәні басқа ұяшықтарға тәуелді. Осы тәуелділікті басқаратын механизм — сілтеме.
Сілтеме — формула жазу барысында қолданылатын объект адресі (ұяшық, жол, баған, ұяшықтар ауқымы).
Асхана мәзірі: құймақ — 100 тг, 5 дана; бауырсақ — 50 тг, 10 дана. Әр тағамның жалпы құнын табу керек: баға × сан. D2 ұяшығына =B2*C2 жазамыз — құймақ үшін 500 шықты. Ал бауырсаққа? Тағы формула жазамыз ба? Жоқ! D2-нің бұрышындағы автотолтыру маркерін ұстап, төмен сүйрейміз. Ғажап: D3-те формула ӨЗІ =B3*C3 болып өзгерді.
Бұл қалай болды? Excel формуладағы адресті «қатып қалған» орын деп емес, «менен бір ұяшық солға» деген БАҒЫТ деп түсінеді. Формула төмен жылжыса — сілтемелер де бірге жылжиды.
Салыстырмалы сілтеме — формуланы көшіргенде адресі жаңа орнына сәйкес автоматты өзгеретін сілтеме (A1, D25). Excel-де үнсіз келісім бойынша барлық сілтеме — салыстырмалы.
Жылжу ережесі: формуланы ОҢҒА немесе СОЛҒА көшірсең — баған аты өзгереді; ЖОҒАРЫ немесе ТӨМЕН көшірсең — жол нөмірі өзгереді. =B1+C1 формуласы бір жол төмен түссе → =B2+C2.
Жұмысшылардың жалақысы 10%-ға өседі. D2 ұяшығына коэффициент жаздық: 1,1. Бірінші жұмысшыға формула: =C4*D2 — 110 000 шықты, дұрыс! Формуланы төмен көшіреміз... апат: #ЗНАЧ!, сосын мүлдем қисынсыз сандар.
Не болды? Салыстырмалы сілтеме «адал» жұмыс істеді: =C5*D3, =C6*D4... Бірақ D3, D4 ұяшықтарында ештеңе жоқ — коэффициент ТЕК D2-де! Бізге керегі: C4 жылжысын, ал D2 ОРНЫНДА ҚАЛСЫН. Ол үшін сілтемені «бекітеміз» — доллар белгісімен: $D$2.
Абсолютті сілтеме — формула көшірілгенде өзгермейтін, әрқашан бір ұяшықты көрсететін сілтеме. Баған аты мен жол нөмірінің алдына $ белгісі қойылады: $A$9, $B$5. Бекіту үшін сілтемені ерекшелеп, F4 пернесін басады.
$ белгісін ЖАРТЫЛАЙ қоюға болады: $A1 — баған бекітілген, жол еркін; A$1 — жол бекітілген, баған еркін. Классикалық мысал — Пифагордың көбейту кестесі: жоғарыда 1–9 (1-жол), сол жақта 1–9 (A бағаны). B2-ге бір ғана формула жазамыз: =B$1*$A2. Бірінші көбейткіш әрқашан 1-ЖОЛДАН (B$1), екіншісі әрқашан A БАҒАНЫНАН ($A2) алынады. Осы формуланы бүкіл кестеге көшірсең — 81 ұяшық өзі толады!
| Сілтеме түрі | Оңға толтырғанда | Төмен толтырғанда |
|---|---|---|
| A1 (салыстырмалы) | B1 | A2 |
| $A1 (баған бекітілген) | $A1 | $A2 |
| A$1 (жол бекітілген) | B$1 | A$1 |
| $A$1 (абсолютті) | $A$1 | $A$1 |
Шешуі: A1 салыстырмалы — бір баған оңға, бір жол төмен → B2 болады. $B$2 өзгермейді.
Жауабы: =B2*$B$2, нәтиже 8·8 = 64.
Шешуі: D4-ке =C4*$D$2, төмен көшіреміз: =C5*$D$2, =C6*$D$2...
Жауабы: =C4*$D$2; 120 000 · 1,1 = 132 000 тг. $ болмаса — #ЗНАЧ! қатесі.
Шешуі: B9 = =B$4+B$5, C9:J9-ға көшіреміз: баған ауысады, жолдар қалады.
Жауабы: =B$4+B$5; қыркүйекте 35 + 8 = 43.
Excel бір санды әртүрлі «киіммен» көрсете алады: сандық (100,72), экспоненциалдық — өте үлкен сандарға (500 000 000 → 5,00Е+08), бөлшек, пайыздық, күн (01.09.2018), уақыт (13:30:55), қаржылық форматтар. Таңдау орны: Ұяшықтар форматы → Сандық форматтар.
Сілтеме бір файлдың ішімен шектелмейді! Екінші файлдағы ұяшыққа «=» қойып, бірінші файлдың ұяшығын шертсең, байланыс орнайды: =[1.xlsx]Лист1!$D$4. Енді бірінші файлда доллар бағамы өзгерсе — екінші файлдағы барлық баға автоматты қайта есептеледі. Мұндай сілтеме әдепкіде абсолютті болады.
- Салыстырмалы (A1) — формуламен бірге жылжиды: қатар есептерге.
- Абсолютті ($A$1) — орнында қалады: тұрақты коэффициент, бағам, мөлшерлемеге.
- Аралас ($A1, A$1) — жартылай бекітілген: кесте торларына (Пифагор кестесі).
- Ауыстырғыш — F4, сілтеме тіпті басқа файлға да сілтей алады.
- Ереже: «не өзгеруі керек — еркін қалдыр, не өзгермеуі керек — $-мен бекіт».