جمع ستون ها با عنوان مشترک (نمونه مجموع فرعی در اکسل) [بایگانی]

amiralex

دوشنبه 27 مهر 1394, 10:16 صبح

با سلام

من در برنامه ایی که ضمیمه کردم میخوام با تغییر در ستون 0 ( نام شهر شهر) ستونهای متناظر اون جمع بشه

من فقط تونستم یک ستون رو در لحظه جمع کنم اما با استفاده حلقه نتونستم این کار رو انجام بدم

در واقع میخوام با زدن دکمه OK مثلا شهرهای هم نام ستونهای دیگه جمع بشه

نام شهر
ستون یک
ستون دو
ستون سه

آبادان
5
2
2

آبادان
6
36
42

آبادان
3
8
8

اهواز
2
74
23

اهواز
1
2
2

اهواز
2
5
4

تهران
1
2
2

تهران
5
7
7

ایلام
6
9
8

مشهد
3
12
12

مشهد
2
11
6

مشهد
1
6
6

و در نهایت در جدول Tarh به این صورت وارد بشه

نام شهر
ستون یک
ستون دو
ستون سه

آبادان
14
46
52

اهواز
5
81
29

تهران
6
9
9

ایلام
6
9
8

مشهد
6
29
24

m.4.r.m

دوشنبه 27 مهر 1394, 16:46 عصر

اینی که این طرح کردی با اونی که تو برنامته خیلی فرق داره چرا این همه از Adodc2.Recordsource استفاده کردی ؟
بانک اطلاعاتیم رمز داره نه جداول معلومه نه کلیداش . اونوقت من چطوری کمکت کنم .

amiralex

دوشنبه 27 مهر 1394, 19:22 عصر

با سلام و عرض ادب
بانک اطلاعاتی رمزش در قسمت لود فرم هست

اینم رمزش 6413455

در ضمن من اینو به عنوان یه نمونه آزمایشی ساختم تا اون مورد و تو برنامه که حجم بیشتری و نتونستم آپلود کنم استفاده کنم

فقط یه راهنمالیی بکنید که چطور باید از یه حلقه استفاده کنم به طوری که وقتی نام یه شهر تغییر کرد بره برای شهر بعدی و جمع کنه

اون جدولی که تو سوال پرسیدم ضمنی و برای مثال بود

amiralex

سه شنبه 28 مهر 1394, 11:50 صبح

لطفا این کد و بررسی کنید ببینید مشکلش کجاست که همش خطای EOF یا BOF میده

Private Sub Command36541782_Click()

On Error Resume Next

If adoPrimaryRS.RecordCount > 0 Then

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''

If adoPrimaryRS.Fields("Markaz") = Me.DataGrid3.Columns(0) Then

adoPrimaryRS.Filter = "Markaz like '*" & Me.DataGrid3.Columns(0) & "*'"
Adodc1.Recordset.MoveNext
Else

Adodc1.RecordSource = "Select * from Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc1.Refresh

Adodc2.RecordSource = "Select sum(Daryafti) as SS From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(Tolidi) as SS2 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(Tolidimomtaz) as SS3 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(GoazreshKhabari) as SS4 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(GozareshTahlili) as SS5 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(GozareshTahliliMomtaz) as SS6 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(GozareshPmomtaz) as SS7 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(AksKhabar) as SS8 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(Gtasviri) as SS9 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(Gtmomtaz) as SS10 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(GTBarjaste) as SS11 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(Dabiri) as SS12 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(FilmPosheshi) as SS13 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(Filmtolidi) as SS14 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(GMTasviri) as SS15 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(GMTmomtaz) as SS16 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(FKHbarjaste) as SS17 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(TTFarsi) as SS18 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh

VSFlexGrid.TextMatrix(1, 1) = Me.DataGrid3.Columns(0)
VSFlexGrid.TextMatrix(1, 2) = Label1
VSFlexGrid.TextMatrix(1, 3) = Label2
VSFlexGrid.TextMatrix(1, 4) = Label3
VSFlexGrid.TextMatrix(1, 5) = Label4
VSFlexGrid.TextMatrix(1, 6) = Label5
VSFlexGrid.TextMatrix(1, 7) = Label6
VSFlexGrid.TextMatrix(1, 8) = Label7
VSFlexGrid.TextMatrix(1, 9) = Label8
VSFlexGrid.TextMatrix(1, 10) = Label9
VSFlexGrid.TextMatrix(1, 11) = Label10
VSFlexGrid.TextMatrix(1, 12) = Label11
VSFlexGrid.TextMatrix(1, 13) = Label12
VSFlexGrid.TextMatrix(1, 14) = Label13
VSFlexGrid.TextMatrix(1, 15) = Label14
VSFlexGrid.TextMatrix(1, 16) = Label15
VSFlexGrid.TextMatrix(1, 17) = Label16
VSFlexGrid.TextMatrix(1, 18) = Label17
VSFlexGrid.TextMatrix(1, 19) = Label18

With VSFlexGrid
adoPrimaryRS.AddNew Array("Markaz", "Daryafti", "Tolidi", "Tolidimomtaz", "GoazreshKhabari", "GozareshTahlili", "GozareshTahliliMomtaz", "GozareshPmomtaz", "AksKhabar", "Gtasviri", "Gtmomtaz", "GTBarjaste", "Dabiri", "FilmPosheshi", "Filmtolidi", "GMTasviri", "GMTmomtaz", "FKHBarjaste", "TTFarsi"), _
Array((.TextMatrix(1, 1)), .TextMatrix(1, 2), .TextMatrix(1, 3), .TextMatrix(1, 4), .TextMatrix(1, 5), .TextMatrix(1, 6), .TextMatrix(1, 6), .TextMatrix(1, 7), .TextMatrix(1, 8), .TextMatrix(1, 10), .TextMatrix(1, 11), .TextMatrix(1, 12), .TextMatrix(1, 13), .TextMatrix(1, 14), .TextMatrix(1, 15), .TextMatrix(1, 16), .TextMatrix(1, 17), .TextMatrix(1, 18), .TextMatrix(1, 19))
adoPrimaryRS.UpdateBatch
Adodc1.Recordset.MoveNext
End With

End If
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''''''

Else

Adodc1.RecordSource = "Select * from Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc1.Refresh

Adodc2.RecordSource = "Select sum(Daryafti) as SS From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(Tolidi) as SS2 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(Tolidimomtaz) as SS3 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(GoazreshKhabari) as SS4 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(GozareshTahlili) as SS5 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(GozareshTahliliMomtaz) as SS6 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(GozareshPmomtaz) as SS7 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(AksKhabar) as SS8 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(Gtasviri) as SS9 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(Gtmomtaz) as SS10 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(GTBarjaste) as SS11 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(Dabiri) as SS12 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(FilmPosheshi) as SS13 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(Filmtolidi) as SS14 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(GMTasviri) as SS15 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(GMTmomtaz) as SS16 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(FKHbarjaste) as SS17 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh
Adodc2.RecordSource = "Select sum(TTFarsi) as SS18 From Karkard Where Markaz='" & Me.DataGrid3.Columns(0) & "'"
Adodc2.Refresh

VSFlexGrid.TextMatrix(1, 1) = Me.DataGrid3.Columns(0)
VSFlexGrid.TextMatrix(1, 2) = Label1
VSFlexGrid.TextMatrix(1, 3) = Label2
VSFlexGrid.TextMatrix(1, 4) = Label3
VSFlexGrid.TextMatrix(1, 5) = Label4
VSFlexGrid.TextMatrix(1, 6) = Label5
VSFlexGrid.TextMatrix(1, 7) = Label6
VSFlexGrid.TextMatrix(1, 8) = Label7
VSFlexGrid.TextMatrix(1, 9) = Label8
VSFlexGrid.TextMatrix(1, 10) = Label9
VSFlexGrid.TextMatrix(1, 11) = Label10
VSFlexGrid.TextMatrix(1, 12) = Label11
VSFlexGrid.TextMatrix(1, 13) = Label12
VSFlexGrid.TextMatrix(1, 14) = Label13
VSFlexGrid.TextMatrix(1, 15) = Label14
VSFlexGrid.TextMatrix(1, 16) = Label15
VSFlexGrid.TextMatrix(1, 17) = Label16
VSFlexGrid.TextMatrix(1, 18) = Label17
VSFlexGrid.TextMatrix(1, 19) = Label18

With VSFlexGrid
adoPrimaryRS.AddNew Array("Markaz", "Daryafti", "Tolidi", "Tolidimomtaz", "GoazreshKhabari", "GozareshTahlili", "GozareshTahliliMomtaz", "GozareshPmomtaz", "AksKhabar", "Gtasviri", "Gtmomtaz", "GTBarjaste", "Dabiri", "FilmPosheshi", "Filmtolidi", "GMTasviri", "GMTmomtaz", "FKHBarjaste", "TTFarsi"), _
Array((.TextMatrix(1, 1)), .TextMatrix(1, 2), .TextMatrix(1, 3), .TextMatrix(1, 4), .TextMatrix(1, 5), .TextMatrix(1, 6), .TextMatrix(1, 6), .TextMatrix(1, 7), .TextMatrix(1, 8), .TextMatrix(1, 10), .TextMatrix(1, 11), .TextMatrix(1, 12), .TextMatrix(1, 13), .TextMatrix(1, 14), .TextMatrix(1, 15), .TextMatrix(1, 16), .TextMatrix(1, 17), .TextMatrix(1, 18), .TextMatrix(1, 19))
adoPrimaryRS.UpdateBatch
Adodc1.Recordset.MoveNext
End With

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''''''

End If

End Sub

vbhamed

سه شنبه 28 مهر 1394, 11:56 صبح

سلام

اصلا نیاز به همچین کارهایی نیست، باید از امکانات Sql استفاده می‌کردین
فرض کنید یک جدول دارید با 4 فیلد m برای نام مرکز و a,b,c برای اعداد
دستور Sql زیر نتیجه مورد نظر شما رو برمی‌گردونه

SELECT m, sum(a) AS SumA, sum(b) AS SumB, sum(c) AS SumC FROM Table1 GROUP BY m

amiralex

سه شنبه 28 مهر 1394, 12:09 عصر

با تشکر

میشه رو ضمیمه ایی که کردم برام تغییرش و انجام بدید؟