Lọc bỏ dữ liệu không trùng ở 2 vế Nợ Có (1 người xem)

[ptthanh87]

Mình đang vướng vấn đề này, các bạn hỗ trợ với.
Mình làm kế toán khi lệch sổ phải chạy báo cáo ra xem hạch toán Nợ và Có bị lệch BT nào còn điều chỉnh nhưng số liệu rất nhiều không làm thủ công được, vậy có công thức nào phát hiện ra BT bị thừa or thiếu chân không ( Bên Nợ có nhưng bên Có không có hoặc ngược lại)
Có công thức nào or ứng dụng gì khi chạy có thể ra luôn các món nào không cân đối Nợ or Có. ( Đặc biệt số tiền trùng nhau. VD có ngày có 3 BT 10 tr bên Nợ nhưng bên Có chỉ có 2 BT vậy chạy sao ra phát hiện bị lệch như vậy)
Mình gửi file đây chỉ là VD 1 ngày, khi chạy báo cáo có rất nhiều món trùng tiền nhau của các ngày, đến hàng chục nghìn bút toán.Vậy làm sao chạy ra. Tks.

 

[Phan Thế Hiệp]

Mình đang vướng vấn đề này, các bạn hỗ trợ với.
Mình làm kế toán khi lệch sổ phải chạy báo cáo ra xem hạch toán Nợ và Có bị lệch BT nào còn điều chỉnh nhưng số liệu rất nhiều không làm thủ công được, vậy có công thức nào phát hiện ra BT bị thừa or thiếu chân không ( Bên Nợ có nhưng bên Có không có hoặc ngược lại)
Có công thức nào or ứng dụng gì khi chạy có thể ra luôn các món nào không cân đối Nợ or Có. ( Đặc biệt số tiền trùng nhau. VD có ngày có 3 BT 10 tr bên Nợ nhưng bên Có chỉ có 2 BT vậy chạy sao ra phát hiện bị lệch như vậy)
Mình gửi file đây chỉ là VD 1 ngày, khi chạy báo cáo có rất nhiều món trùng tiền nhau của các ngày, đến hàng chục nghìn bút toán.Vậy làm sao chạy ra. Tks.

Xem thử giải pháp trong file kèm.
Thân!

 

[ptthanh87]

Cám ơn bạn nhiều

 

[queluatb]

Xem thử giải pháp trong file kèm.
Thân!

bạn giải thích công thức giúp mình với cám ơn bạn nhiều

 

[Phan Thế Hiệp]

bạn giải thích công thức giúp mình với cám ơn bạn nhiều

• Công thức: ISODD(SUMPRODUCT(N(ABS($B$2:$B$151+$C$2:$C$151)=ABS(B2+C2)))) nhằm đếm xem có bao nhiêu giá trị giống nhau (chỉ xem giá trị tuyệt đối, không xem dấu của nó) trên cả hai cột Nợ và Có, nếu nó lẻ: 1,3,5,7... thì chắc chắn đã thiếu 1 bút toán kép nên báo "có vấn đề", ngược lại nếu là chẳn thì không báo. Tuy nhiên, giải pháp này sẽ bị sai khi 2 dòng nào đó cùng 1 cột có cùng giá trị, mà bên cột còn lại không phát sinh bất kỳ giá trị nào giống nó. (xem ví dụ: sheet "Old", ô B4 và B5, file đính kèm)
• Tôi thay bằng công thức khác:

  =SUMPRODUCT(SUMIF($B$2:$B$151,B2:C2*{1;-1})-SUMIF($C$2:$C$151,B2:C2*{1;-1}))

  Công thức này lấy cột Nợ trừ cột Có (sau khi đã tự triệt tiêu bút toán điều chỉnh phát sinh khác dấu trên cùng một cột Nợ hay Có), nếu bút toán nào có chênh lệch <>0 thì sẽ báo "Có vấn đề" (Xem sheet "New").

Xem file đính kèm.
Thân!

 

[Nguyễn Hồng Quang]

Trong tình huống này thì Công thức của bạn Phan Thế Hiệp là đẹp rồi
Tuy nhiên mình cũng xin góp ý cách khác khá nhanh gọn và dễ nhớ. Bằng cách thêm 1 vài cột phụ rồi sử dụng chức năng Sort của Excel để đưa về cách trình bày thành kiểu Các cặp Nợ Có đi với nhau. Từ đó sẽ nhìn ra ngay bút toán nào "Có vấn đề"

 

[Nguyễn Hồng Quang]

• Tôi thay bằng công thức khác:

  =SUMPRODUCT(SUMIF($B$2:$B$151,B2:C2*{1;-1})-SUMIF($C$2:$C$151,B2:C2*{1;-1}))

  .

Bạn Phan Thế Hiệp có thể nói rõ thêm chút về cách thức hoạt động của đoạn công thức này được không ạ
Mình thực sự chưa hiểu lắm; vì mình thử tách đoạn công thức SUMIF($B$2:$B$151,B2:C2*{1;-1})-SUMIF($C$2:$C$151,B2:C2*{1;-1}) ra chạy cho từng dòng thì đều thấy các dòng ra là 0 cả. Rất mong nhận được giải đáp của bạn

 

[Phan Thế Hiệp]

Bạn Phan Thế Hiệp có thể nói rõ thêm chút về cách thức hoạt động của đoạn công thức này được không ạ
Mình thực sự chưa hiểu lắm; vì mình thử tách đoạn công thức SUMIF($B$2:$B$151,B2:C2*{1;-1})-SUMIF($C$2:$C$151,B2:C2*{1;-1}) ra chạy cho từng dòng thì đều thấy các dòng ra là 0 cả. Rất mong nhận được giải đáp của bạn

Tôi rất thích thái độ học nghiêm túc của bạn, đã học thì học cho đến nơi đến chốn.

Như bạn thấy công thức trên là công thức mảng cho nên hoặc phải nhấn Ctrl+Shift+Enter hoặc phải nằm trong đối số của một hàm nào đó chấp nhận Mảng.
Do yêu cầu phải cộng lại nên tôi dùng Sumproduct() bao bên ngoài. Công thức trên còn được hiểu như sau:
= SUMPRODUCT(SUMIF($B$3:$B$152,B3:C3*{1;-1})) <> SUMPRODUCT(SUMIF($C$3:$C$152,B3:C3*{1;-1}))
Tức tìm tổng phát sinh bên Nợ theo giá trị từng dòng (khi kéo xuống) so với tổng giá trị bên Có cũng theo giá trị từng dòng đó.
Khi đang tại dòng có Nợ (cột B) <>0 thì B3*{1;-1} phát huy tác dụng lấy cả hai dấu dương và âm, C3*{1;-1} lúc đó bằng 0.

Ví dụ 1: tại dòng F3, có B3= 873.504.000, có C3="", cho nên B3:C3*{1;-1} sẽ cho 1 mảng 2 chiều: {873.504.000, 0; -873.504.000, 0}

• SUMIF($B$3:$B$152,B3:C3*{1;-1}) sẽ tìm bên cột Nợ (B3:B152) thỏa các giá trị trong mảng 2 chiều như trên, tức tìm được đúng hai giá trị: 873.504.000 và -873.504.000
• Cuối cùng, Sumproduct({873.504.000, 0; -873.504.000, 0}) cộng các kết quả tìm được =0, tức nếu có bút toán điều chỉnh thì nó tự triệt tiêu.
• Tương tự suy luận cho công thức: SUMPRODUCT(SUMIF($C$3:$C$152,B3:C3*{1;-1})), kết quả bằng 0, do vậy 2 vế bất đẳng thức bằng nhau, , kết quả cuối cùng là FALSE, tức không có phát sinh chênh lệch.

Ví dụ 2: tại dòng F5, có B5= 5, có C3="", cho nên B5:C5*{1;-1} sẽ cho 1 mảng 2 chiều: {5, 0; -5, 0}

• SUMPRODUCT(SUMIF($B$3:$B$152,B5:C5*{1;-1})) sẽ tìm bên cột Nợ (B3:B152) thỏa các giá trị trong mảng 2 chiều như trên, ra kết quả : {10, 0; 0; 0}
  
• SUMPRODUCT(SUMIF($C$3:$C$152,B5:C5*{1;-1})), kết quả bằng 0, do vậy 2 vế bất đẳng thức không bằng nhau, kết quả cuối cùng là TRUE, có phát sinh chênh lệch.

Trong file đính kèm tôi có chỉnh lại công thức bạn thắc mắc tại cột F, và các dẫn giải ý nghĩa và cách làm gọn từ các công thức phụ (từ cột G đến cột M), hy vọng sau khi tìm hiểu các công thức trong file sẽ nắm rõ giải thuật hơn.
Thân!

 

[Nguyễn Hồng Quang]

Tôi rất thích thái độ học nghiêm túc của bạn, đã học thì học cho đến nơi đến chốn.

Như bạn thấy công thức trên là công thức mảng cho nên hoặc phải nhấn Ctrl+Shift+Enter hoặc phải nằm trong đối số của một hàm nào đó chấp nhận Mảng.
Do yêu cầu phải cộng lại nên tôi dùng Sumproduct() bao bên ngoài. Công thức trên còn được hiểu như sau:
= SUMPRODUCT(SUMIF($B$3:$B$152,B3:C3*{1;-1})) <> SUMPRODUCT(SUMIF($C$3:$C$152,B3:C3*{1;-1}))
Tức tìm tổng phát sinh bên Nợ theo giá trị từng dòng (khi kéo xuống) so với tổng giá trị bên Có cũng theo giá trị từng dòng đó.
Khi đang tại dòng có Nợ (cột B) <>0 thì B3*{1;-1} phát huy tác dụng lấy cả hai dấu dương và âm, C3*{1;-1} lúc đó bằng 0.
.............................

Rất cảm ơn bạn. Mình rất vui và trân trọng những giải thích và cách hướng dẫn chu đáo; tận tình của Phan Thế Hiệp
Lúc bạn nói đến đoạn

B3:C3*{1;-1} sẽ cho 1 mảng 2 chiều: {873.504.000, 0; -873.504.000, 0}

mình đã hiểu ra vấn đề; nhưng do kiến thức về mảng trong excel của mình còn ít nên cũng mới chỉ hiểu được 80% cách thức hoạt động của công thức
Tuy nhiên ; hàng ngày Quang sẽ dành nhiều thời gian để đọc kỹ hơn các diễn giải của bạn.
Chúc bạn năm mới nhiều thành công mới