Kartais "Vlookup" neveiks be jūsų fantazijos

Patarimai, ką daryti, kai naudodami "Vlookup" iš duomenų bazės norime išrinkti tik mus dominančius įrašus, tačiau mūsų ieškomi elementai (pvz., įmonės) duomenų bazėje yra pavadinti kiek kitaip.

Ankstesniame pavyzdyje pademonstravau, kaip "Vlookup" funkcija, turint asmenų sąrašą, kitoje lentelėje (duomenų bazėje) suranda kiekvieno sąraše esančio asmens telefono numerį. Tokiai paieškai atlikti mums buvo reikalingas raktas, pavyzdžiui, pavardė ir vardas, pateikti viename langelyje (turimame asmenų sąraše ir duomenų bazėje šie langeliai turi būti identiški, nes tai raktas, pagal kurį duomenų bazėje ieškomas asmuo iš mūsų sąrašo).

Tačiau dažnai pasitaiko, kad skirtingose duomenų bazėse tas pats asmuo (įmonė ar kt.) yra pavadintas skirtingai.

Pavyzdžiui, turime atrinktų įmonių sąrašą, į kurį mums reikia įtraukti kiekvienos įmonės el. pašto adresą:

Taip pat turime 10 tūkst. įmonių duomenų bazę, kurioje yra pateikti įmonių el. pašto adresai:

Kaip matote, antroje lentelėje naudojamas kitoks pavadinimo formatas, pavyzdžiui "Zzz10, UAB" vietoje "UAB Zzz10". Praktika rodo, kad dažnai nesilaikoma vieningo formato, ir net toje pačioje duomenų bazėje "UAB" gali būti prieš arba po įmonės pavadinimo, vienos įmonės pavadinimas gali būti nepaimtas į kabutes, o kitos įmonės pavadinimas gali būti tarp kabučių, kurios gali būti įvairios: "Zzz10", „Zzz10“ ar net 'Zzz10' (tad "Vlookup" traktuos, kad tai skirtingos įmonės). Vadinasi, "Vlookup" daugelio įmonių neatpažins (žr. #N/A):

Yra labai paprastas būdas įgalinti "Vlookup". Šį būdą labai dažnai naudoju praktikoje. Mes matome, kad skirtingais būdais aprašyta "UAB Zzz10" visada turės bendrą komponentą: Zzz10. Tad turime abiejose lentelėse iš pavadinimo stulpelio pašalinti visus UAB, visus kablelius, visus tarpus, ir visas kabutes. Pažymėję pavadinimo stulpelį, su "Replace All" tai padarysime per dvi minutes (keičiame į nieką, t. y. naikiname):

Voilà, turime raktą. Dėl visa ko aš visada pasilieku ir pradinį stulpelį, o raktą sukuriu naujame įterptame stulpelyje. Štai tas papildomas rakto stulpelis duomenų bazėje, kurioje ieškosime atrinktų įmonių:

Turėdami raktą, galime taikyti "Vlookup", pavyzdžiui:

+VLOOKUP(B2;DB!$B$2:$C$10001;2;0)

kur:
B2 yra tai, ko ieškome (Zzz01, kaip matyti žemiau pateiktame paveiksle);
DB!$B$2:$C$10001 yra paieškos laukas, kuriame ieškome (Zzz01 bus ieškomas pirmame paieškos lauko stulpelyje), žr. aukščiau pateiktą lentelę;
2 reiškia, kad į formulės langelį, suradus Zzz01, bus perkelta reikšmė iš paieškos lauko antro stulpelio;
0 reiškia, kad ieškome įmonės su identišku pavadinimu.

Platesnis funkcijos laukų paaiškinimas pateiktas ankstesniame pavyzdyje.

Tereikia formulę pakartoti visuose atrinktų įmonių sąrašo C stulpelio langeliuose (čia padės Ctrl+Shift+End kombinacija), ir užduotis įvykdyta:

Priklausomai nuo situacijos, rakto kūrimui gali tekti panaudoti įvairias funkcijas. Pavyzdžiui, ankstesniame pavyzdyje panaudojome "Concatenate", tačiau dažnai tenka naudoti "Right", "Left" ir kitas. Tarkime, vienoje lentelėje A stulpelyje pateikti valstybių pavadinimai tokiu formatu: LT - Lithuania, EE - Estonia ir t. t. Kitoje lentelėje valstybės pavadintos tiesiog Lithuania, Estonia ir t. t. Tad tinkamiausias raktas būtų Lithuania, Estonia ir t. t.

Kaip mums iš "LT - Lithuania" gauti "Lithuania"? Tereikia pašalinti pirmuosius 5 simbolius:

+LEN(A2) nustatys, kad "LT - Lithuania" (esanti A2 langelyje) turi 14 simbolių, vadinasi, "Lithuania" bus 9 simboliai nuo dešinės arba: LEN(A2)-5;
+RIGHT(A2;LEN(A2)-5) mums grąžins "Lithuania".

Štai, šiek tiek formulių, šiek tiek fantazijos, ir galime džiaugtis sutaupytu darbo laiku.

Galingoji "Vlookup" - programinio kodo sergėtoja

Pamokėlė apie tai, kaip naudojant funkciją "Vlookup" iš duomenų bazės išrinkti tik mus dominančius įrašus.

Funkcija "Vlookup" man primena tuos laikus, kai dar nebuvau prisilietęs prie VBA programinio kodo, bet jau niežtėjo rankas šį kodą išbandyti. Galvodamas apie kodą vis atrasdavau kokią nors esamą Excel funkciją, kuri atlikdavo tai, ką būčiau bandęs aprašyti programiniame kode.

Viena tokių atrastų funkcijų buvo "Vlookup". Tarsi programinio kodo sergėtoja, tąkart ir vėl apsaugojusi VBA kodą nuo klaviatūrą mikliai maigančių mano pirštų. Funkcija, leidžianti atlikti operatyvią paiešką didžiulėje duomenų bazėje ir sutaupiusi daugybę valandų mano laiko.

"Vlookup" naudingumą iliustruosiu pavyzdžiu. Tarkime, statistikos padalinys atrinko 1000 asmenų, kuriuos reikėtų apklausti telefonu. Tačiau atrinktų asmenų sąraše nėra pateikti telefono numeriai:

Tarkime, jūsų padalinys yra atsakingas už sąrašo perdavimą apklausėjams, t. y. jūs turite pasirūpinti, kad telefono numeriai atsirastų atrinktų asmenų sąraše. Jūs taip pat turite duomenų bazę, kurioje yra patalpinta informacija (įskaitant telefono numerius) apie maždaug 30 tūkst. asmenų:

Šioje 30 tūkst. asmenų duomenų bazėje turėtų būti ir minėti 1000 atrinktų asmenų. Tad duomenų bazėje jums reikia susirasti atrinktus asmenis ir nukopijuoti jų telefono numerius į atrinktų asmenų sąrašą. Rankiniu būdu surasti kiekvieną iš 1000 asmenų ir nukopijuoti kiekvieno asmens telefono numerį užimtų ilgokai. Darbą apsunkina ir tai, kad duomenų bazėje pirma pateiktas vardas, o po to pavardė (o atrinktų asmenų sąraše - priešingai).

"Vlookup" funkcija tiesiogine prasme jums leis atlikti užduotį per keliolika, o gal net ir kelias minutes. Tačiau pirmiausia reikia sukurti raktą, kuris geriausiai atspindėtų kiekvieną asmenį. Toks raktas galėtų būti viename langelyje pateikti pavardė ir vardas (žmonių su ta pačia pavarde ir tuo pačiu vardu bus santykinai mažai, ir tokius atvejus galima bus nesunkiai išrinkti rankiniu būdu; galima sukurti ir tikslesnį raktą, pavyzdžiui, į tą patį langelį šalia pavardės ir vardo įtraukiant dar ir atstovaujamos institucijos pavadinimą).

Dviejų ar daugiau langelių informaciją perkelti į vieną langelį padeda funkcija "Concatenate" (plačiau apie ją čia). Tad atrinktų asmenų sąraše įterpiame stulpelį "Raktas" ir D2 langelyje įvedame formulę:

+CONCATENATE(A2;" ";B2)

Tai reiškia, kad D2 langelyje atsiras A2 langelio informacija (pavardė), po vardo - tarpas (" "), o po tarpo - vardas (B2 langelio informacija).

Šią formulę turime nukopijuoti į visų dalyvių langelius. Kai turime daug įrašų, tokį kopijavimą palengvins Ctrl+Shift+End kombinacija, kuri pažymės visus žemiau esančius langelius. Kadangi E stulpelyje bus antraštė "Mob. tel.", pažymėjimas apims ir mums nereikalingą E stulpelį. Todėl laikykime Shift nuspaustą ir spustelėkime ant paskutinio mums reikalingo D stulpelio langelio - pažymėjimas susitrauks iki D stulpelio. Ctrl+V įklijuos formulę (kurią prieš tai būsime nukopijavę iš D2 langelio į kompiuterio atmintį) į visus pažymėtus langelius:

Tokį patį raktą turime sukurti ir 30 tūkst. asmenų duomenų bazėje (įterpdami naują E stulpelį ir į E2 langelį įvesdami +CONCATENATE(B2;" ";A2)):

Turėdami paruoštą raktą, galime pritaikyti "Vlookup". Tarkime, duomenų bazę esame perkėlę į tą pačią bylą (kurioje yra atrinktų asmenų sąrašas), į lapą "DB". Atrinktų asmenų sąraše (tai bus kitas lapas nei "DB")), dešiniau pirmo asmens (į E2 langelį) įvedame:

+VLOOKUP(D2;DB!$E$2:$F$30001;2;0)

kur:

pirmasis narys (D2) nurodo, kokios reikšmės bus ieškoma paieškos lauko pirmame stulpelyje, t. y. 30 tūkst. asmenų duomenų bazėje mes ieškosime tokio pat asmens, koks yra nurodytas atrinktų dalyvių sąrašo D2 langelyje (prisiminkime, D stulpelyje yra raktas);

antrasis narys (DB!$E$2:$F$30001) nurodo, koks bus paieškos laukas (kurio pirmame stulpelyje bus ieškoma pirmojo formulės nario nurodytos reikšmės); "DB!" yra nuoroda į lapą "DB"; "$" panaudojimas reiškia, kad atrinktų asmenų sąraše kopijuojant formulę į žemiau esančius langelius informacijos paieškos laukas lape "DB" nesikeis; informacijos ieškome lapo "DB" E-F stulpeliuose, nes lapo "DB" E stulpelyje yra raktas, pagal kurį identifikuosime atrinktą asmenį, o F stulpelyje yra mums reikalingas telefono numeris; informacijos ieškome lapo "DB" 2-30001 eilutėse, nes pirmas asmuo yra 2 eilutėje, o duomenų bazėje yra 30000 asmenų, tad 30000-asis asmuo bus 30001 eilutėje;

trečiasis narys (2) nurodo, iš kurio paieškos lauko stulpelio bus perkelta reikšmė į langelį, kuriame įrašėme formulę; paieškos laukas galėtų apimti daug stulpelių (tas aktualu pažengusiems naudotojams), tačiau mūsų atveju apima tik E ir F stulpelius, tad antras stulpelis yra F, iš jo ir bus imama reikšmė (šiuo atveju - telefono numeris) radus eilutę su ieškomu asmeniu; kaip minėta, asmens bus ieškoma lyginant pirmojo formulės nario nurodytą reikšmę su paieškos lauko pirmajame stulpelyje (E stulpelyje) pateiktomis reikšmėmis;

ketvirtasis narys (0) reiškia, kad ieškome identiškos reikšmės, o ne kaip galima panašesnės reikšmės.

Tereikia formulę pakartoti visuose atrinktų asmenų sąrašo E stulpelio langeliuose (čia vėl padės Ctrl+Shift+End kombinacija), ir, voilà, užduotis įvykdyta:

Kaip užsiminiau, duomenų bazėje gali būti žmonių su ta pačia pavarde ir tuo pačiu vardu, tokiu atveju formulė grąžins reikšmę "#N/A", nes nežinos, kurio asmens telefono numerį perkelti. Tokius atvejus galima nesunkiai išrinkti rankiniu būdu, arba galima naudoti tikslesnį raktą, pavyzdžiui, šalia pavardės ir vardo įtraukiant dar ir atstovaujamos institucijos pavadinimą.

Žvilgsnis į kodą. Šįkart - rašyklių mėgėjams

Ši pamokėlė apie tai, kaip, net visiškai nemokant programuoti, internete susirasti mūsų darbą galintį palengvinti programinį (VBA) kodą ir jį priversti darbą atlikti už mus (kad galėtume sutaupytą darbo laiką smagiai praleisti feisbuke).

Šiame pavyzdyje parodysiu, kaip susirasti ir panaudoti programinį kodą, kuris darbą atliks už jus. Ir tam jums visiškai nebūtina mokėti programuoti.

Turbūt esate girdėję apie skaičiuoklių "macro" objektus, kurie padeda automatizuoti atliekamas užduotis, leidžia sukurti mygtukų pagalba valdomas automatizuotas formas, pvz. automatizuotą skaičiuoklę investicijų projekto sąnaudų ir naudos analizei atlikti. Šie "macro" objektai kuriami Visual Basic for Applications (VBA) programavimo kalba, o ką turėtų daryti "macro" objektas, aprašoma taip vadinamame VBA kode.

Net ir nieko neišmanydami apie programavimą, reikiamai užduočiai atlikti skirtą VBA kodą jūs galite susirasti "Google". Šį kodą tereikia patalpinti reikiamoje vietoje (parodysiu, kur), ir galėsite džiaugtis sutaupytu laiku.

Pavyzdžiui, interneto platybėse galima rasti kodą, sumuojantį tik jūsų nurodytos spalvos langeliuose esančias reikšmes. Šis kodas labai praverčia, kai duomenų peržiūros metu svarbias mums reikšmes pasižymėjome tam tikra spalva.

Tačiau šį kartą norėčiau pralinksminti rašyklių (pvz., Word) mėgėjus - tais pačiais principais veikiantį kodą galima "susigooglinti" ir rašyklei.

Pavyzdžiui, turime Word dokumentą su 200 pastabų, surašytų komentarų "balionuose" (visai pažangų dokumentą pavyko "sugooglinti" pavyzdžio tikslais):

Gavome nurodymą parengti pastabų protokolą, perkeliant visas pastabas į vieną lentelę. Būtų labai neefektyvu tokią lentelę sudaryti kopijuojant kiekvieną pastabą atskirai iš komentarų "balionų". Tą už mus galėtų atlikti VBA kodas. Uždaviau "Google" maždaug tokį klausimą:

"vba extract all comments from word"

Iš "google" pateiktų atsakymų išsirinkau šį:

http://www.thedoctools.com/downloads/basComments_Extract.htm

Šioje nuorodoje yra pateiktas VBA kodas (ką kodas atlieka, žmonių kalba aprašyta čia). Beje, thedoctools.com puslapyje galite rasti ir daugiau naudingų kodų.

Tad, turime VBA kodą. Ką su juo daryti? Pirmiausia išsaugokime savo Word dokumentą .docm formatu (to reikia, jeigu norėsime, kad kodas liktų mūsų dokumente):

Turėdami atsivertę .docm formatu išsaugotą dokumentą, išsikvieskime langą VBA kodo įrašymui. Tam vienu metu spaudžiame Alt ir F11 (Alt nuspaudžiame pirmą ir jo neatleisdami trumpam nuspaudžiame F11). Atsivėrusiame lange spustelėkime ant "ThisDocument":

Atsidarys kitas (kodo) langas:

Į šį langą ir nukopijuokime "susigooglintą" kodą (primenu, kad "Paste" veiksmą galima atlikti vienu metu spaudžiant Ctrl ir V). Nukopijavę kodą spauskime žalią kodo paleidimo mygtuką (arba tiesiog spauskime F5):

Greičiausiai atsidarys toks langas (jei Word pats nesusipras, kurį "macro" reikia vykdyti), spustelėkime "Run":

Būsime paklausti, ar tikrai norime iš dokumento eksportuoti visus komentarus. Patvirtinkime, kad taip ("Yes"), ir galėsime džiaugtis kodo sukurtu visas pastabas vienoje lentelėje talpinančiu dokumentu:

Štai, jokio programavimo, tik keli mygtukų spustelėjimai, ir visos 200 pastabų atsidūrė vienoje lentelėje.

Loteriją surengti padės "Rand"

Ši pamokėlė apie tai, kaip kūrybiškai panaudoti funkciją "Rand", pavyzdžiui, ne tik siekiant atsirinkti audito metu tikrintinus projektus ar mokėjimo prašymus, bet ir norint surengti loteriją.

Kartas nuo karto susiduriame su situacija, kai pastebime turintys šiaip gan naudingą, bet mums nereikalingą gėrybę, pavyzdžiui, už atliktus atsiskaitymus kreditine kortele gautus kvietimus į kiną, už buvimą kredito unijos nariu paštu atsiųstą Daily Card kortelės pakuotę, gautus nuolaidų kodus ir pan.

Mielai kvietimais į kiną ar kita panašia gėrybe pasidalintume su kolegomis, tačiau ką daryti, kai du ar keli kolegos parodo susidomėjimą, tačiau išvydę, kad domisi ne vieni, visi iki vieno atsisako šios gėrybės kitų kolegų labui. Ir taip kvietimai į kiną lieka gulėti ant stalo, besibaimindami sulaukti savo galiojimo pabaigos.

Išvengti tokios užstrigusios situacijos gali padėti loterija, kurioje dalyvautų susidomėjimą išreiškę kolegos. T. y. pranešame, kad yra kvietimai į kiną, kad laukiame paraiškų iki 12 val., o paraiškų skaičiui viršijant vieną bus rengiama loterija. Pats panašiu būdu kažkada laimėjau kuponą "Laisvalaikio" kortelės nemokamam išdavimui.

Kad nereikėtų gadinti popieriaus ar šiaip atrodyti nešiuolaikiškai, tokiam tikslui labai tinkama yra "Rand" funkcija. Ją taip pat labai mėgsta visokie auditoriai ir finansų kontrolieriai, norintys atsirinkti tikrintinus projektus, mokėjimo prašymus ar sąskaitas faktūras.

Tarkime, turime štai tokį loterijos dalyvių sąrašą:

"Rand()" funkcija kiekvienam dalyviui atsitiktine tvarka priskirs skaičių intervale nuo 0 iki 1. Galime paskelbti, kad loterijos laimėtoju bus pripažintas didžiausią skaičių gavęs dalyvis. Tad dešiniau pirmo dalyvio (C2 langelyje) įvedame:

+Rand()

ir spaudžiame "Enter". Yra tik viena bėda - "Rand()" funkcijos suteiktos reikšmės iš karto pasikeis, jei faile atliksime kokį nors veiksmą, pavyzdžiui, ką nors ištrinsime ar failą atidarysime iš naujo. Mūsų loterijos tikslais galime tokį perskaičiavimą laikinai išjungti. Meniu juostos File kortelėje pasirenkame Options, o jame - Formulas. Ties "Workbook Calculation" pasirenkame "Manual", o "Recalculate workbook before saving" paliekame tuščią:

Dabar pertraukiame C2 langelį žemyn iki paskutinio dalyvio (taip nukopijuodami C2 langelyje įrašytą formulę). Kadangi esame išjungę perskaičiavimą, visuose langeliuose atsiras ta pati reikšmė. Tad loterijos rezultatus gausime paspaudę "F9" (tai privers funkciją "Rand()" pateikti naujas reikšmes). Matysime kažką tokio:

Jei norime dokumentuoti rezultatus, galime loterijos rezultatus nukopijuoti į gretimą (D) stulpelį reikšmių formatu (pasirinkdami "Paste Special", o jame - "Values"). Dabar D stulpelyje loterijos rezultatai išliks nepasikeitę net vėl įjungus automatinį perskaičiavimą:

Kad nereikėtų varginti akių ir ieškoti laimėtojo, galima visus dalyvius išrūšiuoti mažėjančia tvarka pagal loterijos metu suteiktą reikšmę. Pasižymime visą mūsų lentelę, meniu juostos Data kortelėje atidarome Sort dialogą, ir pasirenkame rūšiuoti pagal D stulpelį (Column D) mažėjančia tvarka (Largest to Smallest):

Štai ir turime laimėtoją - Tomą Pavardenį:

Jei bandysite pakartoti šį pavyzdį, laimėtojas greičiausiai bus kitas, nes tokia ir yra funkcijos "Rand" paskirtis.

"Concatenate" ir "LEFT" vienoje formulėje

Ši pamokėlė apie tai, kaip naudojant funkciją „Concatenate“ sujungti kelių langelių informaciją į vientisą darinį, iš kai kurių langelių imant ne visą esantį įrašą, o tik dalį įrašą sudarančių simbolių.

Ankstesniuose pavyzdžiuose atskirai buvo panaudotos "Concatenate" ir "RIGHT" funkcijos. "Concatenate" funkcija sujungia kelių langelių informaciją ir įterpia reikalingus jungiamuosius žodžius ar simbolius, o "RIGHT" iš nurodytame langelyje esančio įrašo paima nurodytą skaičių įrašo pabaigoje (t. y. dešinėje pusėje) esančių simbolių.

Turbūt nenustebinsiu pasakydamas, kad taip pat yra ir "LEFT" funkcija, kuri, žinoma, veikia atvirkščiai nei "RIGHT" funkcija. Dažnai tenka kurią nors šių funkcijų naudoti kartu su "Concatenate".

Tarkime, skaičiuoklėje turime renginio dalyvių sąrašą, kuris apima kelis šimtus dalyvių (jau "Concatenate" pavyzdyje matyta lentelė):

Tarkime, klientas, kuriam padedame organizuoti renginį, staiga pradeda reikalauti, kad dalyvių sąrašas jam būtų pateiktas MS Word dokumente tokiu formatu:

Pavardė V., mob. tel. +370 zzz zz zz

Pavyzdžiui:
Mačiekus T., mob. tel. +370 zzz zz zz

Jei neturėtume automatizuoto sprendimo, tai reikštų kelias valandas ar net dieną beprasmio kopijavimo. Tačiau „Concatenate“ ir "LEFT" funkcijų derinys tiesiogine prasme tai leis padaryti per keletą minučių. Dešiniau pirmo dalyvio į F2 langelį tereikia įvesti:

+CONCATENATE(B2;" ";LEFT(A2;1);"., mob. tel. ";E2)

ir paspausti „Enter", o tada pertraukti langelį žemyn iki paskutinio mūsų dalyvio (tokiu būdu nukopijuojant formulę į visus žemesnius langelius).

Štai gautas rezultatas:

Kaip matome, "Concatenate" komanda leido sujungti kelių langelių informaciją, kartu įterpiant reikalingus jungiamuosius žodžius ar simbolius. "Concatenate" komandos iššifravimas pateiktas prie "Concatenate" funkcijai skirto pavyzdžio. Priminsiu tik, kad norint į junginį įdėti savo naujai sugalvotą tekstą, jį reikia apimti į kabutes, pvz. po B2 (pavardės) įdėjome elementą " ", kuris reiškia, kad po pavardės prieš kitą elementą dar turi būti tarpas.

Kas įdomu, "Concatenate" funkcijoje kaip vieną elementų pasitelkėme "LEFT" funkciją. "LEFT" iš nurodytame langelyje (šiuo atveju - A2 langelyje) esančio įrašo paima nurodytą skaičių įrašo pradžioje (t. y. kairėje pusėje) esančių simbolių. "LEFT" funkcijoje antram nariui suteikėme reikšmę "1", vadinasi, bus perkeltas tik pirmas A2 langelyje esančio įrašo simbolis, šiuo atveju "T" raidė (jei būtume įrašę "2", būtų perkelti du pirmieji simboliai). Po "T" raidės reikalingą tašką ir kitą reikalingą prierašą (intarpą) įterpiame "Concatenate" funkcijoje po "LEFT" funkcijos kaip kitą elementą įdėdami intarpą "., mob. tel. ".

F2 stulpelyje sukurtą formulę belieka pertraukti žemyn, kol pasieksime paskutinį renginio dalyvį. Štai, per kelias minutes gavome pergrupuotą dalyvių sąrašą.

Universalusis "IF"

Ši pamokėlė apie tai, kaip kūrybiškai panaudoti funkciją "IF", pavyzdžiui, patikrinti, ar sutampa vienos lentelės tam tikrame stulpelyje pateikti įrašai su kitos lentelės tam tikrame stulpelyje pateiktais įrašais.

Turbūt esate girdėję Excel funkciją "IF". Ji tiesiog nepamainoma ir gali būti naudojama patiems įvairiausiems tikslams, panašiai kaip visapusiškai galima naudoti reklamoje rodomą universalųjį skalbiklį.

Viena man labiausiai patinkančių "IF" paskirčių yra tikrinimas, ar sutampa dviejų lentelių eilutės arba stulpeliai. Pavyzdžiui, dėstytojas vienoje lentelėje turi studentų įvertinimus už pirmą užduotį, o kitoje - už antrą užduotį. Dėstytojas norėtų išvesti įvertinimų vidurkį įrašydamas formulę ir ją pertraukdamas žemyn, tačiau nėra tikras, ar visais atvejais toje pačioje abiejų lentelių eilutėje bus tas pats studentas. Štai čia tos dvi lentelės:

Norėdami patikrinti, ar ir pirmos, ir antros lentelės toje pačioje eilutėje įvestas tas pats studentas, galima panaudoti "IF" funkciją (prieš taikant funkciją būtų pravartu abiejose lentelėse esančius įrašus pagal studento vardą išrūšiuoti didėjančia tvarka). Dešiniau antros lentelės esančiame F3 langelyje įvedame:

+IF(A3=D3;"";"SOS")

kur "+" naudojamas formulei pradėti, kai komandą renkame ranka; gali būti naudojamas ir "=", bet man lengviau nuspausti "+";
IF (_;_;_) yra funkcija, kurioje pirmasis narys atspindi, kokią sąlygą tikriname, pvz. ar A3=D3 (t. y. ar A3 langelyje įvestas tas pats studentas kaip D3 langelyje); antrasis narys atspindi, kokią reikšmę funkcija pateiks, jei sąlyga bus vykdoma (jei norime nurodyti pačių sugalvotą reikšmę, ją turime įrašyti tarp kabučių; "" reiškia, kad bus pateiktas tuščias langelis, nes tarp kabučių nieko nėra); trečiasis narys atspindi, kokią reikšmę funkcija pateiks, jei sąlyga nebus vykdoma, pvz., mūsų atveju, kai studentas bus ne tas pats (tokiu atveju mes čia prašome pateikti "SOS").

Įvedę formulę ir paspaudę "Enter", dar galime pariebinti "SOS" reikšmę sąlyginiu formatavimu. Formatuosime F3 langelį, tad grąžinkime ant jo žymeklį. Meniu juostoje, Home kortelėje, Style skirtuke susirandame "Conditional Formatting" ir spaudžiame "New Rule...".

Iššokusiame lange pasirenkame "Format only cells that contain" ir "No Blanks" (t.y. kad formatą suteiksime tik ne tuščiam langeliui):

Tada spaudžiame "Format..." ir iššokusiame kitame lange pasirenkame skirtuką "Fill" ir, pvz. geltoną spalvą:

Uždarome abu langus su "OK". Dabar langelis, jei formulės grąžinama reikšmė bus ne tuščia, pageltonuos.

Pertraukiame F3 langelio formulę ir formatą žemyn iki paskutinio studento. Matome, kad 8 ir žemesnėse eilutėse atsirado "SOS" geltoname fone:

Tai reiškia, šiose eilutėse yra skirtingi studentai. Tokiu atveju žiūrime, ką turime pirmoje tokioje eilutėje. Matome, kad studentas Vardas5 PavardėE dalyvavo sprendžiant tik antrą užduotį. Aš tokiu atveju pirmoje lentelėje šią eilutę palikčiau tuščią, žemiau esantį pirmos lentelės turinį patraukdamas žemyn (tuščia pirmos lentelės eilutė aiškiai rodys, kad toje pačioje antros lentelės eilutėje nurodytas studentas pirmos užduoties nesprendė). Tai atlikus vėl reikėtų pertraukti formulę žemyn iš to langelio, kur dar buvo fiksuojamas sutapimas (kad ištaisyti lentelės turinio traukimo žemyn metu nusimušusias formules). Iš naujo pertraukus formulę, 9 ir žemiau esančiose eilutėse nesutapimo nebelieka:

Tad tose eilutėse, kuriose nėra pateikta "SOS" reikšmė, drąsiai galima vesti įvertinimų vidurkį, o ką daryti su "SOS" pažymėtose eilutėse esančiais studentais, nuspręs dėstytojas.

Kainą apskaičiuoti padės „Right“

Ši pamokėlė apie tai, kaip naudojant funkciją "Right" (taip pat yra ir "Left") iš langelyje esančio įrašo į naują langelį perkelti tik dalį šiame įraše esančių simbolių.

Vakar draugas manęs paprašė užsukti pas pažįstamą kolekcionierių ir nupirkti keliolika proginių eurų monetų. Štai mano draugo atsiųstas keliolikos monetų sąrašas:

Belgija 2008 Human Rights - 3,90€
Italija 2008 Human Rights - 4,90€
Suomija 2008 Human Rights - 7,90€
Portugalija 2008 Human Rights - 3,90€
...
ir t. t.

Norėdamas pasitikrinti, kiek turėsiu sumokėti už šias monetas, permečiau sąrašą į skaičiuokles:

Aš norėčiau sukurti stulpelį, kuriame būtų tik kaina. Todėl B1 langelyje aš įvedu formulę:

+RIGHT(A1;5)

kur:
"+" reiškia, kad pradedame formulę (kai ją vedame ranka; vietoje pliuso tinka ir "=", tačiau man lengviau ranka įvesti pliusą);
RIGHT yra formulė, kuri iš nurodytame langelyje esančio įrašo paima nurodytą skaičių įrašo pabaigoje (t. y. dešinėje pusėje) esančių simbolių;
A1 atspindi, kad mūsų formulė analizuos A1 langelyje esantį įrašą;
5 reiškia, kad bus paimti paskutiniai 5 simboliai iš A1 langelyje esančio įrašo (t. y. 5 simboliai skaičiuojant nuo dešinės pusės);
vietoje ";" jūsų skaičiuoklėse gali tekti įvesti ","  (jeigu naudojate kitokį skaičių formatą).

Pertraukiu formulę žemyn iki paskutinės monetos ir gaunu kainų stulpelį:

Vis dėlto, B stulpelyje yra pateikiami ne skaičiai, todėl jų nesusumuosime. Todėl nukopijuoju B stulpelį į kompiuterio atmintį (kitaip, įvykdau "copy"), ir įklijuoju šią informaciją į C stulpelį specialiuoju formatu, pasirinkdamas perkelti tik reikšmes:

C stulpelis iš pažiūros atrodys lygiai taip pat, kaip B stulpelis, tačiau jame bus ne formulės, o tekstinė informacija. Iš šios tekstinės informacijos aš norėčiau išgauti skaičius. Tai padės paprastas triukas - "Find and Replace" procedūra. Pasižymiu savo C stulpelį ir naudodamasis "Find and Replace" ("Replace All") pakeičiu "," į tą patį "," (paprastai tokio sužadinimo užtenka, kad tekstas taptų skaičiumi):

Štai, jau turiu kainas man priimtinu skaičių formatu, ir galiu lengvai jas susumuoti. Turbūt pasakysite, kad greičiau būtumėte suskaičiavę rankomis? Gal ir taip, jei nesate įgudę skaičiuoklių naudotojai ir turite tik keliolika eilučių. Tačiau dažnai susiduriame su tūkstančiais eilučių, tad tokios formulės kaip RIGHT tampa nepamainomomis.

Magiškoji „Concatenate“

Ši pamokėlė apie tai, kaip naudojant funkciją „Concatenate“ sujungti kelių langelių informaciją į vientisą darinį, ir kaip šią funkciją panaudoti norint pergrupuoti turimus duomenis kita tvarka.

Dažnai susiduriame su situacija, kai turime pergrupuoti turimus duomenis kita tvarka, nes tiesiog vadovas taip liepė, arba rengiamame dokumente taip gražiau atrodo, ir dažnai tokia situacija mus įklampina į ilgą ir nuobodų rankinio kopijavimo procesą.

Tačiau elektroninėse skaičiuoklėse apstu įrankių, galinčių automatizuoti ir gerokai palengvinti tokias užduotis. Vienas tokių įrankių - mano pamėgta „concatenate“ funkcija. Ši funkcija leidžia sujungti kelių langelių informaciją į vientisą darinį, pagal poreikį tarp jungiamų langelių informacijos įterpiant jungiamuosius žodžius ar simbolius.

Pavyzdžiui, skaičiuoklėje turime renginio dalyvių sąrašą, kuris apima kelis šimtus dalyvių:

t. y. kiekvienam dalyviui apibūdinti skaičiuoklėje skirti 5 langeliai: Vardas; Pavardė; Atstovaujama institucija; Pareigos; Mob. tel.

Tarkime, klientas, kuriam padedame organizuoti renginį, staiga pradeda reikalauti, kad dalyvių sąrašas jam būtų pateiktas MS Word dokumente tokiu formatu:

Pavardė Vardas (pastabos: Atstovaujama institucija; Pareigos; Mob. tel.)

Pavyzdžiui: Mačiekus Tomas (pastabos: UAB zzz; Analitikas; +370 zzz zz zzz)
Jei neturėtume automatizuoto sprendimo, tai reikštų kelias valandas ar net porą dienų beprasmio kopijavimo. Tačiau „concatenate“ funkcija tiesiogine prasme tai leis padaryti per keletą minučių. Dešiniau pirmo dalyvio tereikia įvesti:

+CONCATENATE(B2;" ";A2;" (pastabos: ";C2;"; ";D2;"; ";E2;")")

ir paspausti „Enter", o tada pertraukti langelį žemyn iki paskutinio mūsų dalyvio.

Štai gautas rezultatas:

Kitaip sakant, langelyje F2, kuriame norime matyti pirmo dalyvio informaciją kliento pageidaujamu formatu, įvedėme komandą, kuri sujungia kelių langelių informaciją ir įterpia reikalingus jungiamuosius žodžius ar simbolius:
1) pirmiausia į F2 langelį perkeliama dalyvio pavardė (kuri yra langelyje B2). Kitaip sakant, pirmasis jungiamasis elementas funkcijoje „concatenate“ bus B2, po jo dedame kabliataškį (priklausomai nuo kalbos nustatymų gali tekti dėti kablelį), o po kabliataškio nurodysime kitą jungiamąjį elementą;
2) kadangi pavardę nuo vardo norime atskirti tarpu, turime po pavardės įterpti tarpą; tarpas bus mūsų antrasis jungiamasis elementas, jį įterpsime įrašydami: " "; t. y. kai elementą perkeliame ne iš kito langelio, o sugalvojame patys, sugalvotą elementą apimame kabutėmis (bus įterptas bet koks tekstas, kurį nurodysime tarp kabučių);
3) toliau perkeliame dalyvio vardą (kuris yra langelyje A2);
4) po dalyvio vardo norime matyti tarpą ir tekstą "(pastabos: "; tad įrašome " (pastabos: "; kaip matote, pabaigoje taip pat padedame tarpą, nes jį norėsime matyti prieš atstovaujamos institucijos pavadinimą;
5) toliau perkeliame atstovaujamos institucijos pavadinimą, kuris yra langelyje C2;
ir taip toliau.

F2 stulpelyje sukūrėme formulę, tad belieka ją pertraukti žemyn, kol pasieksime paskutinį renginio dalyvį. Štai, per kelias minutes gavome pergrupuotą dalyvių sąrašą.