Chuyên đề giải đáp các bài tập Excel (Phần 3)

[mackydinh]

=countif($b$4:$b$11,"n1*")
bạn thay thế lần lượt n2,n3,
bạn xem file nhe

cảm ơn bạn nhiều lắm. Bạn cho mình hỏi thêm còn cách nào điền công thức một lần rồi quét xuống không bạn? Thanks bạn nhiều

 

[Phuocam]

cảm ơn bạn nhiều lắm. Bạn cho mình hỏi thêm còn cách nào điền công thức một lần rồi quét xuống không bạn? Thanks bạn nhiều

Công thức D15:
=COUNTIF($B$4:$B$11,B15&"*")
Kéo xuống D16, D17

 

[vivamosforever]

Xin trợ giúp

Tôi đang làm 1 báo cáo
Sheet BN: dữ liệu về bệnh nhân. BN đã được điều trị thì có cả ngày vào và ngày ra. BN đang được điều trị thì có ngày vào nhưng ngày ra thì chưa có.


Nhờ mọi người giúp tôi 3 vấn đề
1. Cách tính số BN được điều trị và số ngày điều trị theo từng bác sỹ trong tháng (cột màu xanh lục). Theo cách tôi đang tính thì chỉ BN nào đã có ngày ra viện (đã xuất viện) thì mới tính được, những BN mà chưa có ngày ra viện (đang điều trị) sẽ bị bỏ sót.
Tương tự vậy là các cột xanh dương
2. Cách tính cho bệnh lý khác (hàng màu vàng): bệnh lý khác là tất những bệnh còn lại mà có mã bệnh bất kỳ, khác với những bệnh được liệt kê ở trên
3. Trong sheet "khoa noi" làm thế nào để hiển thị được dòng thứ 1 (hiện đang hiển thị từ dòng 817)


Cảm ơn trước

 

[leonguyenz]

Tôi đang làm 1 báo cáo
Sheet BN: dữ liệu về bệnh nhân. BN đã được điều trị thì có cả ngày vào và ngày ra. BN đang được điều trị thì có ngày vào nhưng ngày ra thì chưa có.
Nhờ mọi người giúp tôi 3 vấn đề
1. Cách tính số BN được điều trị và số ngày điều trị theo từng bác sỹ trong tháng (cột màu xanh lục). Theo cách tôi đang tính thì chỉ BN nào đã có ngày ra viện (đã xuất viện) thì mới tính được, những BN mà chưa có ngày ra viện (đang điều trị) sẽ bị bỏ sót.
Tương tự vậy là các cột xanh dương
2. Cách tính cho bệnh lý khác (hàng màu vàng): bệnh lý khác là tất những bệnh còn lại mà có mã bệnh bất kỳ, khác với những bệnh được liệt kê ở trên
3. Trong sheet "khoa noi" làm thế nào để hiển thị được dòng thứ 1 (hiện đang hiển thị từ dòng 817)
Cảm ơn trước

Sử dụng hàm SUMPRODUCT để cho nhiều điều kiện và tính tổng.
Bạn xem kết quả trong file.
Còn 2 cột Đỡ và KĐ tôi không hiểu bạn nói đến điều kiện nào.
Sheet Khoa Noi bạn đã Unhide từ dòng 1 đến dòng 816, chọn dòng 817 kéo chuột lên, chuột phải, Unhide.

 

[sươngsom]

T lam nhung k bit dinh fle kiêu gi

nghiaphuc: Bạn hãy sử dụng tiếng Việt có dấu khi viết bài nhé. Bài viết không dấu sẽ bị xóa.

 

[vivamosforever]

Cảm ơn bạn đã giúp đỡ.
Trong sheet BN thì những BN nào có kết quả là 1 tức là khỏi, 2 là đỡ, 3 là không đỡ

Còn câu hỏi số 2 nhờ bạn hướng dẫn nốt (tính kết quả cho các ô màu vàng).
Bệnh khác ở đây ví dụ như những bệnh có mã là M19, G52, R69 (vì ít gặp nên xếp thành 1 nhóm)
Tính số BN theo tháng của nhóm này, số khỏi đỡ, ngày điều trị.

 

[vivamosforever]

Nhờ bạn lý giải cụ thể một tí (hàm sumproduct có điều kiện tôi chưa hiểu rõ lắm)
1. Tính số BN thì hàm này giống countif? Đoạn đk chỗ bôi đỏ có nghĩa thế nào?
=SUMPRODUCT((BN!$L$5:$L$53=$C6)*(BN!$G$5:$G$53<=$G$3)*((BN!$H$5:$H$53="")+(BN!$H$5:$H$53>=$D$3))*(BN!$H$5:$H$53<=$G$3))
2. Tính số ngày điều trị, thấy khó quá, nhờ bạn giải thích giùm công thức này
=sumproduct((bn!$l$5:$l$53=$c6)*(bn!$g$5:$g$53<=$g$3)*((bn!$h$5:$h$53="")+(bn!$h$5:$h$53>=$d$3))*(bn!$h$5:$h$53<=$g$3),(bn!$h$5:$h$53="")*(today()-bn!$g$5:$g$53)+(bn!$h$5:$h$53<>"")*(bn!$h$5:$h$53-bn!$g$5:$g$53))

 

[anhhaodaklak03]

Bạn tính dư CT rồi
BẠn thay bởi CT này thử xem: =IF(C5<3000;HLOOKUP(G5;$F$19:$I$21;2;0);HLOOKUP(G5;$F$19:$I$21;3;0))

 

[duyphuongstellar]

Giúp mình với

Điểm trung bình (ĐTB): được tính với điểm toán hệ số 3, điểm văn hệ số 2, sinh ngữ hệ số 2 và làm tròn chữ số thập phân
Điểm kết quả (ĐK quả): học viên nào có ĐTB>=5 thì ĐK quả là ĐTB, ngược lại ĐK quả là ĐTB cộng với Đ Thêm. Nhưng sau khi cộng thêm mà lớn hơn 5 thì chỉ lấy 5 mà thôi
Bài này nằm ở sheet 2 nha mọi người

 

[concogia]

Điểm trung bình (ĐTB): được tính với điểm toán hệ số 3, điểm văn hệ số 2, sinh ngữ hệ số 2 và làm tròn chữ số thập phân
Điểm kết quả (ĐK quả): học viên nào có ĐTB>=5 thì ĐK quả là ĐTB, ngược lại ĐK quả là ĐTB cộng với Đ Thêm. Nhưng sau khi cộng thêm mà lớn hơn 5 thì chỉ lấy 5 mà thôi
Bài này nằm ở sheet 2 nha mọi người

Công thức ở [K3]:

=IF(J3>=5,J3,MIN(J3+H3,5))

Kéo xuống
Thân

 

[Hieu07061984]

Điểm trung bình (ĐTB): được tính với điểm toán hệ số 3, điểm văn hệ số 2, sinh ngữ hệ số 2 và làm tròn chữ số thập phân
Điểm kết quả (ĐK quả): học viên nào có ĐTB>=5 thì ĐK quả là ĐTB, ngược lại ĐK quả là ĐTB cộng với Đ Thêm. Nhưng sau khi cộng thêm mà lớn hơn 5 thì chỉ lấy 5 mà thôi
Bài này nằm ở sheet 2 nha mọi người

Bạn xem file đính kèm.

 

[Hau151978]

Cảm ơn bạn đã giúp đỡ.
Trong sheet BN thì những BN nào có kết quả là 1 tức là khỏi, 2 là đỡ, 3 là không đỡ

Còn câu hỏi số 2 nhờ bạn hướng dẫn nốt (tính kết quả cho các ô màu vàng).
Bệnh khác ở đây ví dụ như những bệnh có mã là M19, G52, R69 (vì ít gặp nên xếp thành 1 nhóm)
Tính số BN theo tháng của nhóm này, số khỏi đỡ, ngày điều trị.

Xét bệnh nhân có mã bệnh ở BN!J5:
- Để xem có"bệnh khác" hay không ta dùng công thức =ISNA(MATCH(BN!J5,TK!$M$6:$M$15,0))
- Để biết bệnh nhân có điều trị trong khoảng thời gian từ D3 đến G3 ta dùng công thức =AND(RV>=D3,VV<=G3), trong đó VV là ngày vào viện (BN!G5), RV là ngày ra viện (BN!H5) hoặc TODAY() nếu ngày ra viện trống (RV=IF(ISBLANK(BN!H5),TODAY(),BN!H5)
- Để tính số ngày nằm viện từ D3 đến G3 ta xét các trường hợp
1. VV <= D3< RV <=G3 số ngày là RV-D3
2. VV<=D3<G3<=RV số ngày là G3-D3
3. D3<VV<RV<G3 số ngày là RV-VV
4. D3<VV<G3<=RV số ngày là G3-VV
Công thức chung cho 4 trường hợp trên là MIN(RV,G3)-MAX(VV,D3)
5. RV=D3 hoặc VV=G3 hoặc D3<=VV=RV<=G3 (nếu ngày ra viện trùng ngày vào) số ngày là 1
6. RV<D3 hoặc VV>G3 số ngày là 0
Công thức chung cho 6 trường hợp trên là =IF(MIN(RV,G3)>MAX(VV,D3),MIN(RV,G3)-MAX(VV,D3),IF(MIN(RV,G3)=MAX(VV,D3),1,0)), đây là số ngày nằm viện của một bệnh nhân.

Công thức ở ô R16 tính tổng ngày nằm viện của các bệnh nhân có bệnh khác trong khoảng D3 đến G3 là
=SUMPRODUCT(ISNA(MATCH(BN!J5:J53,TK!$M$6:$M$15,0))* IF(IF(IF(ISBLANK(BN!H5:H53),TODAY(),BN!H5:H53)>G3,G3,IF(ISBLANK(BN!H5:H53),TODAY(),BN!H5:H53))>IF(BN!G5:G53>D3,BN!G5:G53,D3),IF(IF(ISBLANK(BN!H5:H53),TODAY(),BN!H5:H53)>G3,G3,IF(ISBLANK(BN!H5:H53),TODAY(),BN!H5:H53))-IF(BN!G5:G53>D3,BN!G5:G53,D3),IF(IF(IF(ISBLANK(BN!H5:H53),TODAY(),BN!H5:H53)>G3,G3,IF(ISBLANK(BN!H5:H53),TODAY(),BN!H5:H53))=IF(BN!G5:G53>D3,BN!G5:G53,D3),1,0)))

Để dễ theo dõi, bạn copy công thức =IF(MIN(RV,G3)>MAX(VV,D3),MIN(RV,G3)-MAX(VV,D3),IF(MIN(RV,G3)=MAX(VV,D3),1,0)) ra file word rồi dùng chức năng thay thế MIN(RV,G3) thành IF(RV>G3,G3,RV), MAX(VV,D3) thành IF(VV>D3,VV,D3). Thay RV thành IF(ISBLANK(BN!H5:H53),TODAY(),BN!H5:H53). Thay VV thành BN!G5:G53. Mình không dùng hàm MIN và MAX vì MIN(BN!G5:G53,G3) là giá trị nhỏ nhất của 49 số gồm tất cả cột ngày vào viện và G3 dẫn đến kết quả sai.

 

[duyphuongstellar]

Giúp mình với

1. Tên hàng: dựa vào ký tự đầu của mã hàng và tra trong bảng đơn giá.
2. Loại hàng: là ký tự thứ 3 của mã hàng và đổi sang dạng số.
3. Thành tiền = số lượng * đơn giá (Đơn giá: dựa vào ký tự thứ 3 của mã hàng) và tra trong bảng đơn giá.
4. Thuế = thành tiền * tỷ lệ thuế và chỉ tính cho những mặc hàng không thuộc loại miễn thuế. (Tỷ lệ thuế được tính dựa vào ký tự thứ 2 của mã hàng và tra trong bảng tỷ lệ thuế).
5. Còn lại là thành tiền sau khi đã trừ thuế đi.
6. Tính toán theo bảng thống kê sau.
7. Trích ra những mặt hàng đã được miễn thuế.
8. Tạo biểu đồ thống kê cho bảng số liệu câu 6

Bài này ở sheet KTNK nha mọi người.

 

[Hieu07061984]

Bạn xem có phù hợp chưa.

 

[park.justin95]

Xin giúp mình về hàm tích có điều kiện

Mọi người giúp mình làm trong cột thành tiền với.
Thành tiền = Số lượng x đơn giá
Mổi loại sp có 1 đơn giá khác nhau, mình không biết dùng hàm gì, các bạn giúp mình với
cảm ơn mn nhé

 

[Hieu07061984]

Mọi người giúp mình làm trong cột thành tiền với.
Thành tiền = Số lượng x đơn giá
Mổi loại sp có 1 đơn giá khác nhau, mình không biết dùng hàm gì, các bạn giúp mình với
cảm ơn mn nhé

Bạn đặt tại ô D4:
=C4*VLOOKUP(LEFT(A4,1),$A$16:$C$20,3,0)
Sau đó auto fill xuống.

 

[park.justin95]

cảm ơn Hiếu nhé

 

[thao86]

kiểm tra dối chiếu số liệu giữa 2 file

Mấy anh mấy chị làm ơn giúp e với ah..
Em đang cần kiểm tra số liệu tồn đầu kỳ của cả 2 file "tổng hợp" & "chi tiết" này nhưng vì bên cột "mã hàng" của 2 file thứ tự lại khác nhau nên e ko biết dò tìm thế nào... cám ơn cả nhà

 

[hoangminhtien]

Mấy anh mấy chị làm ơn giúp e với ah..
Em đang cần kiểm tra số liệu tồn đầu kỳ của cả 2 file "tổng hợp" & "chi tiết" này nhưng vì bên cột "mã hàng" của 2 file thứ tự lại khác nhau nên e ko biết dò tìm thế nào... cám ơn cả nhà

Cái này bạn nên dùng Pivot table với Multiple Consolidation Ranges.
TÌm hiểu bài #7 ở link http://www.giaiphapexcel.com/forum/...Table-PivotChart-2007-Từ-căn-bản-đến-nâng-cao

 

[tieuthubuongbinh]

Sheet OMR:
1.cần lookup cho cột G từ sheet new+outstanding+dipatched 2.Cần vlookup cho n sheet thì kết hợp ntn cho IFERROR nhỉ? (fill cho column D&E)

Sheet dispatched - KPI Report
3. ô màu xanh là các phần mình cần giúp CT vì thường ngày mình phải dùng filter cho từng cột với cách không select blank trong cột E nên mất thời gian quá (dùng pivot cũng không giải quyết được nhiều vì vẫn phải loại bỏ blank của cột E khi kết hợp các cột khác để tính toán)
câu hỏi chung của mình là có CT nào đáp ứng đựợc việc cho các cột khác hiểu theo cột E vì chỉ có cột E là bị merge nên các cột khác sẽ tự loại các row bị lập lại
Vd: E4 là shipment bị trễ (lấy từ cột D4) nhưng nếu dùng count thì sẽ thành 2 (D4 và D5) trong khi thực tế chỉ có 1 shipment bị trễ là SGHUB00000660766

4.CT cho urgent order khi font có màu đỏ (bất cứ row nào có chữ đỏ là urgent)
làm ơn đừng bắt mình đổi thứ tự column/row vì file này nhiều người cùng sử dụng nên ko tự chủ được
cám ơn các bác rất rất nhiều

Sheet excel
5. Ô tô vàng, sao subtotal ko hiểu giá trị của B4:B5 là số trong khi product lại hiểu nhỉ?