Tính dùm em cái tổng này với! (1 người xem)

[lammtv]

Nhờ các anh giúp dùm em bài này với!chi tiết trong file excel nhen!
thanks!

 

[MinhCong]

Bạn dùng tạm công thức này vậy

D1 =IF(ISERROR(MATCH(A1+1;$A$1:$A$40;0));SUM(D2:D40);IF(ISERROR(SUM(OFFSET($D2;;;MATCH(A1+1;$A$1:$A$40;0)-MATCH(A1;$A$1:$A$40;0)-1;1)));0;SUM(OFFSET($D2;;;MATCH(A1+1;$A$1:$A$40;0)-MATCH(A1;$A$1:$A$40;0)-1;1))))

 

[viehoai]

Nhờ các anh giúp dùm em bài này với!chi tiết trong file excel nhen!
thanks!

Bạn thử file này đúng ý bạn không nhé

 

[lammtv]

Bạn dùng tạm công thức này vậy

D1 =IF(ISERROR(MATCH(A1+1;$A$1:$A$40;0));SUM(D2:D40);IF(ISERROR(SUM(OFFSET($D2;;;MATCH(A1+1;$A$1:$A$40;0)-MATCH(A1;$A$1:$A$40;0)-1;1)));0;SUM(OFFSET($D2;;;MATCH(A1+1;$A$1:$A$40;0)-MATCH(A1;$A$1:$A$40;0)-1;1))))

cho em hỏi dấu trừ trong hàm MATCH là sao vậy?em mới nghiên cứu excel nhờ anh giải thích dùm với!thanks anh nhìu!

 

[huuthang_bd]

Nhờ các anh giúp dùm em bài này với!chi tiết trong file excel nhen!
thanks!

Dùng công thức này cho ô D1

=SUBTOTAL(9,D2:$D$40)*2-SUM(D2:$D$40)

Copy cho những ô cần tính tổng còn lại.
Chú ý:
- Tất cả các ô cần tính tổng đều phải dùng công thức này thì kết quả mới đúng.
- Có thể dùng AutoFilter để lọc ra những ô cần tính tổng và chọn Visible Cell. Đừng copy, paste từng cell cho mất công nhé.

 

[ndu96081631]

Góp vui bằng cách dùng công thức đơn giản kết hợp với 1 tí kỹ xảo
- Giả định rằng các cell D1, D15 và D33 đang rổng (chưa có công thức)
- Chọn toàn bộ bảng tính, vào menu Data\Filter\AutoFilter
- Filter cột A với điều kiện NonBlanks
- Filter cột D với điều kiện Blanks
- Gõ vào cell D1 công thức: SUM(OFFSET(D2,,,B1,)) rồi kéo fill xuống
- Hủy AutoFilter và kiểm tra kết quả
----------------------
Bài này làm vậy là vì để ý thấy cột B chứa các con số chỉ số dòng
---------------------

Dùng công thức này cho ô D1

=SUBTOTAL(9,D2:$D$40)*2-SUM(D2:$D$40)

Copy cho những ô cần tính tổng còn lại.
Chú ý:
- Tất cả các ô cần tính tổng đều phải dùng công thức này thì kết quả mới đúng.
- Có thể dùng AutoFilter để lọc ra những ô cần tính tổng và chọn Visible Cell. Đừng copy, paste từng cell cho mất công nhé.

Để ý rằng cell D32 đã có dữ liệu (do người ta gõ sẳn vào rồi) nên dùng công thức trên sẽ cho kết quả sai tai cell D15 ---> Nếu paste công thức luôn vào cell D32 thì các cell khác cho kết quả đúng, có điều chẳng lẽ mất luôn cell D32 (khi ấy D32 = 0)

 

[Lê Duy Thương]

D1=subtotal(109,d2:d15 )
d15=subtotal(109,d16:d32 )
d33=subtotal(109,d34:d41 )
không cần phải viết nhiều công thức

 

[ndu96081631]

D1=subtotal(109,d2:d15 )
d15=subtotal(109,d16:d32 )
d33=subtotal(109,d34:d41 )
không cần phải viết nhiều công thức

Nếu có 100 cell thì phải viết 100 công thức à?
Vấn đề ở đây là tìm 1 công thức tổng quát, đúng cho mọi cell

 

[lammtv]

nếu cột B ghi sai thì tính sai luôn hả thầy?
Có cách nào để cột B luôn là số hàng trắng ko thầy?kể cả khi insert thêm hàng!
Thanks thầy!

 

[lammtv]

D1=subtotal(109,d2:d15 )
d15=subtotal(109,d16:d32 )
d33=subtotal(109,d34:d41 )
không cần phải viết nhiều công thức

Nếu vậy thì khác gì hàm SUM vậy anh?

 

[ndu96081631]

nếu cột B ghi sai thì tính sai luôn hả thầy?

Đương nhiên là vậy rồi
Đúng ra bài này giải quyết rất dể dàng, nhưng rắc rối nằm ở chổ cell D32 đang có sẳn dữ liệu
Đành vậy... nếu gõ sai tại cột B mà vẫn muốn cột D cho kết quả đúng, chắc dùng VBA mới gọi là đơn giản

 

[lammtv]

Góp vui bằng cách dùng công thức đơn giản kết hợp với 1 tí kỹ xảo
- Giả định rằng các cell D1, D15 và D33 đang rổng (chưa có công thức)
- Chọn toàn bộ bảng tính, vào menu Data\Filter\AutoFilter
- Filter cột A với điều kiện NonBlanks
- Filter cột D với điều kiện Blanks
- Gõ vào cell D1 công thức: SUM(OFFSET(D2,,,B1,)) rồi kéo fill xuống
- Hủy AutoFilter và kiểm tra kết quả
----------------------
Bài này làm vậy là vì để ý thấy cột B chứa các con số chỉ số dòng
---------------------

Để ý rằng cell D32 đã có dữ liệu (do người ta gõ sẳn vào rồi) nên dùng công thức trên sẽ cho kết quả sai tai cell D15 ---> Nếu paste công thức luôn vào cell D32 thì các cell khác cho kết quả đúng, có điều chẳng lẽ mất luôn cell D32 (khi ấy D32 = 0)

Em làm vậy thì ô D32=D33 sao thầy?

 

[huuthang_bd]

Góp vui bằng cách dùng công thức đơn giản kết hợp với 1 tí kỹ xảo
- Giả định rằng các cell D1, D15 và D33 đang rổng (chưa có công thức)
- Chọn toàn bộ bảng tính, vào menu Data\Filter\AutoFilter
- Filter cột A với điều kiện NonBlanks
- Filter cột D với điều kiện Blanks
- Gõ vào cell D1 công thức: SUM(OFFSET(D2,,,B1,)) rồi kéo fill xuống
- Hủy AutoFilter và kiểm tra kết quả
----------------------
Bài này làm vậy là vì để ý thấy cột B chứa các con số chỉ số dòng
---------------------

Để ý rằng cell D32 đã có dữ liệu (do người ta gõ sẳn vào rồi) nên dùng công thức trên sẽ cho kết quả sai tai cell D15 ---> Nếu paste công thức luôn vào cell D32 thì các cell khác cho kết quả đúng, có điều chẳng lẽ mất luôn cell D32 (khi ấy D32 = 0)

Yêu cầu là tính tổng của vùng dữ liệu bên dưới. Tôi thiết lập công thức là áp dụng chung cho trường hợp này. Còn dữ liệu tác giả đưa ra tôi nghĩ là do giả lập sai. Nếu không phải do giả lập dữ liệu sai thì yêu cầu bài toán này không còn như lúc đầu nữa, và đương nhiên phải tìm một công thức khác hoặc một cách khác.

 

[ndu96081631]

Em làm vậy thì ô D32=D33 sao anh?

Sao mà bằng được chứ
Có làm gì tại cell D32 đâu! (cell ấy đang có dữ liệu cơ mà)
Làm lại từ đâu:
- Xóa cell D1, D15 và D33 rồi làm lại chính xác như tôi đã hướng dẩn xem
Chú ý rằng cell D32 giữ nguyên nha!

 

[lammtv]

Dạ!em hiểu rồi!nhờ thầy giúp em cái cột B với!

nếu cột B ghi sai thì tính sai luôn hả thầy?
Có cách nào để cột B luôn là số hàng trắng ko thầy?kể cả khi insert thêm hàng!
Thanks thầy!​

 

[lammtv]

Yêu cầu là tính tổng của vùng dữ liệu bên dưới. Tôi thiết lập công thức là áp dụng chung cho trường hợp này. Còn dữ liệu tác giả đưa ra tôi nghĩ là do giả lập sai. Nếu không phải do giả lập dữ liệu sai thì yêu cầu bài toán này không còn như lúc đầu nữa, và đương nhiên phải tìm một công thức khác hoặc một cách khác.

dạ!trong những ô xanh đó có những ô ko có ô trắng phía dưới ạ!cảm ơn bạn nhiều!

 

[ndu96081631]

Dạ!em hiểu rồi!nhờ thầy giúp em cái cột B với!

nếu cột B ghi sai thì tính sai luôn hả thầy?
Có cách nào để cột B luôn là số hàng trắng ko thầy?kể cả khi insert thêm hàng!
Thanks thầy!​

Vậy tôi giúp bạn đếm số cell trắng tại cột B nha!
Có số thứ tự tại B rồi, việc còn lại của bạn là AutoFilter cột D với điều kiện = Blanks rồi gõ vào cell D1 công thức: SUM(OFFSET(D2,,,B1,)) và kéo fill xuống

 

[lammtv]

Thầy cho em hỏi!đềm cột B này chỉ đúng khi mình kéo hết cột B xuống dưới cùng luôn phải ko thầy?Lúc đó SUM trong hàm IF mới đúng phải ko thầy?
có nghĩa là công thức này mình làm từ dưới lên phải ko thầy?

 

[ndu96081631]

Thầy cho em hỏi!đềm cột B này chỉ đúng khi mình kéo hết cột B xuống dưới cùng luôn phải ko thầy?Lúc đó SUM trong hàm IF mới đúng phải ko thầy?

Đương nhiên là vậy rồi ---> Cái trên liên quan với cái dưới nên phải fill đầy đủ mới chính xác
Bạn thí nghiệm thì biết chứ gì

 

[lammtv]

dạ cảm ơn thầy!do trước giờ em cứ làm theo chiều thuận từ trên xuống dưới, chứ chưa bao giờ nghỉ đến cách ngược lại như vậy!xin chân thành cảm ơn thầy!em đã học được rất nhiều ở diễn đàn này!

 

[lammtv]

Đương nhiên là vậy rồi ---> Cái trên liên quan với cái dưới nên phải fill đầy đủ mới chính xác
Bạn thí nghiệm thì biết chứ gì

Thầy ơi!nhờ thầy xem lại dùm em bài tính STT với!sao em làm bị phát sinh ko đúng nhiều cái:
Thứ nhất: Em insert thêm 1 hàng giữa hàng 32 và 33 vào thì nó bị sai!
Thứ hai: cho em hỏi: tại ô B32 em bấm F9 để xem giá trị thì COUNTBLANK($A32:$A$41)=7 và SUM($B34:$B$42)=7 vậy sao 2 cái đó trừ nhau lại = 1 vậy thầy?đáng lý phải bằng 0 chứ thầy?

Với lại tại ô B15 công thức SUM($B16:$B$41) phải bằng 7+1=8 chứ thầy, nhưng mà sao em bấm F9 thì nó lại là 7!
Nhờ thầy giải thích dùm em với!
Cảm ơn thầy!

 

[lammtv]

nhờ thầy giúp dùm em với!

 

[ndu96081631]

Thầy ơi!nhờ thầy xem lại dùm em bài tính STT với!sao em làm bị phát sinh ko đúng nhiều cái:
Thứ nhất: Em insert thêm 1 hàng giữa hàng 32 và 33 vào thì nó bị sai!
Thứ hai: cho em hỏi: tại ô B32 em bấm F9 để xem giá trị thì COUNTBLANK($A32:$A$41)=7 và SUM($B34:$B$42)=7 vậy sao 2 cái đó trừ nhau lại = 1 vậy thầy?đáng lý phải bằng 0 chứ thầy?

Với lại tại ô B15 công thức SUM($B16:$B$41) phải bằng 7+1=8 chứ thầy, nhưng mà sao em bấm F9 thì nó lại là 7!
Nhờ thầy giải thích dùm em với!
Cảm ơn thầy!

Trả lời câu hỏi 1: Sai là sai như thế nào?
Trả lời câu hỏi 2: Khi bạn quét chọn công thức và F9 thì đấy mới là giá trị thật ---> Kết quả trên bảng tính nếu khác là do tôi dùng Custom Format mà ra ----> Bạn đặt con trỏ tại cell B32 rồi bấm vào Format\Cells sẽ biết (nó thật sự = 0 nhưng tôi cố tình cho nó hiển thị = 1)

 

[lammtv]

dạ em có tô màu vàng đánh dấu trong file ạ!
nhờ thầy xem dùm!
với lại nhờ thầy giải thích thêm chi tiết chỗ #;;"1" trong FORMAT CELL dc ko thầy?mình làm vậy để làm gì vậy thầy?
cảm ơn thầy!

 

[ndu96081631]

dạ em có tô màu vàng đánh dấu trong file ạ!
nhờ thầy xem dùm!

Insert row xong nó sai tham chiếu thôi, bạn fill công thức ở trên xuống dưới là được rồi (fill từ dòng 31 xuống)

với lại nhờ thầy giải thích thêm chi tiết chỗ #;;"1" trong FORMAT CELL dc ko thầy?mình làm vậy để làm gì vậy thầy?
cảm ơn thầy!

Đại khái format như thế thì số 0 sẽ biến thành 1 ---> Vì tôi không muốn nó hiện số 0, có vẽ hơi kỳ ----> Còn nếu bạn muốn hiện số 0 nguyên hình thì xóa Format đi (bấm Ctrl + Shift + ~)
Xem Ebook về Custom Format tại đây:
http://www.giaiphapexcel.com/forum/showthread.php?p=23443

 

[lammtv]

cho em hỏi bây giờ em muốn đánh số thứ tự những ô trắng ở cột B thì làm sao hả thầy?khi đó B1=rỗng mất rồi!nhờ thầy chỉ dùm!
cảm ơn thầy!em đã thử dùng hàm MAX nhưng khi INSERT thêm hàng vào thì cái chỗ B1 nó ko nhảy!

 

[ndu96081631]

cho em hỏi bây giờ em muốn đánh số thứ tự những ô trắng ở cột B thì làm sao hả thầy?khi đó B1=rỗng mất rồi!nhờ thầy chỉ dùm!
cảm ơn thầy!em đã thử dùng hàm MAX nhưng khi INSERT thêm hàng vào thì cái chỗ B1 nó ko nhảy!

Ở cột B đang có công thức, những cell nào không thỏa mản điều kiện sẽ trả về giá trị rổng ---> Giờ bạn lại muốn đánh số thứ tự vào các cell rổng này thì... thật sự khó khăn... Tôi vẫn chưa nghĩ ra được biện pháp nào...
Điều mà tôi có thể khuyên bạn là: NÊN XEM LẠI CÁCH BỐ TRÍ DỮ LIỆU ---> Có nên bố trí theo kiểu khác nhằm đơn giản hóa vấn đề hay không?

 

[lammtv]

dạ em cũng muốn lắm, nhưng người ta yêu cầu như vậy ạ!chứ em cũng muốn đơn giản lắm ạ!
Mong hồi âm của thầy!

 

[ndu96081631]

dạ em cũng muốn lắm, nhưng người ta yêu cầu như vậy ạ!chứ em cũng muốn đơn giản lắm ạ!
Mong hồi âm của thầy!

Tôi nghĩ chỉ còn nước dùng VBA ---> Xây dùng 1 hàm tự tạo để giải quyết vấn đề này!
Bạn nghĩ sao? Nếu đồng ý thì chúng ta tiếp tục!

 

[lammtv]

Dạ!nếu vậy nhờ thầy giúp dùm em!và làm trên file mới em kèm theo nhen thầy!Với lại nhờ thầy hướng dẫn dùm là em muốn sau này mở các file Excel khác lên cũng sử dụng được đoạn VBA này luôn!vì em có rất nhiều FILE bảng tính tương tự như vậy!
với lại nhờ thầy sửa đoạn VBA trong file TXT cho phù hợp với file EXCEL mới này luôn được ko thầy?
Thanks thầy!

 

[ndu96081631]

Dạ!nếu vậy nhờ thầy giúp dùm em!và làm trên file mới em kèm theo nhen thầy!Với lại nhờ thầy hướng dẫn dùm là em muốn sau này mở các file Excel khác lên cũng sử dụng được đoạn VBA này luôn!vì em có rất nhiều FILE bảng tính tương tự như vậy!

Code đây:

Function STT(SrcRng As Range, CelLookup As Range) As Long
  Dim Clls As Range, FAdd As String, i As Long, Total As Long
  Application.Volatile
  With CreateObject("Scripting.Dictionary")
    For Each Clls In SrcRng.Resize(, 1)
      If Clls <> "" Then
        i = 0: Total = 0
        .Add Clls.Address, Total
        FAdd = Clls.Address
      Else
        i = i + 1: Total = Total + 1
        .Add Clls.Address, i
        .Item(FAdd) = Total
      End If
    Next Clls
    STT = .Item(CelLookup.Address)
  End With
End Function

Bạn thực hiện như sau:
- Tải file về máy tính
- Mở file TaoSTT.xls lên, vào menu File\Save As ---> Khung Save as type bạn chọn mục "Microsoft Office Excel Add-In(*.xla)"
- Đóng file TaoSTT.xls lại
- Mở file của bạn lên rồi vào menu Tools\Add-Ins và check vào mục Taostt ---> Lưu ý: nếu không thấy mục này, hãy bấm nút Browse để tìm
Vậy là Hàm STT đã được load, kể từ bây giờ, mọi file trên máy tính của bạn đều có thể sử dụng hàm này
Với file VD.xls của bạn, tại cell G12 bạn gõ vào công thức:
=STT($F$12:$F$100,$F12)
Kéo fill xuống đến dòng thứ 100 và kiểm tra độ chính xác
Ngoài ra, để hổ trợ tốt cho việc chèn dòng, xóa dòng, bạn nên dùng thêm công cụ Create List nhé (quét chọn vùng dữ liệu, click phải và chọn Create List...) ---> Với công cụ này, mổi khi bạn chèn dòng hoặc xóa dòng thì tham chiếu trong công thức tự cập nhật chính xác, mặc khác công thức ở trên cũng tự động được fill xuống dòng trống mà bạn vừa chèn
Hãy xem tôi làm thí nghiệm trong file STT_2.xls nhé

 

[lammtv]

những chỗ mà chỉ có 1 hàng xanh,ko có hàng trắng ở phía dưới là số 1 chứ ko phải số 0 thầy ơi!

 

[ndu96081631]

những chỗ mà chỉ có 1 hàng xanh,ko có hàng trắng ở phía dưới là số 1 chứ ko phải số 0 thầy ơi!

Custom Format để làm điều này nhé ---> Như tôi đã từng làm (bạn quên rồi sao)

 

[lammtv]

ah quên!sorry thầy!nhờ thầy sửa đoạn VBA trong bài #3 cho phù hợp với file EXCEL mới dùm em với!và có cách nào khi mở bất kỳ file excel nào ra cũng có sẵn nút BẤM "CHAY CODE" dc ko?và khi không cần thì mình làm sao bỏ nút bấm đó!thanks thầy!

 

[ndu96081631]

ah quên!sorry thầy!nhờ thầy sửa đoạn VBA trong bài #3 cho phù hợp với file EXCEL mới dùm em với!và có cách nào khi mở bất kỳ file excel nào ra cũng có sẵn nút BẤM "CHAY CODE" dc ko?và khi không cần thì mình làm sao bỏ nút bấm đó!thanks thầy!

Nên dùng hàm sẽ hay hơn là SUB, vì nó linh hoạt hơn (ít nhất là trường hợp của bạn)
Bạn thử nghĩ xem, bạn có 20 file với cấu trúc dữ liệu khác nhau, vậy nếu chạy sub thì code nó làm sao biết sẽ hoạt động tại vùng dữ liệu nào? Hơn nữa, dùng hàm (kết hợp với Create List) thì khi bạn thay đổi dữ liệu hoặc chèn dòng, xóa dòng, hàm nó cập nhật ngay!