Excel-де формуланы қалай жазуға болады? Оқыту. Ең қажет формулалар

Қайырлы күн.

Бір кездері формуланы Excel бағдарламасында өзіме жазу мен үшін керемет нәрсе болды. Бұл бағдарламада жиі жұмыс істеуім керек болса да, мен мәтіннен басқа ештеңе толтырмадым ...

Белгілі болғандай, көптеген формулалар күрделі емес және сіз олармен, тіпті жаңа компьютер қолданушысы үшін де оңай жұмыс жасай аласыз. Мақалада мен ең қажетті формулаларды ашқым келеді, олармен жиі жұмыс істеуім керек ...

Сонымен, бастайық ...

1. Негізгі операциялар және негіздер. Excel бағдарламасының негіздерін біліңіз.

Мақаладағы барлық әрекеттер Excel 2007 нұсқасында көрсетіледі.

Excel бағдарламасын іске қосқаннан кейін - көптеген ұяшықтармен терезе пайда болады - біздің кесте. Бағдарламаның басты ерекшелігі - ол сіз жазатын формулаларды оқи алады (калькулятор ретінде). Айтпақшы, әр ұяшыққа формула қосуға болады!

Формула «=» белгісінен басталуы керек. Бұл міндетті шарт. Содан кейін сіз есептеу керек нәрсені жазасыз: мысалы, «= 2 + 3» (тырнақшасыз) және Enter пернесін басыңыз - нәтижесінде сіз ұяшықта «5» нәтижесі пайда болатынын көресіз. Скриншотты төменде қараңыз.

Маңызды! «5» саны A1 ұяшығына жазылса да, формула бойынша есептеледі («= 2 + 3»). Егер келесі ұяшыққа мәтінге «5» деп жазсаңыз - формула редакторында осы ұяшықты апарғанда (жоғарыдағы жол, Fx) - сіз «5» премьер нөмірін көресіз.

Енді ұяшықта сіз 2 + 3 мәнін ғана емес, сонымен қатар сіз қосу керек ұяшықтар санын жаза аласыз деп ойлаңыз. «= B2 + C2» делік.

Әрине, B2 және C2-де бірнеше сандар болуы керек, әйтпесе Excel бағдарламасы A1 ұяшығында нәтиже 0 болады.

Тағы бір маңызды сәт ...

Формуласы бар ұяшықты, мысалы A1 көшіріп алып, оны басқа ұяшыққа қойсаңыз, ол көшірілген «5» мәні емес, формуланың өзі!

Сонымен қатар, формула тікелей пропорцияда өзгереді: яғни. егер А1 А2-ге көшірілсе, онда А2 ұяшығындағы формула «= B3 + C3» болады. Excel автоматты түрде формуланы өзі өзгертеді: егер A1 = B2 + C2 болса, онда A2 = B3 + C3 (барлық сандар 1-ге көбейген) қисынды.

Нәтиже, айтпақшы, A2 = 0, өйткені B3 және C3 ұяшықтары анықталмаған, сондықтан 0-ге тең.

Осылайша, сіз формуланы бір рет жаза аласыз, содан кейін оны қажетті бағанның барлық ұяшықтарына көшіре аласыз - және Excel кестенің әр жолында есептейді!

Егер сіз B2 және C2-нің көшіру кезінде өзгеруін және әрқашан осы ұяшықтарға қосылуын қаламасаңыз, оларға «$» белгісін қосыңыз. Мысал төменде келтірілген.

Осылайша, A1 ұяшығын қай жерге көшірсеңіз, ол әрқашан байланысқан ұяшықтарға сілтеме жасайды.

2. Мәндерді қатарға қосу (SUMM және SUMMESLIMN формулалары)

Әрине, әр ұяшыққа A1 + A2 + A3 және т.б. формулалар жасау арқылы қосуға болады. Бірақ зардап шекпеу үшін, Excel-де сіз таңдаған ұяшықтардағы барлық мәндерді қосатын арнайы формула бар!

Қарапайым мысалды алайық. Қоймада тауарлардың бірнеше түрі бар, және әрбір өнім кг-да қанша болатынын білеміз. қоймада бар ма. Есептеуге тырысайық, бірақ барлығы кг-да қанша тұрады. қоймада жүк

Ол үшін нәтиже көрсетілетін ұяшыққа барып, формуланы жазыңыз: «= SUM (C2: C5)». Скриншотты төменде қараңыз.

Нәтижесінде таңдалған ауқымдағы барлық ұяшықтар қосылады және сіз нәтижені көресіз.

2.1. Шартқа қосымша (шарттарымен)

Енді бізде белгілі бір жағдайлар бар деп елестетіңіз, яғни. Ұяшықтардағы барлық шамаларды емес (кг, қоймада), бірақ бағасы (1 кг.) 100-ден аз болғанда ғана белгілі болады.

Бұл үшін керемет формула бар ».SUMMESLIMNБірден мысал, содан кейін формуладағы әр символға түсініктеме.

= ТҮЙМЕЛЕР (C2: C5; B2: B5; «<100»)қайда:

C2: C5 - қосылатын баған (сол ұяшықтар);

B2: B5 - шарт тексерілетін баған (яғни бағасы 100-ден кем);

"<100" - шарттың өзі, шарт тырнақшаларда жазылғанын ескеріңіз.

Бұл формулада күрделі ештеңе жоқ, бастысы пропорционалдылықты сақтау: C2: C5; B2: B5 - оң; C2: C6; B2: B5 - қате. I.e. жиынтық ауқымы мен шарттар ауқымы пропорционалды болуы керек, әйтпесе формула қатені қайтарады.

Маңызды! Сома үшін көптеген жағдайлар болуы мүмкін, яғни. Сіз 1-ші баған бойынша емес, 10-ға дейін, көптеген жағдайларды тексере аласыз.

3. Шарттарды қанағаттандыратын жолдар санын есептеу (формула COUNTIFLY)

Жалпыға ортақ тапсырма: ұяшықтардағы мәндердің қосындысын емес, белгілі бір жағдайларды қанағаттандыратын осындай ұяшықтардың санын есептеу. Кейде көптеген жағдайлар болады.

Сонымен ... бастайық.

Осы мысалда бағасы 90-нан асатын заттардың санын есептеуге тырысайық (егер қарасаңыз, осындай 2 өнім бар деп айтуға болады: мандарин мен апельсин).

Қажетті ұяшықтағы тауарларды санау үшін біз келесі формуланы жаздық (жоғарыдан қараңыз):

= ЕСЕПТІЛІК (B2: B5; «> 90»)қайда:

B2: B5 - біз белгілеген шарт бойынша олар тексерілетін ауқым;

">90" - шарттың өзі тырнақшаға салынған.

Енді мысалды біршама қиындатып, тағы бір шартқа сәйкес есептік жазбаны қосып көрейік: бағасы 90 дан асса + қоймадағы зат 20 кг-нан аз.

Формула келесі форманы алады:

= ЕСЕП (B2: B6; «> 90»; C2: C6; «<20»)

Мұнда бәрі бірдей болып қалады, тағы бір шарттан басқа (C2: C6; «<20») Айтпақшы, мұндай жағдайлар көп болуы мүмкін!

Мұндай кішкентай үстелге ешкім мұндай формулалар жазмайтыны анық, бірақ бірнеше жүз жолдардан тұратын кесте үшін бұл мүлдем басқа мәселе. Мысалы, бұл кесте көрнекі емес.

4. Мәндерді бір кестеден екінші кестеге ауыстыру (VLOOKUP формуласы)

Елестетіп көріңізші, бізге жаңа кесте, өнімге жаңа баға белгілері енгізілді. Жақсы, егер элементтер 10-20 болса, бәрін қолмен қалпына келтіруге болады. Егер мұндай заттар жүздеген болса? Егер Excel сәйкестендірілген атауларды бір кестеден екіншісіне тауып, жаңа баға белгілерін біздің ескі кестеге көшірсе, бұл әлдеқайда жылдам.

Мұндай тапсырма үшін формула қолданылады VPR. Бір кездері ол осы керемет нәрсені көргенге дейін «ИФ» логикалық формулаларымен «ақылды» болған!

Сонымен, бастайық ...

Мұнда біздің мысал + баға белгілері бар жаңа кесте. Енді біз автоматты түрде жаңа кестедегі жаңа баға белгілерін ескіге ауыстырамыз (жаңа баға белгілері қызыл болады).

B2 ұяшығына курсорды салыңыз - яғни. бірінші ұяшықта, онда автоматты түрде бағаны өзгерту керек. Әрі қарай, формуланы төмендегі скриншоттағыдай жазамыз (скриншоттан кейін ол туралы толық түсінік беріледі).

= VLOOKUP (A2; $ D $ 2: $ E $ 5; 2)қайда

А2 - жаңа баға белгілерін алу үшін біз іздейтін мән. Біздің жағдайда біз жаңа кестеде «алма» сөзін іздейміз.

$ D $ 2: $ E $ 5 - біздің жаңа кестені толығымен таңдаңыз (D2: E5, таңдау жоғарғы сол жақ бұрыштан төменгі оң жақ диагональға ауысады), яғни. іздеу жүргізілетін жерде. Осы формуладағы «$» белгісі осы формуланы басқа ұяшықтарға көшіргенде қажет - D2: E5 өзгермейді!

Маңызды! «Алма» сөзін іздеу сіз таңдаған кестенің бірінші бағанында ғана жүзеге асырылады, бұл мысалда «алма» D бағанында ізделеді.

2 - «алма» сөзі табылған кезде, функция таңдалған кестенің қай бағанынан (D2: E5) қажетті мәнді көшіру керектігін білуі керек. Біздің мысалда 2 (E) бағанынан көшіріңіз, өйткені бірінші бағанда (D) біз іздедік. Егер сіз іздеуге арналған таңдалған кесте 10 бағаннан тұрса, онда бірінші баған іздейді, ал 2-ден 10-ға дейін - сіз көшірілетін нөмірді таңдай аласыз.

Кімге формула = VLOOKUP (A2; $ D $ 2: $ E $ 5; 2) басқа тауар атауларына жаңа құндылықтар алмастырылды - оны бағаның басқа белгілерімен бағанның басқа ұяшықтарына көшіріңіз (біздің мысалда B3: B5 ұяшықтарына көшіріңіз). Формула сізге қажет жаңа кестенің бағанынан мәнді автоматты түрде іздейді және көшіреді.

5. Қорытынды

Бұл мақалада біз Excel-мен жұмыс істеу негіздерін, формулалар жазуды қалай бастау керектігін қарастырдық. Олар Excel-де жұмыс істейтін адамдардың көпшілігімен жұмыс істеуге тура келетін ең көп таралған формулалардың мысалдарын келтірді.

Бөлшектелген мысалдар біреуге пайдалы болады және оның жұмысын тездетуге көмектеседі деп сенемін. Жақсы тәжірибе алыңыз!

PS

Сіз қандай формулаларды қолданасыз? Мақалада келтірілген формулаларды қандай да бір жолмен жеңілдетуге бола ма? Мысалы, әлсіз компьютерлерде есептеулер автоматты түрде орындалатын үлкен кестелерде кейбір мәндер өзгерген кезде, компьютер бірнеше секундқа қатып қалады, қайта есептеу және жаңа нәтижелер көрсетеді ...