Лекция · 3.5

Қолданбалы есептерді шешу

Осы уақытқа дейін біз Excel-дің жеке «құралдарын» — сілтемелер, пішімдер, функциялар, талдау — бөлек-бөлек үйрендік. Бүгін оларды БІРІКТІРІП, нағыз өмірлік есептерді шешеміз: дүкендегі тауар бағасын автоматты табу, телефон тарифін есептеу, ауа райын болжау, үшбұрыш ауданын тригонометриямен табу.

Бес қадамдық шаблон

«Қолданбалы есеп» деген қорқынышты естіледі, бірақ мәні қарапайым: информатикадан тыс салада туындаған есепті компьютермен шешу.

Анықтама
Қолданбалы есеп — адамның белгілі бір саламен (математика, физика, экономика т.б.) байланысты іс-әрекетінен туындайтын, күнделікті өмірмен ұштасқан есеп.

Кез келген қолданбалы есепті шатастырмай шешу үшін бес қадамдық шаблон қолданамыз: 1) Есептің қойылымы — «Берілгені» мен «Табу керек» бөлімдерін жаз; 2) Математикалық модель — формуланы жаз (мысалы, S = ½·a·b·sin(α)); 3) Алгоритм құру; 4) Модельді рәсімдеу — формуланы мәтін түрінде де жаз; 5) Есептеу торын құру — нақты Excel формуласын жазып, нәтижені ал.

🍲

Аналогия: бұл шаблон — ас рецепті сияқты: алдымен ингредиенттерді санайсың («Берілгені»), не пісіретініңді анықтайсың («Табу керек»), рецептті оқисың («Модель»), сосын нақты пісіресің («Есептеу»). Рецептсіз пісіру сәтсіздікке апарады, есепті де ретсіз шешу солай.

Есеп (кітап дүкені): 200 журнал (120 бет, 2000 таңба/бет) және 500 кітап (300 бет, 3000 таңба/бет). Ақпарат >3 млрд бит болса — Интернет-дүкен ашылады.
Модель: N = (200·120·2000 + 500·300·3000)·8 бит.
Есептеу: =(200*120*2000+500*300*3000)*8 = 3 984 000 000 бит.
Шешім: =ЕСЛИ(A11>3000000000;"Интернет-дүкен ашу!";"Ашуға болмайды") → «Интернет-дүкен ашу!»

ВПР функциясы

Дүкен қызметкерінің ЖИІ кездесетін жұмысы: прайс-парақтан тауар бағасын тауып, тапсырыс кестесіне қою. 100 тауар болса, әр бағаны қолмен іздеу — уақыт қорек. Excel-де бұл жұмысты автоматтандыратын арнайы функция бар.

Анықтама
ВПР() — берілген мәнді кестенің шеткі сол жақ бағанынан жоғарыдан төмен қарай іздеп, тапқан жолдағы көрсетілген бағанның мәнін қайтаратын функция.

Төрт аргументін түсінейік: =ВПР(Ізделінетін мән; Кесте; Баған нөмірі; Интервалды көру). Ізделінетін мән — не іздейміз; Кесте — қай кестеден (ыңғайлы болу үшін Ctrl+F3 арқылы атау береміз); Баған нөмірі — жауапты қай бағаннан аламыз; Интервалды көру — 0 (ЖАЛҒАН) дәл сәйкестік, 1 (АҚИҚАТ) жуық сәйкестік.

Мысал: =ВПР(B7;Прайс;2;0) — B7 ұяшығындағы атауды Прайс кестесінің 1-бағанынан тауып, сол жолдың 2-бағанындағы бағаны (120 тг) қайтарады.

📚

Аналогия: ВПР — кітапхана каталогы сияқты: кітаптың атын айтасың, каталог сол атты іздеп, дәл сол қатардағы «сөре нөмірін» саған көрсетеді.

⚠️

Жиі қате: баған нөмірін кестедегі НАҚТЫ бағанмен (мысалы, С бағаны) шатастыру — ВПР-де баған нөмірі кестенің ІШІНДЕГІ реттік орны, парақтың нақты әрпі емес (кесте B-ден басталса да, 1-баған — B).

SIN функциясы және радиан тұзағы

Үшбұрыштың екі қабырғасы мен арасындағы бұрышы белгілі болса, ауданын қалай табамыз? Формула белгілі: S = ½·a·b·sin(α). Бірақ Excel-дің SIN() функциясы РАДИАНМЕН жұмыс істейді, ал бұрыш әдетте градуспен беріледі!

Градусты радианға айналдыру формуласы: радиан = градус × π / 180. Excel-де π саны ПИ() функциясымен алынады. Сондықтан 60°-ты SIN-ге беру үшін: SIN(60*ПИ()/180).

🚩

Жиі қате: тікелей =SIN(60) деп жазу. Excel мұны «60 РАДИАН синусы» деп қабылдайды (60 радиан — бірнеше толық айналымнан да көп!), нәтиже мүлдем басқа сан шығады. Әрқашан градусты ПИ()/180-ге көбейт.

Есеп: a = 5, b = 5,5, α = 60°. Үшбұрыш ауданын тап.
Excel формуласы: =1/2*(5*5,5*SIN(60*ПИ()/180)).
Есептеу: 60° → 1,047 радиан; sin(1,047) ≈ 0,866; S = 0,5 · 5 · 5,5 · 0,866 ≈ 11,9078.
Жауабы: S ≈ 11,9078 шаршы бірлік.

Телефон тарифі — көп қадамды есеп

Телефон компаниясы: 300 минутке дейін — минутіне 10 теңге, ЛИМИТТЕН асқан әр минут — 25 теңге. Мұнда екі жағдай бар — лимиттен асты ма, аспады ма. Есепті төрт бағанға бөлеміз:

Баған	Формула
Негізгі баға (C)	=ЕСЛИ(B4>$C$1;$C$1$F$1;B4$F$1)
Артық минут (D)	=ЕСЛИ(B4>$C$1;B4-$C$1;0)
Артық минут бағасы (E)	=D4*$F$2
Жалпы сома (F)	=C4+E4

Байқа: лимит ($C$1) мен екі баға ($F$1, $F$2) — АБСОЛЮТТІ сілтемеде, өйткені олар БАРЛЫҚ абонентке ортақ.

Мысал: 500 минут сөйлескен абонент, лимит 300.
Негізгі баға: 300 × 10 = 3000 тг. Артық минут: 200. Артық баға: 200 × 25 = 5000 тг.
Жалпы: 3000 + 5000 = 8000 тг.

Басты идея: күрделі есеп — қарапайым қадамдарды тізбектеу ғана. Әр баған — бір нақты сұраққа жауап.

Метеорологиялық есеп — толық талдау

Соңғы, ең толық мысал: үш жылдық ай сайынғы жауын-шашын деректері бойынша статистика ШЫҒАРУ ЖӘНЕ әр айға болжам қою (құрғақшылық/қалыпты/жауын-шашынды). Бұл — бір есепте бірнеше сабақтың бәрін қолдану: статистикалық функциялар (МАКС, МИН, СУММ, СРЗНАЧ) + шартты санау (СЧЁТЕСЛИ) + кірістірілген ЕСЛИ.

Болжам ережесі: <15 мм — құрғақшылық; >70 мм — жауын-шашынды; аралығы — қалыпты. Формула: =ЕСЛИ(B2<15;"Құрғақшылық";ЕСЛИ(B2>70;"Жауын-шашынды";"Қалыпты")).

Құрғақ айлар санын =СЧЁТЕСЛИ(диапазон;"<15") бір формуламен есептейміз — қолмен санамай-ақ.

🎯 ҰБТ байланысы

Қолданбалы есептер көбіне КОМБИНАЦИЯЛЫ түрде келеді.

ВПР синтаксисі: =ВПР(мән;кесте;баған№;интервал) — әр аргументтің қызметін ажырата біл.

Градус/радиан түрлендіру: SIN(60) ≠ SIN(60°) — әрқашан ПИ()/180 есте болсын.

Абсолютті сілтеме тұрақты параметрлерге (тариф, лимит, бағам) міндетті түрде қолданылады.

Жалпы алгоритм: Берілгені → Табу керек → Модель → Формула → Есептеу.

Сабақ қорытындысы

Кез келген қолданбалы есеп бес қадаммен шешіледі: Берілгені → Модель → Алгоритм → Формула → Есептеу.
ВПР(мән;кесте;баған№;0/1) — кестеден автоматты іздеу.
SIN, COS — радианмен жұмыс істейді, градусты ПИ()/180-ге көбейту керек.
Күрделі есептер қарапайым қадамдарға бөлінеді: әр баған — бір ЕСЛИ.
3-бөлімнің барлық құралы (сілтемелер, пішімдер, функциялар, талдау) бір есепте бірге жұмыс істейді.