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

Liên hệ QC
Chào các thầy cô ạ. Em có bảng demo như hình bên dưới.
224282

Em muốn tính tổng như ô G11. Hình như có cái gì đó nó sai ở đoạn "3521L*". Dẫn đến kết quả không như mong muốn.
Nhờ thầy cô chỉ giúp em và gợi ý giúp em cách tính tổng dựa vào chuỗi kí tự trong chuỗi mẹ được không ạ
 

File đính kèm

  • demo.xlsb
    8.4 KB · Đọc: 5
Chào các thầy cô ạ. Em có bảng demo như hình bên dưới.
View attachment 224282

Em muốn tính tổng như ô G11. Hình như có cái gì đó nó sai ở đoạn "3521L*". Dẫn đến kết quả không như mong muốn.
Nhờ thầy cô chỉ giúp em và gợi ý giúp em cách tính tổng dựa vào chuỗi kí tự trong chuỗi mẹ được không ạ
SUMPRODUCT() không dùng ký tự đại diện *,?...
PHP:
G2 =SUMIF($A$2:$A$5;$F2 & "*";B$2:B$5)
 
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 ạ !
 

File đính kèm

  • vd2a.xlsx
    10.5 KB · Đọc: 7
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é
 
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 :(1578897743621.png
 

File đính kèm

  • Tháng 01.2020 BC NXT xưởng.xlsx
    993.4 KB · Đọc: 10
  • 1578897662802.png
    1578897662802.png
    307.9 KB · Đọc: 3
  • BC NXT T12.2019.xlsx
    1.8 MB · Đọc: 10
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 ^^
 

File đính kèm

  • NXT - aQuang.rar
    686 KB · Đọc: 16
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 ạ
 

File đính kèm

  • Cao đẳng Điều dưỡng 14A (7).xlsm
    51.8 KB · Đọc: 2
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
 
=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 ạ
 
=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í đó
 
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.
 

File đính kèm

  • HOi.xlsx
    24.4 KB · Đọc: 2
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)
Mã:
=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
 
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)
Mã:
=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 :)
 
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
 
Web KT
Back
Top Bottom