Interesantais grafiks I daļa

Problēma: vajag izveidot grafiku, no datiem ar kuriem ir grūti izveidot grafiku.

Risinājums: izmantot teksta funkcijas vai COUNTIFS, grafikus un VBA.

Realizācija: Pirmkārt pārveidojam datus saprotamā veidā, otrkārt pielietojam iebūvētos grafikus.

Sāksim ar datiem. Piemērā, ko mēs apskatīsim, ir dota tabula no aptaujas anketas. Klienti ir aizpildījuši anketu par veikala pārdevējiem un atbildēja uz diviem jautājumiem:

·         Kurš ir vislabākais pārdevējs (Jānis, Olga, Jaceks vai Beata)

·         Kā Jūs raksturosiet šo pārdēvēju (Zinošs/-a, Atsaucīgs/-a, Labi ģērbies/-usies, Ieteikumi bija noderīgi, tukšums)

Mūsu uzdevums izveidot grafiku, kura ir redzams kurš no pārdevējiem ir visbiežāk pieminēts un ko par katru pārdēvēju ir pateikuši klienti.

Beigu rezultats

Problēma slēpjas tajā, ka raksturojošie rādītāji katram pārdēvējam tiek ierakstīti vienā šūnā, un mums ir jāizdomā paņēmiens, kā noteikt cik to ir katram pārdevējam.

Izejas dati

Sākuma izveidosim tukšu tabulu, lai zinātu, ka tai ir beigās jāizskatās. Es to būtu izdarījis sekojoši

	Zinošs/-a	Atsaucīgs/-a	Labi ģērbies/-usies	Ieteikumi bija noderīgi	Tukšums
Jānis
Olga
Jaceks
Beata

 Tagad ir skaidrs, ka mums vajag katrā šūnā saskaitīt cik noteikto vērtējumu ir katram pārdēvējam. Kā to izdarīt?

Ir divi paņēmieni- vienkāršais un interesantais.

Vienkāršais paņēmiens.

Excel sākot ar 2007 versiju piedāvā COUNTIF funkcijas papildinājumu, kas saucās COUNTIFS. Šī funkcija ir spējīgi saskaitīt vērtības pie vairākiem nosacījumiem vairākos apgabalos. Tātad tā ir spējīga noteikt cik reizes katrs no vērtējumiem parādījās katram no pārdevējiem. Vienīgā problēma, ka dabūt katru atsevišķo vērtējumu no šūna ar visiem vērtējumiem?

  =COUNTIFS($A$2:$A$1129;$D16;$B$2:$B$1129;"*"&E$15&"*")

$A$2:$A$1129-apgabals ar pārdevēju vārdiem;

$D16-darbinieka vārds no gala tabulas (ir iesaldēta adreses kolona, lai velkot pa labi un uz leju tiktu atlasīts vajadzīgs darbinieks);

$B$2:$B$1129-apgabals ar darbinieku vērtējumiem;

"*"&E$15&"*"-novērtējuma meklēšanas kritērijs (tiek iesaldēta rinda, lai velkot atlasītu tikai vajadzīgos novērtējumus, un zvaigznītes pirms un pēc novērtējuma ir vajadzīgi, lai meklētu garākā teksta rindā)

COUNTIFS izmantošana

 Velkam uz leju un pa labi, iegūstam tabulu ar novērtējumu biežumu katram pārdevējam.

Interesantākais paņēmiens

Gadījumā ja Jums nav 2007 vai 2010 Excel’a, var izmantot sekojošo paņēmienu.

Izmantosim vienu no Excel teksta funkcijām- SEARCH (var lietot arī FIND, bet šodien lietosim SEARCH). Ko dara šī funkcija? Tā nosaka kurā pozīcijā burtu/ciparu virknē atrodas meklētais vārds. Piemērs.

Kā strādā SEARCH

Piemērā vārds „ir” atrodas 7 pozīcijā no kreisās puses (vari saskaitīt pats, ja netici). Meklējot neeksistējošo vardu, funkcija atgriež kļūdu #VALUE.

Kā nestrādā SEARCH

Kā var izmantot šīs zināšanas? Ja funkcija atgriež kļūdu, meklētais vārds vai teikums nav izvelētajā šūnā, un pretēji. Tagad pievienosim funkciju ISERROR, lai noteikt kad SEARCH kļūdās un kad nē.

Kā strādā IFERROR

Funkcija ISERROR atgriež TRUE, ja izteiksme iekavās ir kļūdaina, un FALSE, ja nav kļūdaina. Tātad, ja ISERROR rada FALSE šūna satur meklēto vārdu un, ja TRUE- nesatur. Loģiskāk būtu, ja rezultāts rādītos otrādi, TRUE, ja satur un FALSE, ja nesatur. To var sasniegt pievienojot NOT funkciju. Tā nomaina TRUE uz FALSE un FALSE uz TRUE

NOT pielietojums

Tagad vis skan loģiskāk, ja šūna nesatur meklēto vārdu vai teikumu rāda FALSE, pretējā gadījumā rāda TRUE.

Pienāca laiks aprēķināt cik visās atbildēs parādījās katrs no novērtējumiem. Izveidojam stabiņu ar atbilžu variantiem, blakus ievadam formulu:

=SUM(--NOT(ISERROR(SEARCH(D2;B$2$:B$1129$))))

Gudrākie no mums saprata, ka šeit tiek lietoti masīvi, un palaist šo formulu varēs tikai ar Ctrl+Shift+Enter taustiņu kombināciju.

Kam ir vajadzīgi divi mīnusi NOT priekšā? Tas ir speciāls operators, kas pārveido loģiskās vērtības TRUE un FALSE skaitļos 1 un 0. Tas nozīmē, ka formulas daļa --NOT(ISERROR(SEARCH(D2;B$2$:B$1129$))) beigas atgriež rindu ar 0 un 1, kur 0 nav teksts nav atrasts un 1 teksts ir atrasts. Beidzot SUM funkcija saskaita 0 un 1 iegūstot kopējo atrasto sakritību summu. Iegūto rezultātu var pavilkt uz leju, un uzzināt cik katru variantu parādījās visās atbildēs.

Rezultāti

Lai uzzinātu, cik katra vērtējuma bija konkrētam pārdevējam ir nepieciešama papildus pārbaude. Tagad nepietiek tikai ar to, ka šūna satur meklēto vērtējumu, tagad vajag, lai līdzās būtu meklētais vārds. Pirmais solis ir izveidot tabulu, ka tas ir parādīts augstāk. Katras šūnas vērtību atrodam ar formulu :

=SUM(($D9=$A$2:$A$1129)*NOT(ISERROR(SEARCH(E$8;$B$2:$B$1129))))

Tabulas sastādīšana

Klāt pie iepriekšējās nāk ($D9=$A$2:$A$1129) ar kuras palīdzību mēs nosākām vai meklētais pārdevējs ir dotajā šūnā. Šīs formulas rezultāts ir rinda ar TRUE un FALSE. Reizinot to ar iepriekšējās formulas rezultātu tiek iegūta rinda ar 0 un 1, kas atbilst pieņēmumam, ka 0 nesakrīt meklētais vērtējums un/vai nesakrīt meklētais pārdevējs, 1 sakrīt gan meklētais pārdevējs, gan vērtējums. Neaizmirstam nospiest Ctrl+Shift+Enter un pavilkt pa visu tabulu. Tabula ar datiem ir gatava.

Tabula ir gatava

 Nakamajā rakstā taisīsim grafiku.