Soru 8
8. sorumuzla devam edelim. Gayet iyi gidiyoruz. Ancak biraz daha fazla katılım bekliyorum.
Cevabı bulamasanız bile, yorumlara fikrinizi yazabilirsiniz. En azından mantığını oluşturmaya çalışabilirsiniz 🙂
Aşağıdaki gibi bir tablomuz var.
Bu tabloda, personel verileri tekrar ediyor. Tabloyu, sağdaki küçük tabloya yinelenen değer olmaksızın ve toplam tutarları göstererek oluşturmak istiyorum.
Dosyayı buradan indirebilir, cevabınızı yorum olarak yazabilirsiniz.
CEVAP
Basit düşünün arkadaşlar. Ben makro ya da formül diye belirtmedim 🙂
Ama çok güzel çözümler gelmiş. Tek tek tebrik ederim. Aşağıdaki resmi büyüttüğünüzde çözümü görebilirsiniz.
Tablonun tüm satır ve sütunlarında tek bir formül ile:
=EĞER(HÜCRE(“süt”;J2)=10;EĞER(EĞERSAY($A$2:A2;A2)=1;A2;””);EĞER(HÜCRE(“süt”;J2)=11;EĞER(KAYDIR(J2;0;-1;1;1)””;ETOPLA($A$2:$A$29;KAYDIR(J2;0;-1;1;1);$B$2:$B$29);””);””))
=İNDİS($A$1:$A$29;EĞER(EĞERSAY($A$2:A2;A2)=1;SATIR();””);1)
Yukarıdaki formül ile isimleri başka bir sütuna aldım ve aşağıdaki formül ile isimlerin yanlarına toplam değerleri aldım.
=ETOPLA($A$2:$A$29;M2;$B$2:$B$29)
Tekrar merhaba hocam , gönderdiğim formüle ilaveten şöyle bir şey düşündüm
=EĞER($P2=İNDİS($A$1:$A$29;EĞER(EĞERSAY($A$2:A2;A2)=1;SATIR();””);1);($Q2=ETOPLA($A$2:$A$29;P2;$B$2:$B$29));””)
:))) olmadı hücreye 0 değerini yazdı
=İNDİS($A$1:$A$29;EĞER(EĞERSAY($A$2:A3;A3)=1;SATIR();””);1)
bu formülü en başa eğer kullanayım ve mantıksal sınama doğru ise kaydır ve etopla yan sütuna da toplamları aldırayım dedim yine olmadı ,
çok saçma bir şey oldu :))
sizin cevabınızı sabırla beklicem artık, iyi günler
=BENZERSİZ(A:A;YANLIŞ)&” “&ÇOKETOPLA(B:B;A:A;BENZERSİZ(A:A))
istenen bu değil ama uğraşırken tesadüfen böyle buldum
Personel 0
NAZLI TUNÇAY 126004
ÇAĞLA ALTUNKESER 108713
CUMHUR KINIK 143263
YURDUN GÜRER 147768
RAMAZAN FERHAD KANMAZ 82151
TİMUR UĞURLU 110212
AYŞE GÜL SERTKAYA 70389
0
={BENZERSİZ(A:B;DOĞRU;TOPLA.ÇAR}IM(B:B))}
=TOPLA(EĞER($J2=$A$2:$A$29;$B$2:$B$29))
ctrl shift enter
Liste önce tabloya dönüştürülür. power query sekmesindn tablodan seçilir.
-guruplandır
-guruplama ölçütü; personel
-işlem;toplam
-sutun;satış
seçilerek tablo oluşturulur.
kapat ve hedefe yükle diyerek istediğimiz yere yüklenir.
Sub yinelenen()
sonsatir = Cells(Rows.Count, “a”).End(xlUp).Row
For i = 2 To sonsatir
Cells(i, 10) = Cells(i, 1)
Next i
Range(“j2”, “j” & sonsatir).RemoveDuplicates (1)
sontablosatir = Cells(Rows.Count, “j”).End(xlUp).Row
For y = 2 To sontablosatir
Range(“k” & y) = WorksheetFunction.SumIf(Range(“a2”, “a” & sonsatir), Range(“j” & y), Range(“b2”, “b” & sonsatir))
Next y
End Sub
Bu sefer makro ile çözdüm.
Sub atlas()
Sayfa1.Range(“M1:N10”).ClearContents
Set Con = VBA.CreateObject(“adodb.Connection”)
Con.Open “provider=microsoft.ace.oledb.12.0;data source=” & _
ThisWorkbook.FullName & “;extended properties=””Excel 12.0;hdr=yes”””
Sorgu = “select (Personel), sum(Satış) from [Sayfa1$] ” & _
“group by [Personel] ”
Set rs = Con.Execute(Sorgu)
Sayfa1.Range(“M1”) = “Personel”
Sayfa1.Range(“N1”) = “Satış”
Sayfa1.Range(“M2”).CopyFromRecordset rs
Sayfa1.Range(“N2:N10”).NumberFormat = “#,##0”
End Sub
Pivot Table ile yapabiliriz en hızlı ve en kolay şekilde. iki tık yapsak yeter:)
=ETOPLA(A2:A29;J2;B2:B29)
bunu yapabildim 🙂
J2 dışındakiler sabitlenmesi gerekir. Yoksa aşağı çektiğinde Aralık kaymış olur.
Tek bir işlem, pivot olabilir mi acaba?
Hocam Merhabalar,
=EĞER(SÜTUN(J2)=10;İNDİS($A$1:$A$29;KÜÇÜK(EĞER($A$2:$A$29=$A2;SATIR($A$2:$A$29));SATIR()-1));ETOPLA($A$2:$A$29;İNDİS($A$1:$A$29;KÜÇÜK(EĞER($A$2:$A$29=$A2;SATIR($A$2:$A$29));SATIR()-1));$B$2:$B$29))
Merhaba Hocam;
Personel Sütunu için : =BENZERSİZ($A$2:$A$29)
Toplam Satış Sütunu içn ise : =ETOPLA($A$2:$A$29;J11;$B$2:$B$29)
Saygılar…
Merhabalar Hocam
Macro ile yapabiliriz
Sub Makro3()
‘
‘ Makro3 Makro
‘
‘
Range(“A1:B1”).Select
Selection.Copy
Range(“N1”).Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Range(“A2”).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range(“N2”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range(“$N$1:$N$29”).RemoveDuplicates Columns:=1, Header:=xlYes
Range(“N1”).Select
Range(Selection, Selection.End(xlDown)).Select
Range(“N1:O8”).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range(“O2”).Select
ActiveCell.FormulaR1C1 = “=+SUMIF(R2C1:R29C1,RC[-1],R2C2:R29C2)”
Range(“O2”).Select
Selection.AutoFill Destination:=Range(“O2:O8”), Type:=xlFillDefault
Range(“O2:O8”).Select
Selection.Style = “Comma”
Range(“N1”).Select
End Sub
=SUMIFS(B:B;A:A;J2)
hocam bunu yazınca geldi ama yanlış mı anladım acaba
merhabalar,
=BENZERSİZ(A2:A29)
=ETOPLA(A:B;$E11;$B:$B)
Personel
=IFERROR(INDEX($A2:$A$29;MATCH(0;COUNTIF($J$1:$J1;$A2:$A$29);0));””)
Toplam Satış
=SUMIF($A$2:$A$29;$J2;$B$2:$B$29)
Hocam, tekrar eden personel isimlerinin olduğu sütunu seçip gelişmiş filtreden bezersiz kayıtları istediğimiz sütuna kopyalarız.
Daha sonra etopla ile bu isimlerin toplamlarını yanlarına yazdırırız.
İsimleri yan tarafa nasıl getireceğimi çözemedim ama toplam satışlarını etopla ile bulabiliriz.
=ETOPLA(A:A;J2;B:B)