| =COUNTIF(G5:G19;G5:G19) | |
| KOE1 | 3 |
| KOE2 | 2 |
| KOE3 | 4 |
| KOE4 | 1 |
| KOE5 | 2 |
| KOE5 | 2 |
| KOE7 | 1 |
| KOE8 | 1 |
| KOE9 | 1 |
| KOE1 | 3 |
| KOE2 | 2 |
| KOE3 | 4 |
| KOE3 | 4 |
| KOE3 | 4 |
| KOE1 | 3 |
Pikainen Pivot-perehdytys
Oletetaan, että data on kuten alla. Valitse joku solu tuosta nimien alueelta. Klikkaa yläpalkista Lisää -> Pivot-taulukko ja OK.
Pivot aukeaa uuteen taulukkoon. Vedä oikeasta reunasta "Pivot-taulukon kentät"-otsikon alta "Nimet" noihin laatikoihin kuvan mukaisesti:
Pitäisi näyttää sitten tältä:
Eihän tuo puolipiste mahda olla pilkku suomenkielisessä excelissä? Eli ei ; vaan , (=COUNTIF(G5:G19,G5:G19) )?
Ei tuossa mene mikään väärin. "Normaali" tapa käyttää COUNTIFiä on antaa sille arvo jota etsitään ja alue jolta sitä etsitään ja se laskee kuinka monta kertaa tuo arvo alueella esiintyy. Sille voi myös antaa alueen (array) jota etsitään alueelta (kuten esimerkissäsi) ja se palauttaa silloin alueen. Jos painaisit tuon kaavan antamisen jälkeen ctrl+shift+enter näkisit minkälaisen alueen se palauttaa. Koska tuossa tapauksessa yritetään palauttaa aluetta yhteen soluun on vastaus aina 0.
Sub Auto_Open()
Application.OnKey "%{HOME}", "TS_Laske_Uniikit_Nimet"
End Sub
Sub TS_Laske_Uniikit_Nimet()
Application.OnKey "%{HOME}", "TS_Laske_Uniikit_Nimet"
Dim TS_dictNimet As Object
Set TS_dictNimet = CreateObject("Scripting.Dictionary")
Dim r2 As Long
Dim rng As Range, Rng2 As Range, rngSelect As Range, cell As Range
Set rng = Selection
Set rngSelect = Nothing
For Each Rng2 In rng
If Rng2.Value <> "" Then
If rngSelect Is Nothing Then
Set rngSelect = Rng2
Else
Set rngSelect = Union(rngSelect, Rng2)
End If
End If
Next Rng2
Dim luku1 As Long: luku1 = rngSelect.CountLarge
Dim data() As String
ReDim data(1 To luku1)
Dim name As Long: name = 1
For Each cell In rngSelect
data(name) = cell.Value
name = name + 1
Next cell
TS_dictNimet.CompareMode = vbTextCompare
Dim Nimet As String
For r2 = 1 To UBound(data)
Nimet = data(r2)
TS_dictNimet(Nimet) = TS_dictNimet(Nimet) + 1
Next
Sheets.Add.name = "Uniikkien_Nimien_Esiintymät"
Sheets("Uniikkien_Nimien_Esiintymät").Range("A1").Resize(TS_dictNimet.Count, 1).Value = WorksheetFunction.Transpose(TS_dictNimet.Keys)
Sheets("Uniikkien_Nimien_Esiintymät").Range("B1").Resize(TS_dictNimet.Count, 1).Value = WorksheetFunction.Transpose(TS_dictNimet.Items)
End SubJoo, itse törmäsin samaan joskus ja meni kohtalainen tovi selvitellessä...
=INDEX($P5#;SEQUENCE(ROWS($P5#))) & ": " & INDEX($Q5#;SEQUENCE(ROWS($Q5#))) & "/"& INDEX($R5#;SEQUENCE(ROWS($R5#)))Kiitokset nopeista vastauksista. En suoraan saanut noilla tehtyä mitä halusin mutta löysin Pivotin ja hetken yrityksen ja erehdyksen kautta onnistuin siinä mitä halusin ja sain pari lisätoimintoakinPivot tai uusilla array-funktioilla näin:
P5 kaava: =SORT(UNIQUE(Table1[Vaihe]))
Q5 kaava: =COUNTIFS(Table1[Status];1;Table1[Vaihe];P5#)
R5 kaava: =COUNTIF(Table1[Vaihe];P5#)
Tuo taulukon nimi tietysti Table1.
Edit: hidas...
Edit 2: Jos haluat alkuperäisen pyynnön mukaisen esitystavan yhteen soluun noilla array-funktioilla, saat ne näinkin yksinkertaisesti:
Solun S5 kaava:Koodi:=INDEX($P5#;SEQUENCE(ROWS($P5#))) & ": " & INDEX($Q5#;SEQUENCE(ROWS($Q5#))) & "/"& INDEX($R5#;SEQUENCE(ROWS($R5#)))
Näyttää tältä:
= Table.SelectRows(#"Removed Columns", each ([Nimi] <> "Nimi 1" and [Nimi] <> "Nimi 11" and [Nimi] <> "Nimi 15" and [Nimi] <> "Nimi 17" and [Nimi] <> "Nimi 23"))= Table.SelectRows(#"Removed Columns", each List.Contains(PoistettavatNimet,[Nimi])=false)Oikeastaan sinun tarvitsee vain saada lista suodatettavista nimistä power queryyn ja käyttää sitä listaa filtterinä manuaalisen filtterin sijaan.
Eli ensin lista:
Tee vaikka toiselle välilehdelle taulukko jonka otsikko on Nimet, sen alla poistettavat nimet ja nimeä se taulukko vaikka PoistettavatNimet.
Tee tälle taulukolle uusi kysely eli Data -> From Table/Range -> Transform data. Tämä avaa taas power query -editorin. Mene transform-välilehdelle ja paina Convert to list. Tämä tekee taulukon datasta listan jota voidaan käyttää M-koodissa. Tämä lista pitäisi olla näkyvissä vasemmalla queries-paneelissa:
Nyt kun meillä on lista nimistä, käytetään sitä M-koodissa klikkailtujen filtterien sijaan. Mene alkuperäiseen kyselyyn ja muokkaa sitä steppiä jossa suodatus tapahtuu. Jos sinulla ei ole kaavariviä näkyvissä, paina View -> Formula bar päälle. Siellä todennäköisesti lukee jotain allaolevan kaltaista:
Koodi:= Table.SelectRows(#"Removed Columns", each ([Nimi] <> "Nimi 1" and [Nimi] <> "Nimi 11" and [Nimi] <> "Nimi 15" and [Nimi] <> "Nimi 17" and [Nimi] <> "Nimi 23"))
Tuo siis tutkii edellisen stepin tulosta (#"Removed Columns") rivi riviltä onko jollain rivillä jokin noista nimistä ja poistaa ne rivit. Käytetään tämän kaavan sijaan itse kirjoitettua kaavaa jossa käytetään aiemmin luotua listaa sen sijaan että lueteltaisiin kaikki erikseen:
Koodi:= Table.SelectRows(#"Removed Columns", each List.Contains(PoistettavatNimet,[Nimi])=false)
Tuo siis tutkii joka rivillä löytyykö nimi listasta ja jos löytyy, poistaa sen. Jos tuo =false olisi =true listalta löytyvä nimi säilytettäisiin ja muut poistettaisiin.
Nyt aina kun lisäät nimiä tuolle toisella välilehdellä olevalle listallle, päivittyy myös tuo filtteri automaattisesti ja samalla tietenkin lopullinen kyselyn tulos.
= Table.SelectRows(#"Muutettu tyyppi", each not Text.Contains([Name], "Arto"))= Table.SelectRows(#"Removed Columns", each List.Contains(PoistettavatNimet,[Nimi])=false)= Table.SelectRows(#"Muutettu tyyppi" , each not List.AnyTrue(List.Transform(PoistettavatNimet, (nimet) => Text.Contains([Name],nimet))))| A | B |
|---|---|
| Tunniste1 | 10:34:00 (päivämäärä sisältyy) |
| Tunniste1 | 10:50:00 |
| Tunniste1 | 12:44:00 |
| Tunniste2 | 09:32:00 |
| Tunniste2 | 12:49:00 |
| Tunniste2 | 16:00:00 |
| Tunniste3 | 08:34:00 |
Pivot helpoin. Sarake A riveille ja Min ja Max arvoiksi sarake B.
.Sub InsertRows()
'PURPOSE: Insert row(s) into the active worksheet
'SOURCE: www.TheSpreadsheetGuru.com
'Insert Row Above Row 3
Rows(3).Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
'Insert 5 Rows Above Row 3
Rows("3:7").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
End Sub
www.thespreadsheetguru.com
| 111 | 1 |
| 111 | 2 |
| 222 | 3 |
| 222 | 4 |
| 333 | 4 |
| 333 | 3 |
| 111 | 3 | 4 | ||
| 222 | 1 | 2 | ||
| 333 | 1 | 2 | ||
Excel ei nyt ole välttämättömyys, mutta lienee sillä tehtävissä?
csv/excel tiedosto, jossa 2 saraketta.
ensimmäisessä sarakkeessa numerosarja ja toisessa luvut 1-4. Miten selvitän, että mitkä numeroista 1-4 puuttuu tuolta numerosarjalta?
tässä esimerkkinä taulukko.
Eli
111 numerosarjalta puuttuu 3,4
222 puuttuu 1 ja 2
333 puuttuu 1 ja 2
Nuo puuttuvat pitäisi sitten tunkea vaikka sarakkeisiin C-E, ei väliä vaikka ne tulostuu jokaiselle riville missä on tuo sama numerosarja, koska A sarakkeen duplikaatit poistamalla jää vain se yksi rivi puuttuvin numeroin.
111 1 111 2 222 3 222 4 333 4 333 3
Lopputulema jotakuinkin tämän näköinen, josta saa helposti sortattua nuo puuttuvat numerot.
Toivottavasti tästä selostuksesta saa edes selvää
111 3 4 222 1 2 333 1 2
| Kirjauspäivä | Arvopäivä |
0,069919 | 0,069919 |
0,069919 | 0,069919 |
0,069919 | 0,069919 |
0,069919 | 0,069919 |
0,069919 | 0,069919 |
0,069919 | 0,069919 |
0,069919 | 0,069919 |
0,111586 | 0,111586 |
0,111586 | 0,111586 |
0,111586 | 0,111586 |
support.microsoft.com
Menitkö tuonnissa kohtaan Muunna ja tätä kautta valitsit sarakkeet tiedon tyypin?Eip tuokaan auta.
Kun haen tiedot, kuten kuva näyttää, tulos on desimaaleja.
Myös Open Office ja Googlen Sheet avaa ne csv- filut samanlaisena.
Tuo on muuten ehkä aivan mainio ohje, mutta ainakaan suomenkielisen version kirjoittajalle ei ole kerrottu, että "ohjattu Tuo teksti" eli text import wizard on jo muutaman vuoden ollut "legacy"-toiminto, joka nyky-excelissä pitää erikseen enabloida:Kun Excel avaa .csv-tiedoston, se tulkitsee nykyisten tietojen oletusmuoto-asetusten avulla, miten kukin tietosarake tuodaan. Jos haluat joustavammin muuntaa sarakkeet eri tietomuodoissa, voit käyttää ohjattua Tuo teksti -toimintoa.
Tarkempi kuvaus ohjatusta CSV-tuonnista täällä:
Tekstitiedostojen (.txt tai .csv) tuominen tai vieminen - Microsoft-tuki
Jos haluat tuoda tekstitiedoston, voit avata tiedoston tai tuoda tekstitiedoston ulkoisena tietoalueena. Jos haluat viedä tekstitiedoston, käytä Tallenna nimellä -komentoa.
