miten saan excelin laskemaan sarakkeessa B esiintyvien uniikkien arvojen lukumäärän sarakkeen A perusteella? Esim. sarakkeessa A on lukuja väliltä 0-1000 ja haluan tietää kuinka monta eri lukua on B-sarakkeessa kun A:n arvo on 200-300. En jaksa enää yrittää laittaa kaavaa kaavan perään....pelkät uniikit arvothan saa kun laittaa SUM(IF(FREQUENCY... mutta miten saan tämän toimimaan niin, että laskee toisen sarakkeen lukujen perusteella????
pika-apua exceliin
tollo
202433
0Äänestä
Muut lukivat myös
Odottaa vastausta
Näihin keskusteluihin ei ole vielä vastattu. Ole ensimmäinen.
Vastaukset 20
Vanhimmat
Kokeile jos-funktion käyttöä. Funktioitahan voi yhdistellä.
olen kokeillut jos -funktiota ja funktioiden yhdistelyä, mutta en onnistu luomaan sellaista joka toimisi. aina tulee vastaukseksi risuaitoja tai herjaus, että funktioita on liikaa vaikka en ole laittanut kuin max 5kpl.Jos joku onnistuu niin olisin avusta enemmän kuin kiitollinen!
ainakin jos ymmärsin oikein mitä olet hakemassa. Toki myös VBA:lla tuon voi ratkaista mutta tuohon riittää kyllä kaavatkin.
Eli sinulla on siis luvut 1-1000 A-sarakkeessa (ilmeisesti numerojärjestyksessä soluissa A1:A1000) ja sekalainen määrä lukuja B-sarakkeessa ja haluat tietää kuinka monta eri lukua on B-sarakkeessa välillä 200-300, onko näin?
IF- ja COUNTIF-kaavojen yhdistelmällä pitäisi onnistua eli vaikkapa sarakkeeseen C soluun C1 laittaa seuraavaan kaavan: "=IF(COUNTIF($B$1:$B$1000;A1)>0;1;0)".
Tuo kaava antaa luvun 1 mikäli samalla rivillä A-sarakkeessa oleva luku löytyy vähintään yhden kerran B-sarakkeesta riveiltä 1-1000 (toki enemmänkin rivejä voi tarkastella). Tuon solun C1 kun kopioi soluihin C2:C1000 ja laittaa vaikkapa soluun D1 yhteenlaskukaavan jaksolle 200-300 ("=SUM(C200:C300)") niin sillä pitäisi saada laskettua eri lukujen lukumäärän sarakkeessa B väliltä 200-300.
ei toimi!
Alkuperäinen kysymys
"miten saan excelin laskemaan sarakkeessa B esiintyvien uniikkien arvojen lukumäärän sarakkeen A perusteella? Esim. sarakkeessa A on lukuja väliltä 0-1000 ja haluan tietää kuinka monta eri lukua on B-sarakkeessa kun A:n arvo on 200-300"
Jos siis A sarakkeessa on lukuja 1-1000 numerojärjestyksessä soluissa A1:A1000 B sarakkeessa vaikka vain eri lukuja välillä 1-100 niin kaavahan ei toimi...
kaavahan vain tsekkaa A sarakkeen perusteella lukuja(tässä tapauksessa lukuja 200-300) joita ei siis esiinny lainkaan.
Itse ymmärsin,että uniikkeja haetaan B sarakeesta A -sarakkeessa olevista LUVUISTA 200-300 niitähän voi olla vain 1 tai enemmän.
Arvailuja vaan alkuperäisestä kysymyksestä... :)
P.S. huomasin,että lähettämässäni omassa koodissa väärä koodi kohdassa
Uniikit = HaeUniikit(Alue1.Value) pitäisi olla
Uniikit = HaeUniikit(Alue1)
sorry!
kiitos vastauksista!!
tätä siis tarkoitin: A-sarakkeessa mulla on tuoteryhmiä kuvaavia numeroita väliltä 1-1000. Esim tuoteryhmää X kuvaavat numerot 1-199, tuoteryhmää Y 200-399 jne. Sarakkeessa B on asiakasnumeroita (väliltä 1-999999) ja haluan tietää, kuinka monta ERI asiakasta on kullakin tuoteryhmällä ollut viime kuussa. Eli esim. kuinka monta uniikkia arvoa on sarakkeessa B, kun A on väliltä 1-199. Tästä on tarkoitus tehdä makrot niin, että kun myyntiohjelmasta ajetaan data tyhjälle sheetille, excel laskee nämä automaattisesti. Homma vaan tökkii siinä, että en saa tätä kaavaa onnistumaan. Tämänhän voisi kiertää niin, että tekis omat sarakkeet jokaiselle tuoteryhmälle johon hakee IF:llä as.nrot jos vastaavat A:n kriteerejä - dataa ja tuoteryhmiä vaan on niin paljon, etten mielellään tällaista tekis. Jos siis saatte kaavoilla tämän onnistumaan, pliis informoikaa!!! kiitos!
Muokkasin vielä vähän.Siirrät vain muuttujien arvot sopiviin soluihin.
Option Explicit
Sub AsiakkaidenMäärät()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim Teksti As String
A = Asiakkaat(1, 199)
B = Asiakkaat(200, 399)
C = Asiakkaat(400, 599)
D = Asiakkaat(600, 799)
E = Asiakkaat(800, 999)
Teksti = Teksti & "Asiakkaita tuoteryhmässä A " & A & " kappaletta" & vbNewLine
Teksti = Teksti & "Asiakkaita tuoteryhmässä B " & B & " kappaletta" & vbNewLine
Teksti = Teksti & "Asiakkaita tuoteryhmässä C " & C & " kappaletta" & vbNewLine
Teksti = Teksti & "Asiakkaita tuoteryhmässä D " & D & " kappaletta" & vbNewLine
Teksti = Teksti & "Asiakkaita tuoteryhmässä E " & E & " kappaletta" & vbNewLine
MsgBox Teksti
End Sub
Function Asiakkaat(TuoteryhmäMin As String, TuoteryhmäMax As String) As Integer
Dim Alue As Range
Dim Alue1 As Range
Dim Uniikit As Variant
Range("A:B").AutoFilter Field:=1, Criteria1:=">=" & TuoteryhmäMin, Operator:=xlAnd, _
Criteria2:="
oletkos harkinnut pivot taulukoiden käyttöä ?
Tai voisit kokeilla tämmöistä
=LASKE(JOS(TAAJUUS(JOS(A1:A20>=200;B1:B20);B1:B20);1))
tuo on matriisikaava
kuten itsekin totesit, suuremmilla rivimäärillä saattaa laskenta alkaa hidastumaan. Pari tuhatta riviä menee vielä aika helposti mutta esim. 50000 riviä saattaa alkaa jo hieman tökkimään.
Ohessa makro joka luo tarvittavat kaavat sarakkeisiin C ja D, rivejä tuossa määritelty 2000 kpl.
---------------------------
Sub UniikkiKaavat()
'
' UniikkiKaavat Macro
' Macro recorded 14.10.2004 by Excelit.fi
'
'
Alkaen = InputBox("Anna ensimmäinen tuotenumero")
Range("E2").Select
ActiveCell.FormulaR1C1 = Alkaen
Päättyen = InputBox("Anna viimeinen tuotenumero")
Range("F2").Select
ActiveCell.FormulaR1C1 = Päättyen
Range("C1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]>=R2C5,IF(RC[-2]0,IF(COUNTIF(R1C3:R50000C3,RC[-2])>1,COUNTIF(R1C3:RC[-1],RC[-2]),COUNTIF(R1C3:R50000C3,RC[-2])),0)"
Range("C1:D1").Select
Selection.Copy
Range("C2:D2000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("E1").Select
ActiveCell.FormulaR1C1 = "Alkaen"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Päättyen"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Asiakkaita kpl"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1]C[-3]:R[49998]C[-3],1)"
Dim UniikkejaKpl As String
UniikkejaKpl = Range("G2")
Range("A1").Select
MsgBox "Tuotenumerot " & Alkaen & " - " & Päättyen & " sisältäviä asiakkaita on yhteensä " & UniikkejaKpl & " kpl."
End Sub
---------------------------
Kokeilin tuon Exletit.Fi:n tekemän makron ja se toimii ja varmaan toimii Kundenkin tekemä (ainakin tähän asti ne on aina toimineet), mutta myös kahdella erikoissuodatuksella voidaan hakea asiakkaiden määrä. Esim:
Sarakkeessa A otsikkona "tuote"
Sarakkeessa B "asiakas"
Kirjoita soluihin D2 ja E2 "tuote"
Soluun D3 >199 , soluun E3
Kundehan oli jo käyttänyt suodatusta.
Pitää näköjään tutkia tarkemmin edelliset vastaukset.
en tiedä toimiiko se pivot tässä tapauksessa kun tarkoitus olisi tehdä makro.
tuo kaava mitä ehdotit: count(if(frequency(if... - en saanut sitä toimimaan???
kiitti!! tämä toimii!! jos täällä joku fiksu vielä keksii miten kyseinen toimitus tehdään kaavoja yhdistelemällä - kertokaa. itseä jäi vaivaamaan, pitäisihän sen onnistua niinkin!
vielä kerran kiitos kaikille avusta!
Väsäsin käyttäjän funktion...
totahan on helppo muokkailla mukaisekseen
Function Uniikit(Alkuarvo As Long, Loppuarvo As Long) As Long
Dim solu As Range
Dim Alue As Range
Dim Ainutkertainen As New Collection
Set Alue = Range("B" & Alkuarvo & ":B" & Loppuarvo)
Application.Volatile
On Error Resume Next
For Each solu In Alue
Ainutkertainen.Add solu.Value, CStr(solu.Value)
Next solu
On Error GoTo 0
Uniikit = Ainutkertainen.Count
End Function
Option Explicit
Sub Koe()
Dim Alue As Range
Dim Alue1 As Range
Dim Uniikit As Variant
Range("A:B").AutoFilter Field:=1, Criteria1:=">199", Operator:=xlAnd, _
Criteria2:="
Tällä matriisikaavalla saa sen tehtyä
{=SUMMA(JOS(PITUUS(B1:B20);1/LASKE.JOS(B1:B20;B1:B20)))}
eikös alkuperäinen kysyjä halunnut että luvut lasketaan sarakkeen A arvojen perusteella ? Miten tuo kaava huomioi sen?
Soluissa C1 ja D1 alraja ja yläraja soluun E1 seuraava kaava
{=SUMMA(1/LASKE.JOS(EPÄSUORA(OSOITE(VASTINE(C1;A1:A1000;0);2;4;2)&":"&OSOITE(VASTINE(D1;A1:A1000;0);2;4;2));EPÄSUORA(OSOITE(VASTINE(C1;A1:A1000;0);2;4;2)&":"&OSOITE(VASTINE(D1;A1:A1000;0);2;4;2))))}
@Kunde
hierottu ! Sulla on homma hallussa !
Kumminkin VBA:lla yleensä teen...
Käytin tuota sinun ensimmäistä kaavaa TULOJEN.SUMMAN yhtenä ehtona ja näyttäis toimivan.
{=TULOJEN.SUMMA((JOS(PITUUS(B1:B1000);1/LASKE.JOS(B1:B1000;B1:B1000))*(A1:A1000>C1)*(A1:A1000