Nhờ diễn đàn tư vấn giúp đỡ về hàm trong Excel (2 người xem)

[chttuan]

Hiện tại mình phải làm dự toán, không biết các lệnh của EXCEL để làm cho nhanh, làm thủ công thì không biết bao giờ mới xong. Nhờ diễn đàn tư vấn giúp một chút về file Excel.
1. Ý tưởng: tại sheet "Đơn giá chi tiết" mình muốn triển khai giá: Vật liệu, Nhân công, Máy thi công (Tại cột O6-P6-Q6-Bôi vàng) sao cho khớp đúng với dòng 6 (Tương ứng với hạng mục công việc số 1).
2. Bình thường mình đã làm được rồi bằng cách dùng hàm Sumif, rồi kiểm tra vùng dữ liệu ($D6:$D20;O$5;$I6:$L20).
3. Với cách làm này thì mình phải làm thủ công để kéo chuột kiểm tra dữ liệu đến đúng dòng thứ 20 (Tương ứng với công việc số 1). Nếu kéo đến dòng 21 (Bắt đầu công việc số 2) thì lập tức số liệu giá VL, NC, M sẽ sai.
4. Công việc số 1 không có phần Vật liệu----->số dòng sẽ khác so với công việc khác (Đầy đủ thành phần VL, NC, MTC), vì vậy vùng dữ liệu mình kiểm tra đối với mỗi công việc cũng khác nhau.....
5. Kết luận: Mình xin hỏi diễn đàn có cách nào chỉ cần áp dụng một công thức duy nhất để mình tính ra được giá VL, NC, MTC (đúng như vùng bôi vàng), trong đúng một khoảng dữ liệu của công việc (số 1,2,3...) mà không bị lấn sang công việc tiếp theo được không ?
(Xem file đính kèm)

 

[Ba Tê]

Hiện tại mình phải làm dự toán, không biết các lệnh của EXCEL để làm cho nhanh, làm thủ công thì không biết bao giờ mới xong. Nhờ diễn đàn tư vấn giúp một chút về file Excel.
1. Ý tưởng: tại sheet "Đơn giá chi tiết" mình muốn triển khai giá: Vật liệu, Nhân công, Máy thi công (Tại cột O6-P6-Q6-Bôi vàng) sao cho khớp đúng với dòng 6 (Tương ứng với hạng mục công việc số 1).
2. Bình thường mình đã làm được rồi bằng cách dùng hàm Sumif, rồi kiểm tra vùng dữ liệu ($D6:$D20;O$5;$I6:$L20).
3. Với cách làm này thì mình phải làm thủ công để kéo chuột kiểm tra dữ liệu đến đúng dòng thứ 20 (Tương ứng với công việc số 1). Nếu kéo đến dòng 21 (Bắt đầu công việc số 2) thì lập tức số liệu giá VL, NC, M sẽ sai.
4. Công việc số 1 không có phần Vật liệu----->số dòng sẽ khác so với công việc khác (Đầy đủ thành phần VL, NC, MTC), vì vậy vùng dữ liệu mình kiểm tra đối với mỗi công việc cũng khác nhau.....
5. Kết luận: Mình xin hỏi diễn đàn có cách nào chỉ cần áp dụng một công thức duy nhất để mình tính ra được giá VL, NC, MTC (đúng như vùng bôi vàng), trong đúng một khoảng dữ liệu của công việc (số 1,2,3...) mà không bị lấn sang công việc tiếp theo được không ?
(Xem file đính kèm)

Một công thức đây, cả ngàn dòng thì hơi bị "rùa" đấy, thử xem.

 

[Ếch Xanh]

Một công thức đây, cả ngàn dòng thì hơi bị "rùa" đấy, thử xem.

Anh Ba Tê giờ tiến bộ thật đó nha, viết có phương pháp hẳn ra. Em "phá" một tí cho cái code của Anh ngắn ngắn tí nhé!

Private Sub CommandButton1_Click()
  Application.ScreenUpdating = False
  With [S6:U900]
    .FormulaR1C1 = "=IF(OR(RC1="""",ISNA(MATCH(R5C,GPE1,0))),"""",INDEX(GPE2,MATCH(R5C,GPE1,0)))"
    .Copy
  End With
  [S6].PasteSpecial 3
  [S6].Select
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

Vậy nó mới không chớp chớp nhiều hehehehehe.

Lưu ý với công thức, nhất là Sumproduct, nó sẽ chạy rất chậm code của mình, thấy vậy ta vội thêm cái này:

Application.Calculation = xlCalculationManual
.........................
.........................
Application.Calculation = xlCalculationAutomatic

Code sẽ nhanh hơn, nhưng hậu quả của PasteSpecial (Value) là sai kết quả, vì trong suốt quá trình chạy code nó không tính toán gì cả!

 

[Ba Tê]

Anh Ba Tê giờ tiến bộ thật đó nha, viết có phương pháp hẳn ra. Em "phá" một tí cho cái code của Anh ngắn ngắn tí nhé!
.............
Vậy nó mới không chớp chớp nhiều hehehehehe.

Lưu ý với công thức, nhất là Sumproduct, nó sẽ chạy rất chậm code của mình, thấy vậy ta vội thêm cái này:

Application.Calculation = xlCalculationManual
.........................
.........................
Application.Calculation = xlCalculationAutomatic

Code sẽ nhanh hơn, nhưng hậu quả của PasteSpecial (Value) là sai kết quả, vì trong suốt quá trình chạy code nó không tính toán gì cả!

Cảm ơn, cảm ơn. Xin đừng khen coi chừng "té hen". Nó là sản phẩm của Record Macro đấy, tôi viết code như vậy chưa nổi đâu.
Dù sao cũng mừng vì được khen, Hihi...

 

[chttuan]

Các ơn các cao thủ đã chỉ giúp, để em thử xem sao

 

[chttuan]

Mình dùng được rồi, cám ơn bạn BaTe nhé. Với mình chẳng biết gì về lập trình thì chỉ cần dùng công thức =IF(OR($A6="";ISNA(MATCH(O$5;GPE1;0)));"";INDEX(GPE2;MATCH(O$5;GPE1;0))) đúng không ?
Và nếu mình muốn dùng công thức trên cho một dự toán khác thì phải làm thế nào. Mình không hiểu lệnh GPE1, GPE2 là ở đâu ra

 

[Ba Tê]

Mình dùng được rồi, cám ơn bạn BaTe nhé. Với mình chẳng biết gì về lập trình thì chỉ cần dùng công thức =IF(OR($A6="";ISNA(MATCH(O$5;GPE1;0)));"";INDEX(GPE2;MATCH(O$5;GPE1;0))) đúng không ?
Và nếu mình muốn dùng công thức trên cho một dự toán khác thì phải làm thế nào. Mình không hiểu lệnh GPE1, GPE2 là ở đâu ra

1. Chọn ô O6, vào menu Insert - Name - Define... sẽ thấy công thức GPE, GPE1, GPE2 được gán vào các Name.
2. Nếu không "Túm gọn" nó vào các Name thì công thức trong ô O6 từ:

=IF(OR($A6="";ISNA(MATCH(O$5;GPE1;0)));"";INDEX(GPE2;MATCH(O$5;GPE1;0)))

Sẽ là thế này:

=IF(OR($A6="";ISNA(MATCH(O$5;OFFSET(OFFSET('Don gia chi tiet'!$A6;;;MATCH('Don gia chi tiet'!$A6+1;'Don gia chi tiet'!$A6:$A$1000;0)-1;;3);0)));"";INDEX(OFFSET(OFFSET('Don gia chi tiet'!$A6;;;MATCH('Don gia chi tiet'!$A6+1;'Don gia chi tiet'!$A6:$A$1000;0)-1;;8);MATCH(O$5;OFFSET(OFFSET('Don gia chi tiet'!$A6;;;MATCH('Don gia chi tiet'!$A6+1;'Don gia chi tiet'!$A6:$A$1000;0)-1;;3);0)))

Nhìn vào có mà "điếc".
Tôi cũng mới biết sự "lợi hại" của Name qua GPE thôi, Bạn cố gắng tìm hiểu thêm nhé.

 

[chttuan]

Mình vào ô O6, vào menu Insert - Name - Define... gán vào các Name:
GPE=OFFSET('Don gia chi tiet'!$A6;;;MATCH('Don gia chi tiet'!$A6+1;'Don gia chi tiet'!$A6:$A$1000;0)-1
GPE1=OFFSET(GPE;;3)
GPE2=OFFSET(GPE;;8)
Với những sheet có nhiều hạng mục hơn thì mình chỉ việc sửa 'Don gia chi tiet'!$A6:$A$1000 thành 'Don gia chi tiet'!$A6:$A$3000 đúng không ?
Sau khi mình sửa như vậy tại sao có một hạng mục cuối cùng không trả được kết quả nhỉ ?
(Mình không thể gửi file đính kèm được, Bạn Ba Tê có mail không, để mình gửi vào mail nhờ bạn giúp)

 

[Ba Tê]

Mình vào ô O6, vào menu Insert - Name - Define... gán vào các Name:
GPE=OFFSET('Don gia chi tiet'!$A6;;;MATCH('Don gia chi tiet'!$A6+1;'Don gia chi tiet'!$A6:$A$1000;0)-1
GPE1=OFFSET(GPE;;3)
GPE2=OFFSET(GPE;;8)
Với những sheet có nhiều hạng mục hơn thì mình chỉ việc sửa 'Don gia chi tiet'!$A6:$A$1000 thành 'Don gia chi tiet'!$A6:$A$3000 đúng không ?
Sau khi mình sửa như vậy tại sao có một hạng mục cuối cùng không trả được kết quả nhỉ ?
(Mình không thể gửi file đính kèm được, Bạn Ba Tê có mail không, để mình gửi vào mail nhờ bạn giúp)

Đúng là công thức chưa hoàn chỉnh, xài tạm "lá bùa" này nếu không muốn sửa công thức.
Bùa ở ô A610.

 

[chttuan]

Cho mình hỏi: Tại sao trong sheet dự toán của mình, đối với những công việc mà một đơn giá nào đó = 0 (Ví dụ như công việc số 1), thì khi mình dùng công thức khối lượng x đơn giá, kết quả lại trả về là #VALUE vậy, và cách khắc phục thế nào hả bạn ?
Thanks ./.

 

[Ba Tê]

Cho mình hỏi: Tại sao trong sheet dự toán của mình, đối với những công việc mà một đơn giá nào đó = 0 (Ví dụ như công việc số 1), thì khi mình dùng công thức khối lượng x đơn giá, kết quả lại trả về là #VALUE vậy, và cách khắc phục thế nào hả bạn ?
Thanks ./.

Cách khắc phục là đổi dữ liệu rỗng thành 0, từ:

=IF(OR($A6="";ISNA(MATCH(O$5;GPE1;0)));"";INDEX(GPE2;MATCH(O$5;GPE1;0)))

thành:

=IF(OR($A6="";ISNA(MATCH(O$5;GPE1;0)));0;INDEX(GPE2;MATCH(O$5;GPE1;0)))

Thử xem có được không?