Microsoft Excel ongelma

[Eliasta Pani Karhu]

Moi!

Yritän vähän ratkoa kaavaongelmaa Excelissä, mutta taitaa mennä yli oman hilseen.

Eli tarvitsisin tietynlaista keskiarvon laskemista tiedoin jotka löytyvät useammasta eri excel- tiedostosta.

Tarvitsen tiedot koottua vaikka nyt 3 eri Excelistä. Kaikista haetaan nimet ja lukuarvo. Tämän jälkeen pitäisi koostaa kyseisiä kohtia.

Eli simppelisti se olisi tälleen:

Nyt en tiedä mitään keinoa millä saan otettua esim. tuon keskiarvolaskelman henkilö1 kohdalla, koska henkilö1:n sijainti taulukossa vaihtelee.

Nimien määrä ja lukuarvo vaihtelee, esim. helmikuussa voi olla ettei ole yhtään samaa nimeä kenellä on lukuarvo kuin tammikuussa.

Joten jos voisi jotenkin linkittää se että Excel etsii : henkilö1 - silloin se myös kopioi viereisessä solussa olevan lukuarvon - useammasta kohtaan ja summaa sen esimerkkitaulukon mukaisesti.

Nyt tuossa ylläolevassa taulukossa jouduin ottamaan jokaisen henkilön datamäärän erikseen, jotta saan heille linkitettyä keskiarvon.

Meniköhän liian sekavaksi. Noh jos joku saa selvää olisi aika jees. Kysykää jos tarvis.

edit. tietojen hakeminen muista taulukoista ei ole ongelma, tai no se on hidasta itselläni, mutta saan sen onnistumaan sentään.

 

[Vipa^]

Tässä pari linkkiä jotka varmasti auttavat aiheessa:

https://support.office.com/en-us/ar...workbook-3a557ddb-70f3-400b-b48c-0c86ce62b4f5
https://support.office.com/en-us/article/SUMIF-function-169b8c99-c05c-4483-a712-1697a653039b
https://support.office.com/en-us/article/AVERAGE-function-047bac88-d466-426c-a32b-8f33eb960cf6

Yhdistämällä SUMIF ja AVERAGE -funktiot pystyt ottamaan keskiarvon helposti.

 

[Eliasta Pani Karhu]

Tässä pari linkkiä jotka varmasti auttavat aiheessa:

https://support.office.com/en-us/ar...workbook-3a557ddb-70f3-400b-b48c-0c86ce62b4f5
https://support.office.com/en-us/article/SUMIF-function-169b8c99-c05c-4483-a712-1697a653039b
https://support.office.com/en-us/article/AVERAGE-function-047bac88-d466-426c-a32b-8f33eb960cf6

Yhdistämällä SUMIF ja AVERAGE -funktiot pystyt ottamaan keskiarvon helposti.

Kiitoksia vastauksesta. Ekaa linkkiä olen käyttänytkin juuri datan hakemiseen ja averagella laskenut keskiarvoja. Pitää katsella vielä tuo yksi linkki tässä päivän aikana.

Isoin ongelma tässä on se että lukumäärä pitää aina olla linkitettynä oikeaa nimeen. Ja nimien määrä ja niiden sijainti taulukossa vaihtelee (eli sama nimi toistuu useammassa kohdassa).

 

[Vipa^]

Ymmärsin niin, että haluat ottaa monesta eri tiedostosta joissa jokaisessa nimi esiintyy vain yhdessä sarakkeessa, ja ympätä ne toiseen, jossa nimi esiintyy monessa sarakkeessa. Jos näin, niin ei pitäisi olla mitään isompaa ongelmaa. Toki jos nimi vaihtaa paikkaa pystysarakkeittain (esim. A-sarakkeesta D-sarakkeeseen) luettavissa tiedostoissa, niin sitten voinee ongelmia esiintyä, mutta jos vain rivi vaihtuu, niin sitten tuo SUMIF-koodi toimii.

Tietysti helpottaisi hommaa, jos saisi kaikki tiedostot samoilla muotoiluilla, niin olisi tiedot aina samassa sarakkeessa.

 

[Dalle]

Jos data on tuossa muodossa niin täysin helppoa ja yksiviivaista tapaa ei taida olla. Mutta tässä nyt yksi räpellys käyttäen SUMIF ja COUNT-IF:iä. Tässä esim nimet A-sarakkeessa, luvut B-sarakkeessa, ja vastaavasti D- ja E-sarakkeet. Kriteerin voisi myös laittaa vielä soluviittauksen, jolloin kaavan voi kopioida kaikille henkilöille (lisää absoluuttiset osoitteet alueisiin tällöin).

=(SUMIF(A1:A10;"henkilö 1";B1:B10)+SUMIF(D1:D10;"henkilö 1";E1:E10))/(COUNTIF(A1:A10;"henkilö 1")+COUNTIF(D1:D10;"henkilö 1"))

Jos on mahdollista muokata tuo data niin, että kaikki nimet olisi A-sarakkeessa (kuukaudet nimien välissä) ja luvut B-sarakkeessa niin tuo olisi helppo laskea AVERAGEIF:llä.

=AVERAGEIF($A$2:$A$23;"henkilö 1";$B$2:$B$23)

 

[Eliasta Pani Karhu]

Moi,

Saako kummasakaan esimerkissä napattua tuota henkilön nimeä ja vetää se automaattisesti siihen kaavan viereen vasempaan soluun? Täten minun ei tarvisi koskaan välittää nimien päivittämisestä, koska ne automaattisesti napataan sarakkeista (tuplat tietty pois).

Kokoa- komento toimii tällaisessa aika nätisti, mutta sen kanssa rajoittuu sitten moni muu juttu.

Hitto mulla jumittaa kyllä pää tämän kanssa, periaatteessa nyt olisi vain data-arvoja & nimiä. Niistä pitäisi saada koostettua summa ja keskiarvo (mikä nyt onnistuu näillä neuvoin osittain). Mutta tämän lisäksi pitäisi tehdä if- lausekkeita mm. sellaisessa tapauksessa että jos jokin tietty arvo ei ole ylittynyt 2 eri kukkauden aikana, niin kaava ilmoittaa siitä.

Tarttisin kyl excel- koulutusta ja pahasti!

Pakko kyllä sanoa Dalle että suuret kiitokset sinulle! Kyllä tässä uusia ideoita saa, varsinkin kun tämä käyttö on lähtenyt itsellä tässä melko nollapohjalta. Jo tuo datan hakeminen toisista dokumenteista ja niiden automaattinen päivitys vei minulta oman aikansa

 

[Dalle]

Moi,

Saako kummasakaan esimerkissä napattua tuota henkilön nimeä ja vetää se automaattisesti siihen kaavan viereen vasempaan soluun? Täten minun ei tarvisi koskaan välittää nimien päivittämisestä, koska ne automaattisesti napataan sarakkeista (tuplat tietty pois).

Kokoa- komento toimii tällaisessa aika nätisti, mutta sen kanssa rajoittuu sitten moni muu juttu.

Hitto mulla jumittaa kyllä pää tämän kanssa, periaatteessa nyt olisi vain data-arvoja & nimiä. Niistä pitäisi saada koostettua summa ja keskiarvo (mikä nyt onnistuu näillä neuvoin osittain). Mutta tämän lisäksi pitäisi tehdä if- lausekkeita mm. sellaisessa tapauksessa että jos jokin tietty arvo ei ole ylittynyt 2 eri kukkauden aikana, niin kaava ilmoittaa siitä.

Tarttisin kyl excel- koulutusta ja pahasti!

Pakko kyllä sanoa Dalle että suuret kiitokset sinulle! Kyllä tässä uusia ideoita saa, varsinkin kun tämä käyttö on lähtenyt itsellä tässä melko nollapohjalta. Jo tuo datan hakeminen toisista dokumenteista ja niiden automaattinen päivitys vei minulta oman aikansa

Juu saa, senhän tossa jo mainitsinkin, elikkä tyyliin näin:
Laita henkilöiden nimet G1-alkamaan ja tämän kaavan siihen viereen ja vedät alaspäin.

=(SUMIF($A$1:$A$10;G1;$B$1:$B$10)+SUMIF($D$1:$D$10;G1;$E$1:$E$10))/(COUNTIF($A$1:$A$10;G1)+COUNTIF($D$1:$D$10;G1))

 

[rust]

Excel kursseihin ja koulutuksiin en tuhlaisi aikaa. paras kurssi on se mitä käyt juuri; pähkäillä, lukea netistä ja kysyä kun tulee seinä vastaan. Hienoimmat oivallukset ja toteutukset talteen _omaan_ säilöön. luottamukselliset tiedot voi aina korvata random datalla.
Vastauksen saamista helpottaa kun kaikesta tarvittavasta on pieni esimerkki. en ainakaan huomannut ulkoisten data ekkeleiden muodosta esimerkkiä.

 

[Eliasta Pani Karhu]

Joo olen saanut nyt tällaisen tuloksen:

Käytin kaavaa:

=(SUMMA.JOS(Taul1!$A$1:$A$31;H2;Taul1!$B$1:$B$31)+SUMMA.JOS(Taul2!$A$1:$A$30;H2;Taul2!$B$1:$B$30)+SUMMA.JOS(Taul3!A1:A22;H2;Taul3!B1:B22))/(LASKE.JOS(Taul1!$A$1:$A$31;H2)+LASKE.JOS(Taul2!$A$1:$A$30;H2)+LASKE.JOS(Taul3!A1:A22;H2))

Ja summassa vain pois tuo jako. Eli tein 3 lisäsivua, joihin lisäsin henkilöille a-b jonkin arvon ja haen sen sitten tuohon taulokkoon.

Nyt eka:

Saako tuohon H-sarakkeeseen jotenkin automaattisesti haettua nimet, siten että se etsii niitä toisista taulukoista. Kuten se tässä tapauksessa hoitaa jo laskemiset ja yhdistämisen nimeen.

Oikeassa kaaviossa kun nimiä/nimikkeitä on tuhansia, joten jos ne saisi aina automaattisesti haettua tuohon, olisi aika jees. Siten että tuplat automaattisesti suodattuu pois.

Ja sitten haluaisin tähän vielä jonkinlaista if- laskentaa. Henkilö A:n luvut ovat 1, 22, 543. Millä kaavalla saan yes/no tai vaihtoehtoisen, laskettua jos henkilöllä on esim. 0, 0, 455.

Nythän en tiedä että onko keskiarvo tullut yhdestä vai useammasta lukuarvosta. Joten pitäisi poissulkea sellaiset.

Esim. Jos henkilö A:lla olisikin lukuinaan 0, 0 ja 455 niin kaava vain sanoisin että "no" silloin tarkoittaisi että kaksi avoa olisi 0 tai vähemmän.

ps. sori suomenkielinen Excel

 

[Dalle]

Nyt eka:

Saako tuohon H-sarakkeeseen jotenkin automaattisesti haettua nimet, siten että se etsii niitä toisista taulukoista. Kuten se tässä tapauksessa hoitaa jo laskemiset ja yhdistämisen nimeen.

Oikeassa kaaviossa kun nimiä/nimikkeitä on tuhansia, joten jos ne saisi aina automaattisesti haettua tuohon, olisi aika jees. Siten että tuplat automaattisesti suodattuu pois.

Tähän ei varmasti taida kaavoilla olla mitään mahdollisuutta, VBA:lla onnistuu. Kirjoitat koodin mikä käy kaikki välilehdet missä nimiä on läpi ja lisää ne listaan ensin katsoen löytyykö ko. nimi jo listalta. Lopuksi kirjoitat listan tohon H-sarakkeeseen.

Ja sitten haluaisin tähän vielä jonkinlaista if- laskentaa. Henkilö A:n luvut ovat 1, 22, 543. Millä kaavalla saan yes/no tai vaihtoehtoisen, laskettua jos henkilöllä on esim. 0, 0, 455.

Nythän en tiedä että onko keskiarvo tullut yhdestä vai useammasta lukuarvosta. Joten pitäisi poissulkea sellaiset.

Esim. Jos henkilö A:lla olisikin lukuinaan 0, 0 ja 455 niin kaava vain sanoisin että "no" silloin tarkoittaisi että kaksi avoa olisi 0 tai vähemmän.

ps. sori suomenkielinen Excel

Voiko luku olla pienempi kuin 0? Tarkoittaako 0, että henkilöä ei ole listalla tiettynä kuukautena? Jos näin niin tuo LASKE.JOS -kaava voisi toimia.

=JOS((LASKE.JOS(Taul1!$A$1:$A$31;H2)+LASKE.JOS(Taul2!$A$1:$A$30;H2)+LASKE.JOS(Taul3!A1:A22;H2))>=2;"YES";"NO")

Ja nyt kun tätä tutkailee niin kannattanee tehdä omat sarakkeet "Luvuille" ja summalle ja keskiarvolle, eli lisäät "Lukujenmäärä" sarakkeen.
Siihen laitat ton LASKE.JOS-härpäkkeen, Summa sarakkeeseen laitat sen SUMMA.JOS-härpäkkeen. Keskiarvo sarakkeeseen laitat sitten ton päättelyn, eli

=JOS(I2>=2;J2/I2;"no")

Ton laitat K-sarakkeeseen, I-sarakkeessa "Lukujenmäärä" ja J-sarakkeessa Summa

tässä vielä havaintokuva

 

[Eliasta Pani Karhu]

Voi olla mahdollisuus että henkilö ei ole lainkaan yhdessä listauksessa tai sitten hänen arvonsa on 0. Joten se ei riitä että lasketaan löytyykö henkilö arvona taulukosta, vaan myös jos hänen arvo on 0 niin se pitäisi saada ympättyä tuohon myös.

 

[Dalle]

Voi olla mahdollisuus että henkilö ei ole lainkaan yhdessä listauksessa tai sitten hänen arvonsa on 0. Joten se ei riitä että lasketaan löytyykö henkilö arvona taulukosta, vaan myös jos hänen arvo on 0 niin se pitäisi saada ympättyä tuohon myös.

En nyt jaksa koko toteutusta alkaa suunnittelemaan, koita noilla neuvoilla jotenkin ratkaista. Alun kysymykseesi on tullut aika radikaaleja lisä "ominaisuuksia", joten voi olla että joku toinen ratkaisu voisi olla parempi kuin mitä tässä nyt olen ehdotellut.

Jos sillä 0-luvulla ei sinäänsä ole mitään arvoa niin nimet joilla lukuna 0 voisi poistaa taulukosta jo valmiiksi, tällöin tuo edellinen neuvoni voisi toimia.

 

[Eliasta Pani Karhu]

Joo ehkä jätetään tämä tähän, kiitos Dalle & muut, yritän loput ratkoa itse (eli spämmin välillä jonnekkin muualle )

 

[rust]

Saako tuohon H-sarakkeeseen jotenkin automaattisesti haettua nimet, siten että se etsii niitä toisista taulukoista. Kuten se tässä tapauksessa hoitaa jo laskemiset ja yhdistämisen nimeen.

Oikeassa kaaviossa kun nimiä/nimikkeitä on tuhansia, joten jos ne saisi aina automaattisesti haettua tuohon, olisi aika jees. Siten että tuplat automaattisesti suodattuu pois.

Tähän tyyliin saisit haettua nimet. Useampia tiedostoja saa ketjutettua iferrorilla. Jos tiedostojen määrä ja nimet vaihtelee, kannattaa luoda aputauluja hakemaan tiedostonimet. Ehkä jopa jokaisesta tiedostosta erikseen nimet aputauluihin.
=INDEX([data1.xlsx]Sheet1!$A$2:$A$20;MATCH(0;INDEX(COUNTIF(B$2:B2;[data1.xlsx]Sheet1!$A$2:$A$20);0;0);0))

Array kaavoilla voisi saada hienostuneemmin ja sortattuna, mutta usein ne hidastavat ekkeliä kohtuuttomasti. Monesti vba hoitaa homman nopeammin, ja itsekin pysyy kärryillä mitä missäkin tapahtuu. Yleensä pyrin kuitenkin välttämään vba:ta jos tiedosto menee muiden käyttöön tai yleiseen jakoon.