Chuyên mục GIẢI THÍCH CÔNG THỨC

[kokano90]

SUMPRODUCT() không dùng ký tự đại diện *,?...

G2 =SUMIF($A$2:$A$5;$F2 & "*";B$2:B$5)

cám ơn thầy ạ

 

[lala_qn]

Em chào anh chị ạ !
Trong vd2a này em muốn nhập kí tự 1,2,3 ở cột A (sheet1), thì bên sheet2 các cột B,C,D tự động lấy CT ở cột C (sheet1) sang lọc ạ
Nhờ anh chị giúp dùm em CT ở sheet2 cột B,C,D ạ,
Cảm ơn anh chị nhiều ạ !

 

[tieuthubuongbinh]

Thu

Em chào anh chị ạ !
Trong vd2a này em muốn nhập kí tự 1,2,3 ở cột A (sheet1), thì bên sheet2 các cột B,C,D tự động lấy CT ở cột C (sheet1) sang lọc ạ
Nhờ anh chị giúp dùm em CT ở sheet2 cột B,C,D ạ,
Cảm ơn anh chị nhiều ạ !

Thực sự ko hiểu mục đích của bạn là gì. Mình đẩy bài bạn lên cho có người đọc nhé

 

[thaonguyen1993]

Anh chị giải thích giúp em công thức này với ạ. em xin bảng từ 1 người khác. khi e áp dụng công thức này vào bảng mới của em mà nó không ra
=IFERROR(OFFSET(Pivot!$C$1,AGGREGATE(15,6,ROW(Pivot!$C$3:$C$1690)/(N(Pivot!$B$3:$B$1690='THNXT TP'!$A11))/(N(Pivot!$G$3:$G$1690=$F$3)),COUNTIF('THNXT TP'!$Q$8:R$8,"Ngày SX"))-1,),"")
em xin bảng dữ liệu tháng 12 từ 1 người khác. khi e áp dụng công thức này vào bảng mới Tháng 1/2020 của em mà nó không ra

 

[demiyeu]

Hi cả nhà,
Em có file excel nhập xuất tồn nhờ mọi người giúp sửa lại công thức ở Sheet " Thẻ kho" với ạ. Hiện em mới nhập tạm được 2 mã phụ liệu của Thùng carton(MVN3NPL92VN ) và móc treo(MVN1NPL83VN), còn nhiều phụ liệu khác nữa chưa nhập. Em muốn xem thẻ kho của Thùng carton hoặc móc treo trong tháng mà đang bị lỗi công thức. Em nhờ cả nhà giúp em với ạ.
Em cám ơn nhiều. Mọi người xem file đính kèm giúp em ^^

 

[thoht]

Public Sub s_Gpe()
Const CoLs As Long = 36, TKHP As String = "TKHP"
Dim sArr(), dArr(), I As Long, J As Long, K As Long, N As Long, R As Long, Mon As String
sArr = Sheets("Diem_TChi").Range("A7", Sheets("Diem_TChi").Range("A10").End(xlDown)).Resize(, CoLs).Value
R = UBound(sArr)
ReDim dArr(1 To R * 6, 1 To 6)
For I = 4 To R
For J = 15 To CoLs Step 4
If sArr(I, J) < 4 Then
K = K + 1
dArr(K, 1) = K
For N = 2 To 4
dArr(K, N) = sArr(I, N)
Next N
Mon = Split(sArr(1, J), "_")(1)
dArr(K, 6) = Left(Mon, Len(Mon) - 4)
End If
Next J
Next I
With Sheets("DS_ThiLai")
.Range("A5").Resize(K, 6) = dArr
End With
End Sub
Nhờ các anh chị giải thích chương trình này cho e với ạ. E mày mò mãi vẫn chưa hiểu ạ

 

[quochr]

Mình có đính kèm file excel (Công thức ở CP73) Phần bảng mình có bôi vàng trên file đính kèm.
Mình tách biệt các công thức nhỏ ra thi nó đúng
Full: {=IFERROR(IF(LEN(CO73)=0;0;COUNTIFS((OFFSET($C$8;0;$CP$21;120));$CO73;$CH$8:$CH$120;1));"")}
Tách 1: {=COUNTIFS((OFFSET($C$8;0;$CP$21;120));CO73}: OK
Tách 2: {=COUNTIFS($CH$8:$CH$120;1))}
Nhưng gom lại thì nó ko ra đúng
Cao nhân hướng dẫn với.
Thanks

 

[NGOC310]

=IFERROR(AGGREGATE(15;6;AU$10:AU$183/(AU$10:AU$183>0);ROWS(BJ$193:BJ194));"")
=IFERROR(INDEX(BJ$194:BJ$346;MATCH(;INDEX(COUNTIF(BC$193:BC193;BJ$194:BJ$346);;"")
Các bác giải thích giúp em công thức này với ạ

 

[bebo021999]

=IFERROR(AGGREGATE(15;6;AU$10:AU$183/(AU$10:AU$183>0);ROWS(BJ$193:BJ194));"")
Trên thanh công thức, bôi đen từng phần công thức nhỏ bên trong là hiểu thôi mà
AGGREGATE(15,6, vùng_kết_quả, n) với n tăng dần 1 đơn vị khi copy xuống, chính là trả về giá trị nhỏ thứ n (từ 1,2,3...) trong vùng kết quả.
vùng kết quả: = AU$10:AU$183/(AU$10:AU$183>0) chính là vùng giá trị với điều kiện >0

=IFERROR(INDEX(BJ$194:BJ$346;MATCH(;INDEX(COUNTIF(BC$193:BC193;BJ$194:BJ$346);;"")
Có thể CT này thiếu, mình đoán nó thế này:
INDEX(DANH SÁCH, MATCH(0,COUNTIF(DANH SÁCH,vùng_ngay_phía_trên),0)
Cấu trúc này thường dùng để trích lọc ra danh sách duy nhất từ 1 danh sách cho trước.
với:
countif giúp tạo ra mảng 0 và >0, trong đó số 0 là vị trí mà chưa lọc ra từ DANH SÁCH
MATCH(0,COUNTIF,0) chỉ ra vị trí tương đối trong DANH SÁCH
INDEX giúp lấy ra vị trí đó

 

[nghiank09]

Mình nhờ mọi người giải thích giúp mình công thức ở các ô được tô xanh của sheet Trực VS ( sheet2). Mình cám ơn rất nhiều.

 

[bebo021999]

Mình nhờ mọi người giải thích giúp mình công thức ở các ô được tô xanh của sheet Trực VS ( sheet2). Mình cám ơn rất nhiều.

Dùng công thức khác, cũng dùng INDEX và AGGREGATE, nhưng tránh dùng OFFSET (làm chậm bảng tính) và không phải công thức mảng (Chỉ cần Enter thay vì Ctrl-Shift-Enter)

=IFERROR(INDEX(Sheet1!$B$1:$B$7,AGGREGATE(15,6,ROW(Sheet1!$B$3:$B$7)/(Sheet1!$C$1:$FZ$1=B$1)/(Sheet1!$C$2:$FZ$2=B$2)/(Sheet1!$C$3:$FZ$7=$A$2),ROWS($1:1))),"")

Để hiểu công thức, trên thanh công thức bạn highlight từng CT nhỏ bên trong và nhấn F9 để xem kết quả;
VD: Sheet1!$C$1:$FZ$1=B$1 là biểu thức điều kiện của ngày thứ n. Bôi đen và F9 sẽ thấy chuỗi TRUE/FALSE (=0 hoặc 1)
Có 3 BT đk như vậy chia nhau sẽ tạo chuỗi 1 và DIV/0, nơi nào thỏa cả 3 sẽ là 1
Lấy ROW cột B chia cho chuỗi đó sẽ ra chuỗi gồm số thứ tự dòng và DIV/0
Cho vào bên trong hàm AGGREGATE để lấy lần lựơt số TT dòng đó, từ nhỏ tới lớn
Cuối cùng là INDEX để lấy chuỗi tương ứng trong cột B

 

[nghiank09]

Dùng công thức khác, cũng dùng INDEX và AGGREGATE, nhưng tránh dùng OFFSET (làm chậm bảng tính) và không phải công thức mảng (Chỉ cần Enter thay vì Ctrl-Shift-Enter)

=IFERROR(INDEX(Sheet1!$B$1:$B$7,AGGREGATE(15,6,ROW(Sheet1!$B$3:$B$7)/(Sheet1!$C$1:$FZ$1=B$1)/(Sheet1!$C$2:$FZ$2=B$2)/(Sheet1!$C$3:$FZ$7=$A$2),ROWS($1:1))),"")

Để hiểu công thức, trên thanh công thức bạn highlight từng CT nhỏ bên trong và nhấn F9 để xem kết quả;
VD: Sheet1!$C$1:$FZ$1=B$1 là biểu thức điều kiện của ngày thứ n. Bôi đen và F9 sẽ thấy chuỗi TRUE/FALSE (=0 hoặc 1)
Có 3 BT đk như vậy chia nhau sẽ tạo chuỗi 1 và DIV/0, nơi nào thỏa cả 3 sẽ là 1
Lấy ROW cột B chia cho chuỗi đó sẽ ra chuỗi gồm số thứ tự dòng và DIV/0
Cho vào bên trong hàm AGGREGATE để lấy lần lựơt số TT dòng đó, từ nhỏ tới lớn
Cuối cùng là INDEX để lấy chuỗi tương ứng trong cột B

Cám ơn bạn, mình sẽ chia nhỏ công thức để tìm hiểu. Bạn cho mình hỏi thêm hàm Aggregate hay như vậy nhưng Google sheet không có hàm này thì có cách nào khắc phục không

 

[ngocnhan1999]

Trong công thức của anh có phần này khó hiểu ,không biết người hỏi có biết không ?sao không thấy hỏi ?em thì ,
(RIGHT($B3)="B")+3 Phần này thì em bó tay .
như công thức này thì ,vế này nếu trả ra FALSE Thì lấy dử liệu ở cột thứ 3 .
Không lẻ ? FALSE+3 =3 ? Đúng nó bằng 3 thật sau nó ra như thế vậy anh ?
Còn TRUE+3= 4 Còn trường hợp này lại bằng 4 ? lấy dử liệu ở cột số 4 .
Em không hiểu ?

hiểu đơn giản thì hàm VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) ở vế col_index_num được định nghĩa là Số thứ tự của cột trong table_array chứa thông tin mà bạn muốn trả về. Đếm từ cột đầu tiên của table_array.
Nhưng ở câu trả lời này =VLOOKUP(LEFT($B3),$B$12:$E$16,(RIGHT($B3)="B")+3,0) , vế col_index_num là (RIGHT($B3)="B")+3 sẽ được máy tính nó hiểu kiểu này,
- Trường hợp 1: xét ký tự bên phải của ô B3 nếu là ký tự B thì sẽ trả kết quả cột 1+ 3 = cột 4 chính là cột của học bổng khu vực B.
- Trường hợp 2: xét ký tự bên phải của ô B3 không phỉa là ký tự B thì sẽ bỏ qua hàm [ (RIGHT($B3)="B")+ ] mà xét mỗi số 3 thì là trả về kết quả từ cột 3. mà cột 3 là học bổng khu vực A
Kết luận: cách làm này vẫn đúng hơi khó hiểu vế (RIGHT($B3)="B")+3 nhưng mình nghĩ sẽ dễ hiểu hơn sau khi bạn đọc bài mình giải thích

 

[ptm0412]

1999 có lẽ sinh năm ấy.
Giá như 1989 thì tốt quá. Vậy thì bạn sẽ kịp đăng bài này.

Đùa thế thôi, chứ 1999 là = em gái tớ.

Lại nói nhảm nữa rồi?

 

[tu205489039]

Dạ nhờ mọi người giải thích giúp mình cách thức hoạt động của hàm DATEVALUE(A2&1) trong file nghĩa là gì với được không ạ
Đề bài yêu cầu, chuyển tháng bằng chữ sang dạng số ạ