LibreOffice Calc ja VLOOKUP

[Tupsa]

En tajua, miten saan alla olevan taulukon ryhmiteltyä oikein. Eli Group pitäisi saada vastaamaan ID:tä oikein. Nyt esim. näet Item ID-ja ID (Myynti) -sarakkeita verratessasi, että Group yhden kohdalla olisivat tuotteet 1, 2, 3, 4, 5, vaikka pitäisi siis olla 1, 32, 33, 34 ja 75. Eli tuo Group sarake tulisi päivittää vastaamaan ID (Myynti kenttää).

Ilmeisesti pitää käyttää VLOOKUP funktiota, jota en ymmärtänyt vaikka olen lukenut ja katsonut ohjeita tuntikausia.

NewGroup sarake on lisätty muuten vain, siellä ei välttämättä ole edes käyttöä.

 

[Torak]

Miksi ihmeessä group sarake tulisi vastata ID myynti kenttää?
Silloihan luodaan kaksi identtistä saraketta.

Nyt pitäisi miettiä miten tuo Group eli ryhmä määritellään.
Esimerkiksi: Yleisesti voidaan olettaa että jokin asia (Item) kuuluu johonkin ryhmään (Group).
Tällöin tarvitaan uusi taulukko joka määrittelee Item ja group välisen riippuvuus suhteen.

Näin sitten olemme käytännössä luoneet kahden taulukon tietokannan.

 

[Tupsa]

@Torak , nyt Group sarake on väärin koska se ei vastaa myynti id:itä. Esim. id 2 ei kuulu ryhmään 1.

Eli siis selvennyksenä, tuossa nuo 15 ryhmää määrittyy niin, että niissä on yksi arvokkaampi myyntikohde, minkä lisäksi koko ryhmän (yhteensä 5 myyntikohdetta) minimiarvo on maksimoitu. Eli jos tuo taulukko olisi oikein, pitäisi aluksi mennä suuruusjärjestyksessä. Eli ID 1 kuuluisi ryhmään 1, 2->2, 3->3, aina 15. saakka, minkä jälkeen tulisi enemmän vaihtelua, koska minimin maksimointi pyrkii tasaamaan ryhmien kokonaisarvot. Tämä optimointi nyt ei liity tähän taulukkopulmaan mitenkään, vaan probleema olisi tosiaan vain numeroida nuo ryhmät nyt oikein.

 

[Tupsa]

Joo, taas on katseltu tutoriaaleja tunteja, mutta harmi kyllä ei vielä ole ainakaan sopivaa löytynyt. En vain tajua, miten tuota vlookupia pitäisi tässä käyttää. Eli vielä tiivistettynä:

1. ID (Myynti) -kentän tulee pysyä muuttumattomana
2. Group -kenttä tulee päivittää

Ensimmäiset viisitoistahan menisi tuossa siis Group 1, 2, ... 15, koska hinnat on suuruusjärjestyksessä. Joten tällä hetkellä Group-kenttä on siis täysin väärin verrattuna ID (Myynti) -kenttään.

Item ID-kenttä jää tämän toimenpiteen jälkeen tarpeettomaksi, kun ID (Myynti) arvojen -kohdille saadaan oikea Group arvo.

 

[Marquette]

Jos id myynti = group viiteentoista saakka niin miten sen pitäisi sitten mennä 16:sta eteenpäin? Auttaisin mutta en tajua mikä on ongelmana tai haluttu asia ..

 

[Torak]

Eli tarkoitin ideana tällästä jossa ryhmä määritellään jokaiselle tavaralle erikseen erillisissä taulukossa.
Eli syötetään tavaran ID ja se hakee muut tiedot toisesta taulukosta.

 

[Tupsa]

@Marquette , siis menee tuon taulukon mukaan. Asiaa on todella vaikea selittää, koska itse sen ymmärrän täysin, mutta sitä on vaikea selittää.

Mutta siis, Group sarake on väärin. Tai siis oikein sillä tavalla, että nyt se kertoo, mitkä ID:t pitää olla siinä ryhmässä. Esim. ryhmässä yksi pitää olla ID:t 1, 32, 33, 34 ja 75. Mutta nyt Group tunnukset esiintyvät päällekkäisillä rivellä. Kuten näet, sarakkeessa ID (Myynti) on aivan eri ID, kuin mitä pitäisi olla.

Tarkoituksena on siis saada taulukko järjestettyä Group-tunnusten mukaan. Näin nähdään, ja voidaan analysoida helposti, mitkä kaikki id:t kuuluvat kyseiseen ryhmään.

 

[Intta83]

Eli haluat taulukossa on kukin tuote järjestyksessä.syötät tiedot vetämällä Hiiri alakulmaan ja tulee semmoinen työkalu jolla pitäis onnistua. Se opetetaan Excel kursseilla atk ajokortti.

 

[Marquette]

"sarakkeessa ID (Myynti) on aivan eri ID, kuin mitä pitäisi olla" --> eli se on sittenkin se sarake joka on väärin ???

"Group sarake on väärin" --> group-sarake on väärin ???

"ryhmässä yksi pitää olla ID:t 1, 32, 33, 34 ja 75" --> voiko asian päätellä taulukostasi ilman että täytyy kysyä sinulta mikä kuuluu mihinkin ???

Tuota noin, sinun pitäisi kyetä selittämään ongelma selkeämmin? Ongelmana tämä ei ole varmaankaan kovin vaikea.

 

[Tupsa]

@Intta83 , pellkkä järjestäminen ei auta, koska ID:t eivät vastaa nyt tosiaan. Group sarake täytyisi päivittää vastaamaan ID (Myynti) sarakkeita Item ID -sarakkeen mukaan.

@Marquette , eli Group on oikein suhteessa Item ID -sarakkeeseen, mutta väärin suhteessa ID (Myynti) sarakkeeseen.

• Group-sarake on oikein: suhteessa Item ID -sarakkeeseen
• Group-sarake on väärin: suhteessa ID (Myynti) -sarakkeeseen

Jos ja kun taulukko saadaan muokattua oikealla tavalla, Item ID-sarake jää tarpeettomaksi. Tuossa tilassa se vain kertoo, mitkä kaikki ID:t kuuluvat mihinkin ryhmään (Group).

 

[Tupsa]

@Marquette No niin, eli tässä täytin taulukkoa vähän matkaa käsin, jos se yhtään auttaisi asian ymmärtämistä.

Eli siis 1) katsotaan sarakkeesta ID (Myynti) ensin joku numero, vaikka 10. Sen jälkeen 2) katsotaan sarakkeesta Item ID sama numero. Sen jälkeen 3) katsotaan sen kohdalta sarakkeesta Group, mihin ryhmään Item ID kuuluu. Lopuksi 4) kirjoitetaan ryhmän numero sarakkeeseen NewGroup.

 

[Torak]

Tarkoituksena on siis saada taulukko järjestettyä Group-tunnusten mukaan. Näin nähdään, ja voidaan analysoida helposti, mitkä kaikki id:t kuuluvat kyseiseen ryhmään.

Klikkaa jotain group tunnuksen alla olevaa tietuetta ja paina sort (Järjestä).
Tällöin oletuksena ne menee pienemmästä suurimpaan.

 

[Tupsa]

@Torak , tuo ei valitettavasti auta, koska nyt Group siis vasta Item ID:tä, mutta ei ID (Myynti) -saraketta. Eli Group pitäisi päivittää kuten tuossa hieman ylempänä edellisessä viestissäni kerroin.

 

[bindi]

Onnistuisiko index/match funkkareilla?

 

[Marquette]

Nyt taidan ehkä tajuta tai sitten en, mutta vlookuppia ainakin excelillä käyttäessä pitää olla vasemmalla se sarake, joten monistin item id eli P-sarakkeen tuonne N-sarakkeeseen

Onko tämä nyt siellä päinkään?

En käytä Libreofficea ...

 

[Marquette]

XLOOKUP pia käyttäessä menisi helpommin mutta en jaksa tutkia onko Libressä sellaista

 

[Marquette]

Ja korjaus kaavaan vielä

kaava pitää olla =VLOOKUP(S7;$N$7:$O$16;2;0)

 

[Tupsa]

@Marquette , on Libressä tuollainen xlookup.

 

[Marquette]

Ok xlookupilla ylimmän rivin kaava olisi

=XLOOKUP(S7;$P$7:$P$16;$O$7:$O$16)

eikä tarvi monistettua N-saraketta

 

[Tupsa]

@Marquette, ei ihan mennyt nyt oikein. Tuo group numero pitää hakea tuosta group sarakkeesta. Esim. tuon ID (Myynti) nro 16 kohdalla pitäisi olla NewGroup sarakkeessa tässä tapauksessa 15. Nyt tuo näyttää vain kopioivan tuolta ID (Myynti) sarakkeesta nuo numerot.

Laitoin näin sen kaavan: XLOOKUP(S2; $P$2:$P$76; $O$2:$O$76)

 

[Marquette]

Esimerkissäsi on 16 4 4 ja niistä pitäisi jotenkin saada aikaan siis 15 ?

Olemme alkupisteessä, eli en ymmärrä ollenkaan mitä pitäsi saada aikaan.

 

[Tupsa]

@Marquette , ei vaan ryhmiä on yhteensä 15 (1-15). ID:ietä on yhteenä 75. Ryhmässä on aina 5 ID:tä. Tarkoittaa siis sitä, että jokaiseen ryhmään kuuluu viisi eri ID:tä.


Otetaanpa uudestaan.

1) Katsotaan ensin saraketta ID (Myynti). Yksinkertaisuuden vuoksi nyt vaikka 1.

2) Seuraavaksi katsotaan saraketta Item ID. Haetaan sieltä sama numero.

3) Katsotaan mihin ryhmään se kuuluu, eli Group saraketta.

4) Se kuuluu ryhmään yksi, koska samalla rivillä sarakkeessa Group on numero 1.

Tuostahan näkee, että ryhmässä 1 pitää siis ID (Myynti) 1:n lisäksi olla 32, 33, 34 ja 75. Toisin sanoen näiden Myynti-ID:eiden kohdalle pitää sarakkeeseen NewGroup tulostaa numero 1.

On kyllä aikamoinen älykkyystesti tullut tästä ketjusta, puolin ja toisin. Mutta jos tämä saadaan ymmärrettyä, puolin ja toisin, niin on kyllä virtuaalikaljat (tai kahvit) ansaittu.

 

[Marquette]

Ei vieläkään, käytä jotakin muuta esimerkkinä kuin ykköstä

Eli jos id myynti on x ja sitä vastaava item id on y ja sitä vastaava group on z niin uuteen sarakkeeseen pitäisi saada x, y tai z mikä niistä

 

[Torak]

Ei ymmärrä ei..

Eikös tämä ole suora kopio Group:sta ja NewGroup ovat käytännössä ihan samat. Tällä logiikalla.
Jos jotain ItemID on myyty kaksi kertaa, niin koko himmeli kostaa.

 

[Tupsa]

@Marquette , newgroup on se kenttä, joka pitää päivittää. Tässä koko taulukko ja vähän käsin täytetty, että hoksaisi, mistä ne numerot pitäisi siirtyä.

EDIT: Eipä voinutkaan liittää... joutuu ymmärtämään ilman.

Eli siis esim. ID (Myynti) 20:n kohdalle pitää sarakkeeseen NewGroup tulla numero 8. Ja mistä tuo numero 8 tulee? Sarakkeesta Item ID.

Ensimmäisenä siis katsotaan tuo ID (Myynti) numero. Sitten katsotaan sama numero sarakkeesta Item ID ja sen vierestä (Group) tuo numero 8, joka siis menee ID (Myynti) 20 kohdalle sarakkeeseen NewGroup.

Kts. kuva!!!

 

[Marquette]

Solu T19

=XLOOKUP(O19;$P$19:$P$43;$Q$19:$Q$43)

Mutta vaihdat 19 ja 43 tilalle oman taulukkosi ekan ja viimeisen rivin numeron.

Jos ei niin joku toinen voi jatkaa arvailua.

 

[Tupsa]

@Marquette on nero! Nyt näyttää toimivan.

 

[Marquette]

Huoh

 

[Tupsa]

Hyvinhän ne järjestyy, mutta semmoinen pieni esteettinen juttu. Nyt joutuu kopioimaan nuo pellkät luvut uuteen sarakkeeseen. Onko mahdollista jotenkin "lukita" nuo luvut niin, ettei solujen arvoja lasketa uudestaan? Sama koskee myös ID (Myynti) -kenttää, koska ne lasketaan riviltä (row()-1). (Huom. kuvassa ei järjestetty vielä NewGroup (Sort) -kentän mukaan.

 

[otaar]

Tässä on selkeästi menty perse edellä puuhun ja nyt yritetään päästä korkeammalle vaikka latva on jo taipunut maahan.
nähdäkseni faktat ovat seuraavat:
1. 75 riviä raaka-dataa, mahdollisesti enemmän tai tulevaisuudessa se voi jopa muuttua, tuotu varmaankin csv:nä ja tehty se yksi asia oikein että ei ole suoraan siihen tuodun datan taulukkoon lähdetty rakentamaan kaavoja
2. uuteen taulukkoon tuotu soluviittauksin raaka-data olennaisin osin(about sarakkeet a-c), mahdollisesti lisätty otsikkorivi, tämäkin ehkä oikein
3. jotain perversioita(minimin maksimointi) toteutettu sarakkeissa d-m, jotta data ryhmitelty 15 ryhmään, jotka typerästi nimetty 1-15 siinä toivossa että ryhmien määrän toivotaan muuttuvan dynaamisesti joskus
4. päädytty meille näytettyyn sarakkeeseen n, jolla on päätetty järjestää(sotkea) koko taulukko isoimmasta pienimpään debuggaustarkoituksessa, joka on ollut virhe
5. O2 soluun laitettu =O1+1 kaava tai jotain yhtä pervoa ja kahvailtu alas jolloin saatu järjestysnumerot N-sarakkeen arvoille
6. 3. kohdan saadut ryhmittelyt kopypastettu sarakkeisiin P ja Q.
7. S sarakkeeseen saatu lookupilla ryhmittelyt, jotka liitetty ilman kaavoja sarakkeeseen R
8. lopulta yritetään epätoivoisesti saada sekoitettua taulukkoa vielä enemmän järjestämällä rivit R sarakkeen mukaan...

Menikö oikein?

Luultavasti saat haluamasi taulukon tekemällä pivot taulukon sarakkeista a-m ja järjestämällä sen

 

[Sallys]

@Marquette , ei vaan ryhmiä on yhteensä 15 (1-15). ID:ietä on yhteenä 75. Ryhmässä on aina 5 ID:tä. Tarkoittaa siis sitä, että jokaiseen ryhmään kuuluu viisi eri ID:tä.


Otetaanpa uudestaan.

1) Katsotaan ensin saraketta ID (Myynti). Yksinkertaisuuden vuoksi nyt vaikka 1.

2) Seuraavaksi katsotaan saraketta Item ID. Haetaan sieltä sama numero.

3) Katsotaan mihin ryhmään se kuuluu, eli Group saraketta.

4) Se kuuluu ryhmään yksi, koska samalla rivillä sarakkeessa Group on numero 1.

Tuostahan näkee, että ryhmässä 1 pitää siis ID (Myynti) 1:n lisäksi olla 32, 33, 34 ja 75. Toisin sanoen näiden Myynti-ID:eiden kohdalle pitää sarakkeeseen NewGroup tulostaa numero 1.

On kyllä aikamoinen älykkyystesti tullut tästä ketjusta, puolin ja toisin. Mutta jos tämä saadaan ymmärrettyä, puolin ja toisin, niin on kyllä virtuaalikaljat (tai kahvit) ansaittu.

Kannattaa harkita selkeämmän taulukon tekemistä. Eihän tuossa ole mitään järkeä kun kukaan ei ymmärrä, etkä edes itse osaa selittää sitä.

 

[Tupsa]

@Sallys, itse nimenomaan ymmärsin, mutta en osaa selittää. Taulukkoa voi kehittää paremmaksi jopa kuukausia, mutta menee idea siltä, jos tarkoituksena on vain hieman harrastella tasaamalla myyntierien hintaa. Käsin laskemalla siinä taas menisi ikuisuus kokeilemalla. Siis koko hommanhan olisi voinut tehdä kiinanpojan tekoälyllä, mutta otettiin sieltä vain tuo "optimointiosuus". Sentään jotain haluttiin jättää itselle tehtäväksi.

Mutta tästä on jo ollut hyötyä. On opittu jotenkuten käyttämään nyt taas uutta kaavaa.

@otaar , melkein muttei ihan!

Eli tuossa 75 myyntikappaletta haluttu järjestää tasakokoisiin ja mahdollisimman tasahintaisiin ryhmiin. Tasahintaisuus oli tässä se "minimin maksimointi". Eli ettei eri ryhmien välillä olisi kovin suuria eroja. Joka ryhmässä oli yksi arvokkaampi tuote ja loput edullisempia. Tuota maksimointia muka yritin (ai:n ohjeistamana) Calcissa, mutta ei sitä kässännyt, tai en osannut noudattaa riittävästi ohjeita. Joten ai teki sen varsinaisen optimoinnin ja sieltä liitin sitten ne 75 arvopari tuohon taulukkoon.

 

[Marquette]

Excel tai Libre solveri minimoi tuollaiset varmaankin 1..10 sekunnissa, kun laittaa muuttujiksi binary (kuuluu tähän ryhmään 1 tai ei kuulu 0) ja sellaista. Mutta en voi neuvoa sen tarkemmin. Tuo on laaja ja mielenkiintoinen alue.