Tóm tắt: Đếm số ô chứa giá trị khác 0 khác rỗng trong một vùng dữ liệu.
Hàm: =LAMBDA(range,SUM(--((LEN(range)>0)*(range<>0))))
Ví dụ: =COUNTVALUE(A1:C10) → Trả về số ô chứa giá trị cả số, cả chữ khác 0 trong vùng A1:C10.
Tóm tắt: Đếm số ô chứa giá trị lỗi trong một vùng dữ liệu.
Hàm: =LAMBDA(range,SUM(--ISERROR(TOCOL(range,1))))
Ví dụ: =COUNTERRORS(A1:C10) → Trả về số ô chứa lỗi (như #N/A, #VALUE!, #DIV/0!, #REF!, ...) trong vùng A1:C10.
Tóm tắt: Trả về ký tự cột (Column Letter) của một ô hoặc vùng dữ liệu. Nếu không truyền tham số, hàm sẽ trả về ký tự cột của chính ô chứa công thức
Cú pháp: COLUMNLETTER([reference])
Tham số: reference (tùy chọn): Ô hoặc vùng dữ liệu cần lấy ký tự cột. Nếu bỏ qua, hàm sử dụng ô chứa công thức.
Ví dụ:
=COLUMNLETTER(A1) → A
=COLUMNLETTER(D10) → D
=COLUMNLETTER(XFD1) → XFD
=COLUMNLETTER() → Ký tự cột của ô chứa công thứcTóm tắt: Trả về tên của worksheet chứa ô được chỉ định hoặc ô hiện tại nếu bỏ qua tham số.
Hàm: =LAMBDA([reference],LET(ref,IF(ISOMITTED(reference),INDIRECT("RC",FALSE),reference),TEXTAFTER(CELL("filename",ref),"]")))
Ví dụ: =SHEETNAME() → Trả về tên của worksheet hiện tại (ví dụ: Data).
Tóm tắt: Trả về tên thư mục chứa tệp Excel hiện tại.
Hàm: =LAMBDA(LET(path,TEXTBEFORE(CELL("filename"),"["),IF(path="","",TEXTAFTER(LEFT(path,LEN(path)-1),"\",-1))))
Ví dụ: =FOLDERNAME() → Trả về tên thư mục chứa sổ làm việc (ví dụ: Reports).
Tóm tắt: Trả về đường dẫn đầy đủ của thư mục chứa tệp Excel hiện tại.
Hàm: =LAMBDA(IF(CELL("filename")="","",TEXTBEFORE(CELL("filename"),"[")))
Ví dụ: =FOLDERPATH() → Trả về C:\Users\Hung\Documents\Reports\ nếu sổ làm việc nằm trong thư mục đó.
Tóm tắt: Trả về danh sách các chuỗi là chuỗi con của ít nhất một chuỗi khác trong cùng vùng dữ liệu.
Hàm: =LAMBDA(text_range,UNIQUE(FILTER(text_range,MAP(text_range,LAMBDA(text,SUM((text_range<>text)*ISNUMBER(SEARCH(text,text_range)))>0)),"null")))
Ví dụ: =INTEGRATE(A2:A6) → Với {"AB";"ABC";"BCD";"BC";"XYZ"} trả về {"AB";"BC"} vì "AB" có trong "ABC" và "BC" có trong "ABC" hoặc "BCD".
Tóm tắt: Trả về danh sách các giá trị bị trùng lặp trong vùng dữ liệu.
Hàm: =LAMBDA(value_range,FILTER(UNIQUE(value_range),COUNTIF(value_range,UNIQUE(value_range))>1,"null"))
Ví dụ: =DUPLICATES(A2:A8) → Với {"A";"B";"A";"C";"B";"D";"E"} trả về {"A";"B"}.
Tóm tắt: Trả về danh sách các hàng bị trùng lặp trong vùng dữ liệu.
Hàm: =LAMBDA(data_range,LET(key,BYROW(data_range,LAMBDA(row,TEXTJOIN(CHAR(1),,row))),count,MAP(key,LAMBDA(k,SUM(--(key=k)))),UNIQUE(FILTER(data_range,count>1,"null"))))
Ví dụ: =DUPLICATEROWS(A2:C8) → Trả về các hàng xuất hiện từ 2 lần trở lên trong vùng A2:C8.
Tóm tắt: Trả về dãy số thứ tự từ 1 đến số hàng của vùng dữ liệu.
Hàm: =LAMBDA(data_range,SEQUENCE(ROWS(data_range)))
Ví dụ: =ROWNUM(A2:A6) → Trả về {1;2;3;4;5}.
Tóm tắt: Trả về một số nguyên ngẫu nhiên trong khoảng cho trước, không trùng với các giá trị trong vùng chỉ định.
Hàm: =LAMBDA(min_value,max_value,existing_values,LET(pool,FILTER(SEQUENCE(max_value-min_value+1,,min_value),ISNA(XMATCH(SEQUENCE(max_value-min_value+1,,min_value),TOCOL(existing_values,1)))),IFERROR(INDEX(pool,RANDBETWEEN(1,ROWS(pool))),NA())))
Ví dụ: =RANDEXCEPTN(1,100,A2:A20) → Trả về một số ngẫu nhiên từ 1 đến 100 chưa xuất hiện trong vùng A2:A20; nếu không còn số hợp lệ thì trả về #N/A.
Tóm tắt: Thay thế nhiều chuỗi trong văn bản theo danh sách cặp giá trị cũ và mới phân tách bằng dấu chấm phẩy (;).
Hàm: =LAMBDA(text,old_texts,new_texts,LET(old_list,TOCOL(TEXTSPLIT(old_texts,";")),new_list,TOCOL(TEXTSPLIT(new_texts,";")),REDUCE(text,SEQUENCE(ROWS(old_list)),LAMBDA(result,i,SUBSTITUTE(result,INDEX(old_list,i),INDEX(new_list,i))))))
Ví dụ: =SUBSTITUTES("Hà Nội, Việt Nam","Hà;Việt","TP.;VN") → TP. Nội, VN
Tóm tắt: Thay thế hàng loạt chuỗi ký tự trong văn bản bằng cách lần lượt thay mỗi giá trị trong old_text_range bằng giá trị tương ứng trong new_text_range.
Hàm: =LAMBDA(text,old_text_range,new_text_range,LET(old_list,TOCOL(old_text_range,1),new_list,TOCOL(new_text_range,1),REDUCE(text,SEQUENCE(ROWS(old_list)),LAMBDA(result,i,SUBSTITUTE(result,INDEX(old_list,i),INDEX(new_list,i))))))
Ví dụ: =_REPLACE("Hà Nội, Việt Nam",A2:A3,B2:B3) → Nếu A2:A3={"Hà";"Việt"} và B2:B3={"TP.";"VN"} thì kết quả là TP. Nội, VN.
Tóm tắt: Trả về giá trị rỗng hoặc if_error nếu kết quả lỗi, không lỗi trả về giá trị gốc.
Hàm: ==LAMBDA(value,[if_error],IFERROR(value,IF(ISOMITTED(if_error),"",if_error)))
Ví dụ:
Tóm tắt: Trả về giá trị mặc định nếu giá trị đầu vào trống hoặc rỗng, ngược lại trả về chính giá trị đó.
Hàm: =LAMBDA(value,[if_blank],IF(OR(ISBLANK(value),value=""),IF(ISOMITTED(if_blank),0,if_blank),value))
Ví dụ: =DEFAULT(A2,"Chưa nhập") → Trả về "Chưa nhập" nếu A2 trống, ngược lại trả về giá trị của A2.
Tóm tắt: Trả về giá trị mặc định nếu giá trị đầu vào bằng 0, ngược lại trả về giá trị đầu vào.
Hàm: =LAMBDA(value,[if_zero],IF(value=0,IF(ISOMITTED(if_zero),"-",if_zero),value))
Ví dụ: =IFZERO(A2,"N/A") → Trả về "N/A" nếu A2 bằng 0, ngược lại trả về giá trị của A2.
Tóm tắt: Ghép các chuỗi thỏa mãn điều kiện thành một chuỗi, sử dụng ký tự phân tách tùy chọn.
Hàm: =LAMBDA(text_range,criteria,[separator],LET(sep,IF(OR(ISOMITTED(separator),separator=""),";",separator),IFERROR(TEXTJOIN(sep,TRUE,IF(criteria,text_range,"")),"")))
Ví dụ: =TEXTJOINIF(A2:A6,B2:B6="Yes",", ") → Ghép các giá trị trong A2:A6 có ô tương ứng ở B2:B6 bằng "Yes", phân tách bằng ", ".
Tóm tắt: Trả về giá trị đầu tiên trong vùng dữ liệu khác 0 và không rỗng.
Hàm: =LAMBDA(data_range,TAKE(FILTER(data_range,(data_range<>0)*(data_range<>"")),1))
Ví dụ: =FIRSTVALUE(A2:A10) → Với {"",0,"A","B"} trả về "A".
Tóm tắt: Trả về đường dẫn đầy đủ từ đường dẫn tương đối, hỗ trợ lùi thư mục bằng ký tự < và giữ nguyên đường dẫn tuyệt đối hoặc URL.
Hàm: =LAMBDA(path,LET(levels,LEN(path)-LEN(SUBSTITUTE(path,"<","")),current_path,TEXTBEFORE(CELL("filename"),"["),IF(OR(LEFT(path,4)="http",LEFT(path,2)="\\",MID(path,2,1)=":"),path,IF(levels,TEXTBEFORE(current_path,"\",-levels),current_path)&SUBSTITUTE(path,"<",""))))
Ví dụ: =FULLPATH("<<\Data\File.xlsx") → Nếu sổ làm việc hiện tại nằm trong C:\Projects\App\Report\ thì kết quả là C:\Projects\Data\File.xlsx.
Tóm tắt: Trả về đường dẫn đầy đủ từ đường dẫn tương đối, hỗ trợ lùi thư mục bằng ký tự < và giữ nguyên đường dẫn tuyệt đối hoặc URL.
Hàm: =LAMBDA(path,LET(levels,LEN(path)-LEN(SUBSTITUTE(path,"<","")),workbook_path,CELL("filename"),current_folder,LEFT(workbook_path,FIND("[",workbook_path)-2),IF(OR(LEFT(path,4)="http",LEFT(path,2)="\\",MID(path,2,1)=":"),path,IF(levels,LEFT(current_folder,FIND("@",SUBSTITUTE(current_folder,"\","@",LEN(current_folder)-LEN(SUBSTITUTE(current_folder,"\",""))-levels+1))-1),current_folder)&SUBSTITUTE(path,"<",""))))
Ví dụ: =FULLPATH("<<\Data\File.xlsx") → Nếu sổ làm việc hiện tại nằm trong C:\Projects\App\Report\ thì kết quả là C:\Projects\Data\File.xlsx.
Tóm tắt: Trả về đường dẫn đầy đủ bằng cách kết hợp đường dẫn của sổ làm việc hiện tại với đường dẫn tương đối, đồng thời hỗ trợ lùi thư mục bằng ký tự <.
Hàm: =LAMBDA(path,LET(levels,LEN(path)-LEN(SUBSTITUTE(path,"<","")),workbook_path,CELL("filename"),folder,LEFT(workbook_path,FIND("[",workbook_path)-2),IF(OR(LEFT(path,4)="http",LEFT(path,2)="\\",MID(path,2,1)=":"),path,IF(levels,LEFT(folder,FIND("@",SUBSTITUTE(folder,"\","@",LEN(folder)-LEN(SUBSTITUTE(folder,"\",""))-levels+1))-1),folder)&SUBSTITUTE(path,"<",""))))
Ví dụ: =FULLPATH("<<\Data\File.xlsx") → Nếu sổ làm việc hiện tại nằm trong C:\Projects\App\Report\ thì kết quả là C:\Projects\Data\File.xlsx.
Tóm tắt: Kiểm tra xem một giá trị có nằm trong khoảng từ giá trị nhỏ nhất đến giá trị lớn nhất hay không.
Hàm: =LAMBDA(value,min_value,max_value,AND(value>=min_value,value<=max_value))
Ví dụ: =ISBETWEEN(A2,10,20) → Trả về TRUE nếu A2 nằm trong khoảng từ 10 đến 20 (bao gồm cả 10 và 20), ngược lại trả về FALSE.
Tóm tắt: Kiểm tra chuỗi có khớp với mẫu ký tự đại diện (*, ?) hay không, không phân biệt chữ hoa và chữ thường.
Hàm: =LAMBDA(text,pattern,REGEXTEST(LOWER(text),"^"&SUBSTITUTE(SUBSTITUTE(LOWER(pattern),"*",".*"),"?",".")&"$"))
Ví dụ: =LIKE("Report2026.xlsx","report*.xls?") → Trả về TRUE.
Tóm tắt: Kiểm tra xem một giá trị có tồn tại trong danh sách hay không.
Hàm: =LAMBDA(value,list_range,ISNUMBER(XMATCH(value,TOCOL(list_range,1),0)))
Ví dụ: =INLIST("Táo",A2:A10) → Trả về TRUE nếu "Táo" có trong vùng A2:A10, ngược lại trả về FALSE.
Cải tiến: hàm vừa hỗ trợ tìm kiếm chính xác vừa hỗ trợ wildcard,
Hàm: =LAMBDA(value,list_range,[wildcard],LET(w,IF(ISOMITTED(wildcard),FALSE,wildcard),ISNUMBER(XMATCH(value,TOCOL(list_range,1),IF(w,2,0)))))
Ví dụ:
=Contains("Apple",A1:A10) → TRUE (tìm khớp chính xác)=Contains("App*",A1:A10,TRUE) → TRUE (tìm bằng wildcard)=Contains("???le",A1:A10,TRUE) → TRUE=Contains("App*",A1:A10) → FALSE (vì mặc định không dùng wildcard)Tóm tắt: Kiểm tra xem một giá trị không tồn tại trong danh sách.
Hàm: =LAMBDA(value,list_range,NOT(ISNUMBER(XMATCH(value,TOCOL(list_range,1),0))))
Ví dụ: =NOTINLIST("Táo",A2:A10) → Trả về TRUE nếu "Táo" không có trong vùng A2:A10, ngược lại trả về FALSE.
Cải tiến: hàm vừa hỗ trợ tìm kiếm chính xác vừa hỗ trợ wildcard,
Hàm: =LAMBDA(value,list_range,[wildcard],LET(w,IF(ISOMITTED(wildcard),FALSE,wildcard),NOT(ISNUMBER(XMATCH(value,TOCOL(list_range,1),IF(w,2,0))))))
Ví dụ:
=NotContains("Apple",A1:A10) → FALSE=NotContains("Orange",A1:A10) → TRUE=NotContains("App*",A1:A10,TRUE) → FALSE=NotContains("Z*",A1:A10,TRUE) → TRUETóm tắt: Chuyển một mảng thành nhiều cột với số hàng tối đa được chỉ định cho mỗi cột.
Hàm: =LAMBDA(data,max_rows,LET(flat,TOCOL(data,1),count,ROWS(flat),column_count,CEILING(count/max_rows,1),index,SEQUENCE(max_rows,column_count,1,1),IF(index>count,"",INDEX(flat,index))))
Ví dụ: =WRAPCOLS2(A1:A10,3) → Chuyển 10 giá trị trong A1:A10 thành 4 cột, mỗi cột tối đa 3 hàng.
Tóm tắt: Mã hóa chuỗi bằng cách tăng mã ASCII của mỗi ký tự thêm 3.
Hàm: =LAMBDA(text,length,TEXTJOIN("",TRUE,CHAR(CODE(MID(text,ROW(INDIRECT("1:"&LEN(text))),1))+,length)))
Ví dụ: =ENCODE("ABC123",3) → DEF456
Tóm tắt: Giải mã chuỗi đã được mã hóa bằng cách giảm mã ASCII của mỗi ký tự đi length.
Hàm: =LAMBDA(text,length,TEXTJOIN("",TRUE,CHAR(CODE(MID(text,ROW(INDIRECT("1:"&LEN(text))),1))-length)))
Ví dụ: =DECODE("DEF456",3) → ABC123
Tóm tắt: Tạo chuỗi ngẫu nhiên có độ dài chỉ định theo chế độ ký tự (số, chữ thường hoặc kết hợp số và chữ).
Hàm: =LAMBDA(length,mode,LET(character_set,SWITCH(mode,0,"0123456789",1,"abcdefghijklmnopqrstuvwxyz",2,"0123456789abcdefghijklmnopqrstuvwxyz","0123456789abcdefghijklmnopqrstuvwxyz"),character_count,LEN(character_set),TEXTJOIN("",TRUE,MID(character_set,RANDARRAY(length,1,1,character_count,TRUE),1))))
Ví dụ: =RANDTEXT(8,2) → Trả về một chuỗi ngẫu nhiên gồm 8 ký tự, ví dụ: 7k3m2qax.
Tóm tắt: Tạo danh sách mã tháng theo định dạng TYYMM trong khoảng từ số tháng trước đến số tháng sau so với một ngày tham chiếu.
Hàm: =LAMBDA(reference_date,previous_months,next_months,LET(start_month,EDATE(reference_date,-previous_months),total_months,previous_months+next_months+1,month_list,EDATE(start_month,SEQUENCE(total_months,,0)),"T"&TEXT(month_list,"YYMM")))
Ví dụ: =MONTHLIST(DATE(2026,7,8),2,2) → Trả về {"T2605";"T2606";"T2607";"T2608";"T2609"}.
Tóm tắt: Chuyển một số thành chữ tiếng Việt, hỗ trợ số âm và giá trị đến dưới 1 triệu tỷ.
Hàm: =LAMBDA(number,LET(text_number,RIGHT(TEXT(number,SUBSTITUTE("0\*0\=0\/0\*0;0\*0\=0\/0\*0",0,"0\-0\+0")),LEN(ROUND(ABS(number),0))*2-1),old_text,{"0-0+0*";"0-0+0/";"0-0+0";"0+0";"0+";"+0";"+5";"1+";"+1";"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";"+";"-";"*";"/";",=";"=";", "},new_text,{"";"";"";"";" lẻ";"+";"+ lăm";" mười";"+ mốt";" không";" một";" hai";" ba";" bốn";" năm";" sáu";" bảy";" tám";" chín";" mươi";" trăm";" ngàn,";" triệu,";"=";" tỷ,";""},result,REDUCE(text_number&" ",SEQUENCE(ROWS(old_text)),LAMBDA(acc,i,SUBSTITUTE(acc,INDEX(old_text,i),INDEX(new_text,i)))),IF(ABS(number)>=10^15,"###",IF(number<0,"Âm ","")&MID("KMHBBNSBTC",LEFT(ROUND(number,0))+1,1)&MID(TRIM(result),2-(number<0),999))))
Ví dụ: =DOCSO(123456789) → Một trăm hai mươi ba triệu bốn trăm năm mươi sáu ngàn bảy trăm tám mươi chín.
Tóm tắt: Chuyển một số thành chữ tiếng Anh, hỗ trợ số âm và giá trị đến hàng octillion.
Hàm: =LAMBDA(number,LET(sign_index,SIGN(number)+2,text_number,"00"&IF(ISTEXT(number),MID(number,1+(sign_index=1),99),TEXT(number,"0;0")),text_length,LEN(text_number),group_count,INT(text_length/3),result,SUBSTITUTE(TEXTJOIN(",",1,MAP(SEQUENCE(group_count,1,group_count,-1),LAMBDA(group,LET(part,LEFT(text_number,text_length-group*3+3),ones,{"zero";"one";"two";"three";"four";"five";"six";"seven";"eight";"nine";"ten";"eleven";"twelve";"thirteen";"fourteen";"fifteen";"sixteen";"seventeen";"eighteen";"nineteen"},tens,{"";"twenty";"thirty";"forty";"fifty";"sixty";"seventy";"eighty";"ninety"},value,RIGHT(part,3)*1,last_two,MOD(value,100),last_one,MOD(last_two,10),IF(value>0,IF(LEN(part)>4,IF(value<100,IF(group=1," and","")," "&INDEX(ones,INT(value/100)+1)&" hundred"&IF(last_two>0," and","")),"")&IF(last_two>=20," "&INDEX(tens,INT(last_two/10))&IF(last_one>0,"-"&INDEX(ones,last_one+1),""),IF(last_two>0," "&INDEX(ones,last_two+1),""))&CHOOSE(group,""," thousand"," million"," billion"," trillion"," quadrillion"," quintillion"," sextillion"," septillion"," octillion"),""))))),", and"," and"),CHOOSE(sign_index,"Negative"&result,"Zero",UPPER(MID(result,2,1))&MID(result,3,999))))
Ví dụ: =READNUM(1234567) → One million, two hundred and thirty-four thousand, five hundred and sixty-seven.
Tóm tắt: Chuyển một ngày thành chuỗi tiếng Việt đầy đủ gồm thứ, ngày, tháng và năm.
Hàm: =LAMBDA(date, "Thứ "&CHOOSE(WEEKDAY(date,2),"hai","ba","tư","năm","sáu","bảy","chủ nhật")&", ngày "&DAY(date)&" tháng "&MONTH(date)&" năm "&YEAR(date))
=DATETOTEXT(DATE(2026,7,8)) → Thứ tư, ngày 8 tháng 7 năm 2026.Tóm tắt: Chuyển một ngày thành chuỗi tiếng Anh đầy đủ gồm thứ, ngày (kèm hậu tố), tháng và năm.
Hàm: =LAMBDA(date,TEXT(date,"dddd")&", the "&DAY(date)&IF(OR(DAY(date)=11,DAY(date)=12,DAY(date)=13),"ᵗʰ",CHOOSE(MIN(4,MOD(DAY(date),10)+1),"ᵗʰ","ˢᵗ","ⁿᵈ","ʳᵈ"))&" day of "&TEXT(date,"mmmm yyyy"))
Ví dụ: =DATE_EN(DATE(2026,7,8)) → Wednesday, the 8ᵗʰ day of July 2026.
Tóm tắt: Xóa hàng loạt chuỗi ký tự khỏi văn bản theo danh sách phân tách bằng dấu chấm phẩy (;).
Hàm: =LAMBDA(text,remove_texts,REDUCE(text,TOCOL(TEXTSPLIT(remove_texts,";")),LAMBDA(result,remove_text,SUBSTITUTE(result,remove_text,""))))
Ví dụ: =REMOVETEXTS("Hà Nội, Việt Nam","Hà ;Việt ;,") → Nội Nam
Tóm tắt: Đếm số lần xuất hiện của một chuỗi ký tự trong văn bản.
Hàm: =LAMBDA(text,find_text,(LEN(text)-LEN(SUBSTITUTE(text,find_text,"")))/LEN(find_text))
Ví dụ: =COUNTTEXT("ABCABCAB","AB") → 3
Tóm tắt: Trích xuất chuỗi nằm giữa hai chuỗi ký tự xác định.
Hàm: =LAMBDA(text,start_text,end_text,TEXTBEFORE(TEXTAFTER(text,start_text),end_text))
Ví dụ: =TEXTBETWEEN("Mã[ABC123]Kết thúc","[","]") → ABC123
Tóm tắt: Đảo ngược thứ tự các ký tự trong một chuỗi văn bản.
Hàm: =LAMBDA(text,TEXTJOIN("",,MID(text,SEQUENCE(LEN(text),,LEN(text),-1),1)))
Ví dụ: =REVERSE("Excel") → lecxE
Tóm tắt: Loại bỏ các giá trị trùng lặp trong chuỗi phân tách bằng ký tự chỉ định.
Hàm: =LAMBDA(text,[delimiter],TEXTJOIN(IF(delimiter="",";",delimiter),TRUE,UNIQUE(TRANSPOSE(TRIM(TEXTSPLIT(text,IF(delimiter="",";",delimiter)))))))
Ví dụ: =UNIQUETEXT("A;B;A;C;B",";") → A;B;C
Tóm tắt: Trích xuất tất cả các chữ số trong chuỗi và ghép chúng thành một số.
Hàm: =LAMBDA(text,LET(characters,MID(text,SEQUENCE(LEN(text)),1),TEXTJOIN("",,FILTER(characters,ISNUMBER(--characters),""))))
Ví dụ: =DIGITS("NV-2026-001") → 2026001
Tóm tắt: Trả về danh sách số thứ tự của các phần tử có điều kiện đúng (TRUE).
Hàm: =LAMBDA(criteria,FILTER(SEQUENCE(ROWS(criteria)),criteria))
Ví dụ: =WHEREROW(A2:A6>100) → Nếu A2:A6={50;120;80;150;200} thì trả về {2;4;5}.
Tóm tắt: Tra cứu nhiều giá trị phân tách bằng dấu chấm phẩy và ghép các kết quả trả về thành một chuỗi.
Hàm: =LAMBDA(list_text,lookup_array,return_array,[if_error],TEXTJOIN(";",TRUE,IF(if_error="",XLOOKUP(TRANSPOSE(TRIM(TEXTSPLIT(list_text,";"))),lookup_array,return_array),XLOOKUP(TRANSPOSE(TRIM(TEXTSPLIT(list_text,";"))),lookup_array,return_array,if_error))))
Ví dụ: =XLOOKUPLIST("A01;A03",A2:A10,B2:B10,"") → Nếu A01→Táo và A03→Cam thì kết quả là Táo; Cam.
Tóm tắt: Trả về tất cả giá trị tương ứng với khóa tra cứu trong mảng dữ liệu.
Hàm: =LAMBDA(lookup_value,lookup_array,return_array,[if_error],IF(ISOMITTED(if_error),FILTER(return_array,lookup_array=lookup_value),IFERROR(FILTER(return_array,lookup_array=lookup_value),if_error)))
Ví dụ: =XLOOKUPALL("A01",A2:A10,B2:B10) → Trả về tất cả giá trị trong B2:B10 có mã "A01" trong A2:A10.
Tóm tắt: Trả về giá trị khớp thứ n với khóa tra cứu trong mảng dữ liệu.
Hàm: =LAMBDA(lookup_value,occurrence,lookup_array,return_array,[if_error],IF(ISOMITTED(if_error),INDEX(FILTER(return_array,lookup_array=lookup_value),occurrence),IFERROR(INDEX(FILTER(return_array,lookup_array=lookup_value),occurrence),if_error)))
Ví dụ: =XLOOKUPN("A01",2,A2:A10,B2:B10) → Trả về giá trị khớp lần thứ 2 trong B2:B10 có mã "A01" trong A2:A10.
Tóm tắt: Trả về tất cả các hàng dữ liệu có giá trị tra cứu khớp với khóa chỉ định.
Hàm: =LAMBDA(lookup_value,lookup_array,data_range,[if_error],IF(ISOMITTED(if_error),FILTER(data_range,lookup_array=lookup_value),IFERROR(FILTER(data_range,lookup_array=lookup_value),if_error)))
Ví dụ: =FILTERROWS("A01",A2:A10,B2:E10) → Trả về tất cả các hàng trong B2:E10 có mã "A01" trong A2:A10.
Tóm tắt: Trả về tất cả các hàng trong bảng có giá trị tra cứu khớp với khóa chỉ định.
Hàm: =LAMBDA(lookup_value,lookup_array,return_table,[if_error],IF(ISOMITTED(if_error),FILTER(return_table,lookup_array=lookup_value),IFERROR(FILTER(return_table,lookup_array=lookup_value),if_error)))
Ví dụ: =FILTERTABLE("A01",A2:A10,B2:E10) → Trả về tất cả các hàng trong B2:E10 có mã "A01" trong A2:A10.
Tóm tắt: Chuyển một hàng tiêu đề và một hàng dữ liệu thành bảng gồm hai cột Field và Value.
Hàm: =LAMBDA(headers,data,VSTACK({"Field","Value"},HSTACK(TOCOL(headers),TOCOL(data))))
Ví dụ: =UNPIVOTROW(A1:D1,A2:D2) → Với A1:D1={"ID","Name","Age","City"} và A2:D2={1,"An",25,"Hà Nội"} trả về bảng gồm hai cột Field và Value.
Tóm tắt: Thực hiện các phép toán tập hợp giữa hai danh sách (hiệu, giao, hiệu đối xứng, hợp) và có thể trả kết quả dưới dạng mảng hoặc chuỗi.
Hàm: =LAMBDA(array1,array2,operation,LET(list1,UNIQUE(FILTER(array1,array1<>"")),list2,UNIQUE(FILTER(array2,array2<>"")),result,SWITCH(ABS(operation),1,FILTER(list1,ISNA(XMATCH(list1,list2))),2,FILTER(list2,ISNA(XMATCH(list2,list1))),3,FILTER(list1,ISNUMBER(XMATCH(list1,list2))),4,VSTACK(FILTER(list1,ISNA(XMATCH(list1,list2))),FILTER(list2,ISNA(XMATCH(list2,list1)))),5,UNIQUE(VSTACK(list1,list2)),NA()),IF(operation<0,TEXTJOIN(", ",,result),result)))
Ví dụ: =SETOP(A2:A6,B2:B6,3) → Trả về các giá trị chung (giao) giữa hai danh sách; dùng -3 sẽ trả về cùng kết quả nhưng được ghép thành chuỗi phân tách bằng ", ".
Operation:.
1 Giá trị chỉ có trong Array1 (A − B)
2 Giá trị chỉ có trong Array2 (B − A)
3 Giá trị có trong cả hai mảng (A ∩ B)
4 Giá trị chỉ xuất hiện ở một trong hai mảng (Symmetric Difference)
5 Tất cả giá trị của hai mảng (A ∪ B)
Quy ước:
Operation > 0 → trả về mảng.
Operation < 0 → trả về chuỗi (TEXTJOIN).
Tóm tắt: Lọc dữ liệu theo tối đa 5 điều kiện, hỗ trợ ký tự đại diện (*, ?) và giá trị mặc định khi không có kết quả.
Hàm: =LAMBDA(array,filter_array1,criteria1,[if_empty],[filter_array2],[criteria2],[filter_array3],[criteria3],[filter_array4],[criteria4],[filter_array5],[criteria5],LET(match_wild,LAMBDA(filter_range,criteria,MAP(filter_range,LAMBDA(value,--(SUM(COUNTIF(value,criteria))>0)))),include1,match_wild(filter_array1,criteria1),include2,IF(ISOMITTED(filter_array2),1,match_wild(filter_array2,criteria2)),include3,IF(ISOMITTED(filter_array3),1,match_wild(filter_array3,criteria3)),include4,IF(ISOMITTED(filter_array4),1,match_wild(filter_array4,criteria4)),include5,IF(ISOMITTED(filter_array5),1,match_wild(filter_array5,criteria5)),FILTER(array,include1*include2*include3*include4*include5,IF(ISOMITTED(if_empty),"",if_empty))))
Ví dụ: =FILTERLIKE(A2:D100,B2:B100,{"A*";"B*"},"Không có",C2:C100,"*HN*") → Trả về các hàng trong A2:D100 có cột B bắt đầu bằng A hoặc B và cột C chứa HN; nếu không có kết quả thì trả về "Không có".
Tóm tắt: Lọc dữ liệu theo tối đa 10 điều kiện, hỗ trợ ký tự đại diện (*, ?) và giá trị mặc định khi không có kết quả.
Hàm: =LAMBDA(array,filter_array1,criteria1,[if_empty],[filter_array2],[criteria2],[filter_array3],[criteria3],[filter_array4],[criteria4],[filter_array5],[criteria5],[filter_array6],[criteria6],[filter_array7],[criteria7],[filter_array8],[criteria8],[filter_array9],[criteria9],[filter_array10],[criteria10],LET(match_wild,LAMBDA(filter_range,criteria,MAP(filter_range,LAMBDA(value,--(SUM(COUNTIF(value,criteria))>0)))),include1,match_wild(filter_array1,criteria1),include2,IF(ISOMITTED(filter_array2),1,match_wild(filter_array2,criteria2)),include3,IF(ISOMITTED(filter_array3),1,match_wild(filter_array3,criteria3)),include4,IF(ISOMITTED(filter_array4),1,match_wild(filter_array4,criteria4)),include5,IF(ISOMITTED(filter_array5),1,match_wild(filter_array5,criteria5)),include6,IF(ISOMITTED(filter_array6),1,match_wild(filter_array6,criteria6)),include7,IF(ISOMITTED(filter_array7),1,match_wild(filter_array7,criteria7)),include8,IF(ISOMITTED(filter_array8),1,match_wild(filter_array8,criteria8)),include9,IF(ISOMITTED(filter_array9),1,match_wild(filter_array9,criteria9)),include10,IF(ISOMITTED(filter_array10),1,match_wild(filter_array10,criteria10)),FILTER(array,include1*include2*include3*include4*include5*include6*include7*include8*include9*include10,IF(ISOMITTED(if_empty),"",if_empty))))
Ví dụ: =FILTERLIKE(A2:F100,B2:B100,"A*",,C2:C100,"*HN*",D2:D100,{"Active";"Pending"}) → Trả về các hàng trong A2:F100 có cột B bắt đầu bằng A, cột C chứa HN và cột D là Active hoặc Pending.
=LAMBDA(array,by_array1,criteria1,[if_empty],[by_array2],[criteria2],[by_array3],[criteria3],[by_array4],[criteria4],[by_array5],[criteria5],LET(MatchWild,
LAMBDA(rng,crit,MAP(rng,LAMBDA(x,--(SUM(COUNTIF(x,crit))>0)))),Include1,MatchWild(by_array1,criteria1),Include2,IF(ISOMITTED(by_array2),1,MatchWild(by_array2,criteria2)),Include3,IF(ISOMITTED(by_array3),1,MatchWild(by_array3,criteria3)),Include4,IF(ISOMITTED(by_array4),1,MatchWild(by_array4,criteria4)),Include5,IF(ISOMITTED(by_array5),1,MatchWild(by_array5,criteria5)),FILTER(array,Include1*Include2*Include3*Include4*Include5,IF(ISOMITTED(if_empty),"",if_empty))))
=LAMBDA(array,by_array1,criteria1,[if_empty],[by_array2],[criteria2],[by_array3],[criteria3],[by_array4],[criteria4],[by_array5],[criteria5],[by_array6],[criteria6],[by_array7],[criteria7],[by_array8],[criteria8],[by_array9],[criteria9],[by_array10],[criteria10],LET(MatchWild,LAMBDA(rng,crit,MAP(rng, LAMBDA(x,--(SUM(COUNTIF(x,crit))>0)))),Include1,MatchWild(by_array1,criteria1),Include2,IF(ISOMITTED(by_array2),1,MatchWild(by_array2,criteria2)),Include3,IF(ISOMITTED(by_array3),1,MatchWild(by_array3,criteria3)),Include4,IF(ISOMITTED(by_array4),1,MatchWild(by_array4,criteria4)),Include5,IF(ISOMITTED(by_array5),1,MatchWild(by_array5,criteria5)),Include6,IF(ISOMITTED(by_array6),1,MatchWild(by_array6,criteria6)),Include7,IF(ISOMITTED(by_array7),1,MatchWild(by_array7,criteria7)),Include8,IF(ISOMITTED(by_array8),1,MatchWild(by_array8,criteria8)),Include9,IF(ISOMITTED(by_array9),1,MatchWild(by_array9,criteria9)),Include10,IF(ISOMITTED(by_array10),1,MatchWild(by_array10,criteria10)),FILTER(array,Include1*Include2*Include3*Include4*Include5*Include6*Include7*Include8*Include9*Include10,IF(ISOMITTED(if_empty),"",if_empty))))
| Tham số | Bắt buộc | Mô tả |
|---|---|---|
| array | Có | Vùng dữ liệu cần lọc |
| by_array1 | Có | Vùng điều kiện thứ nhất |
| criteria1 | Có | Điều kiện lọc thứ nhất |
| [if_empty] | Không | Giá trị trả về nếu không có kết quả |
| [by_array2,3,4,5] | Không | Vùng điều kiện thứ hai, ba, bốn, năm |
| [criteria2,3,4,5] | Không | Điều kiện lọc thứ hai, ba, bốn, năm |
Tóm tắt: Tạo bảng tổng hợp (Pivot Table) từ dữ liệu theo các trường hàng, trường giá trị và hàm tổng hợp, hỗ trợ phân cấp tổng, sắp xếp, lọc và quan hệ trường.
Hàm: =LAMBDA(data,row_field_labels,value_field_labels,aggregate_function,[total_depth],[sort_order],[filter_array],[field_relationship],LET(headers,TAKE(data,1),body,DROP(data,1),row_ids,XMATCH(TOROW(row_field_labels),headers,0),value_ids,XMATCH(TOROW(value_field_labels),headers,0),row_fields,VSTACK(CHOOSECOLS(headers,row_ids),CHOOSECOLS(body,row_ids)),value_fields,VSTACK(CHOOSECOLS(headers,value_ids),CHOOSECOLS(body,value_ids)),IF(ISOMITTED(total_depth),GROUPBY(row_fields,value_fields,aggregate_function,3),IF(ISOMITTED(sort_order),GROUPBY(row_fields,value_fields,aggregate_function,3,total_depth),IF(ISOMITTED(filter_array),IF(ISOMITTED(field_relationship),GROUPBY(row_fields,value_fields,aggregate_function,3,total_depth,sort_order),GROUPBY(row_fields,value_fields,aggregate_function,3,total_depth,sort_order,,field_relationship)),IF(ISOMITTED(field_relationship),GROUPBY(row_fields,value_fields,aggregate_function,3,total_depth,sort_order,VSTACK(TRUE,filter_array)),GROUPBY(row_fields,value_fields,aggregate_function,3,total_depth,sort_order,VSTACK(TRUE,filter_array),field_relationship)))))))
Ví dụ: =PIVOTTABLE(A1:E100,{"Region";"Product"},{"Sales"},SUM) → Tạo bảng tổng hợp doanh số (Sales) theo Region và Product.
| Tham số | Ý nghĩa |
|---|---|
| data | Vùng dữ liệu bao gồm cả dòng tiêu đề |
| row_field_labels | Tên cột dùng để nhóm dòng |
| value_field_labels | Tên cột chứa giá trị cần tổng hợp |
| agg_fn | Hàm tổng hợp như SUM, COUNT, AVERAGE, MAX, MIN |
| [total_depth] | Tùy chọn hiển thị dòng tổng |
| [sort_order] | Tùy chọn sắp xếp |
| [filter_array] | Mảng điều kiện lọc |
| [field_relationship] | Cách xử lý quan hệ giữa nhiều trường nhóm |
Ví dụ dữ liệu:
| Region | Product | Year | Sales | Profit |
|---|---|---|---|---|
| North | A | 2024 | 100 | 20 |
| North | B | 2024 | 150 | 30 |
| South | A | 2024 | 200 | 50 |
Nhóm theo Region, tính tổng Sales: =GROUPBYS(A1:E4,"Region","Sales",SUM)
Kết quả:
| Region | Sum of Sales |
|---|---|
| North | 250 |
| South | 200 |
| Grand Total | 450 |
Nhóm theo nhiều trường: =GROUPBYS(A1:E4,{"Region","Product"},"Sales",SUM)
Tổng hợp nhiều cột giá trị: =GROUPBYS(A1:E4,"Region",{"Sales","Profit"},SUM)
Dùng nhiều phép tính cho cùng một cột: =GROUPBYS(A1:E4,"Region","Sales",HSTACK(SUM,AVERAGE,COUNT))
Lọc dữ liệu trước khi nhóm, ví dụ chỉ lấy năm 2024: =GROUPBYS(A1:E100,"Region","Sales",SUM,,,(C2:C100=2024))
Lưu ý: filter_array chỉ áp dụng cho phần dữ liệu, không bao gồm dòng tiêu đề. Vì vậy nếu data là A1:E100, điều kiện lọc nên là C2:C100=2024.
Nếu dùng Excel với dấu phân cách tham số là dấu chấm phẩy, hãy viết: =GROUPBYS(A1:E4;"Region";"Sales";SUM)
Tóm tắt: Tạo bảng Pivot từ dữ liệu theo trường hàng, trường cột và trường giá trị, hỗ trợ hàm tổng hợp, tổng phụ, sắp xếp, lọc và tính toán tương đối.
Hàm: =LAMBDA(data,row_field_labels,column_field_labels,value_field_labels,aggregate_function,[row_total_depth],[row_sort_order],[column_total_depth],[column_sort_order],[filter_array],[relative_to],LET(headers,TAKE(data,1),body,DROP(data,1),row_ids,XMATCH(TOROW(row_field_labels),headers,0),column_ids,XMATCH(TOROW(column_field_labels),headers,0),value_ids,XMATCH(TOROW(value_field_labels),headers,0),row_fields,VSTACK(CHOOSECOLS(headers,row_ids),CHOOSECOLS(body,row_ids)),column_fields,VSTACK(CHOOSECOLS(headers,column_ids),CHOOSECOLS(body,column_ids)),value_fields,VSTACK(CHOOSECOLS(headers,value_ids),CHOOSECOLS(body,value_ids)),IF(ISOMITTED(row_total_depth),PIVOTBY(row_fields,column_fields,value_fields,aggregate_function,3),IF(ISOMITTED(row_sort_order),PIVOTBY(row_fields,column_fields,value_fields,aggregate_function,3,row_total_depth),IF(ISOMITTED(column_total_depth),PIVOTBY(row_fields,column_fields,value_fields,aggregate_function,3,row_total_depth,row_sort_order),IF(ISOMITTED(column_sort_order),PIVOTBY(row_fields,column_fields,value_fields,aggregate_function,3,row_total_depth,row_sort_order,column_total_depth),IF(ISOMITTED(filter_array),IF(ISOMITTED(relative_to),PIVOTBY(row_fields,column_fields,value_fields,aggregate_function,3,row_total_depth,row_sort_order,column_total_depth,column_sort_order),PIVOTBY(row_fields,column_fields,value_fields,aggregate_function,3,row_total_depth,row_sort_order,column_total_depth,column_sort_order,,relative_to)),IF(ISOMITTED(relative_to),PIVOTBY(row_fields,column_fields,value_fields,aggregate_function,3,row_total_depth,row_sort_order,column_total_depth,column_sort_order,VSTACK(TRUE,filter_array)),PIVOTBY(row_fields,column_fields,value_fields,aggregate_function,3,row_total_depth,row_sort_order,column_total_depth,column_sort_order,VSTACK(TRUE,filter_array),relative_to)))))))))
Ví dụ: =PIVOTTABLE2(A1:E100,{"Region"},{"Year"},{"Sales"},SUM) → Tạo bảng Pivot tổng hợp Sales theo Region (hàng) và Year (cột).
Tóm tắt: Trả về cột dữ liệu theo tên tiêu đề, trong đó tiêu đề nằm ngay phía trên vùng dữ liệu.
Hàm: =LAMBDA(data,column_label,LET(headers,TAKE(OFFSET(data,-1,0),1),CHOOSECOLS(data,XMATCH(column_label,headers))))
Ví dụ: =GETCOLS(A2:E100,"Sales") → Trả về cột Sales từ vùng dữ liệu A2:E100 với tiêu đề nằm ở hàng A1:E1.
Tóm tắt: Sắp xếp một mảng theo khóa sắp xếp được tra cứu từ một bảng ánh xạ.
Hàm: =LAMBDA(array,lookup_array,sort_array,[sort_order],SORTBY(array,XLOOKUP(array,lookup_array,sort_array),IF(ISOMITTED(sort_order),1,sort_order)))
Ví dụ: =_SORTBY(A2:A10,E2:E5,F2:F5,-1) → Sắp xếp các giá trị trong A2:A10 theo thứ tự khóa được ánh xạ từ E2:E5 → F2:F5 theo thứ tự giảm dần.
Tóm tắt: Sắp xếp một mảng theo tối đa 3 khóa sắp xếp được tra cứu từ các bảng ánh xạ.
Hàm: =LAMBDA(array,lookup_array1,sort_array1,[sort_order1],[lookup_array2],[sort_array2],[sort_order2],[lookup_array3],[sort_array3],[sort_order3],SORTBY(array,XLOOKUP(array,lookup_array1,sort_array1),IF(ISOMITTED(sort_order1),1,sort_order1),IF(ISOMITTED(lookup_array2),SEQUENCE(ROWS(array))*0+1,XLOOKUP(array,lookup_array2,sort_array2)),IF(ISOMITTED(sort_order2),1,sort_order2),IF(ISOMITTED(lookup_array3),SEQUENCE(ROWS(array))*0+1,XLOOKUP(array,lookup_array3,sort_array3)),IF(ISOMITTED(sort_order3),1,sort_order3)))
Ví dụ: =SORTMAP(A2:A10,E2:E5,F2:F5,1,G2:G5,H2:H5,-1) → Sắp xếp A2:A10 theo khóa ánh xạ E:F (tăng dần), sau đó theo khóa ánh xạ G:H (giảm dần).
Tóm tắt: Trả về các cột được chỉ định từ bảng dữ liệu sau khi loại bỏ các hàng trùng lặp.
Hàm: =LAMBDA(array,return_columns,LET(column_indices,BYCOL(return_columns,LAMBDA(target_column,XMATCH(TRUE,BYCOL(array,LAMBDA(column,AND(column=target_column)))))),CHOOSECOLS(UNIQUE(array),column_indices)))
Ví dụ: =UNIQUECOLS(A1:E100,H1:J100) → Trả về các cột tương ứng với H1:J100 từ bảng A1:E100 sau khi loại bỏ các hàng trùng lặp.
Chọn nhiều cột RỜI RẠC (Dùng thêm hàm HSTACK) Bạn chỉ cần dùng hàm HSTACK để gộp các cột muốn lấy lại thành 1 mảng
Tóm tắt: Tra cứu giá trị theo khóa kết hợp với điều kiện động và trả về kết quả khớp đầu tiên.
Hàm: =LAMBDA(lookup_value,condition_value,lookup_range,condition_range,return_range,LET(matches,FILTER(HSTACK(condition_range,return_range),lookup_range=lookup_value),IFERROR(INDEX(FILTER(CHOOSECOLS(matches,2),MAP(CHOOSECOLS(matches,1),LAMBDA(condition,IF(condition="",TRUE,VALUATE(condition_value&condition))))),1),"")))
Ví dụ: =XLOOKUPIF("A01",100,A2:A10,C2:C10,B2:B10) → Trả về giá trị đầu tiên trong B2:B10 có mã "A01" và điều kiện trong C2:C10 thỏa mãn 100 kết hợp với biểu thức điều kiện (ví dụ: ">=50" hoặc "<200").
Tóm tắt: Tính tổng có trọng số cho từng cột của một mảng dữ liệu có tính năng tràn
Hàm: =LAMBDA(weights,data_array,BYCOL(data_array,LAMBDA(column,SUM(weights*column))))
Ví dụ: =_SUMPRODUCT(A2:A5,B2:D5) → Trả về tổng có trọng số của từng cột trong B2:D5 với trọng số tương ứng ở A2:A5.
Phiên bản 2 cặp tham số có phân luồng chạy nhanh:
Phiên bản 5 cặp tham số có phân luồng chạy nhanh:
Tóm tắt: Tính tổng có trọng số theo tối đa 5 điều kiện, hỗ trợ nhiều tiêu chí (;), khoảng giá trị (a:b) và hệ số cộng/trừ (-).
Hàm: =LAMBDA(s_r,r_1,c_1,[r_2],[c_2],[r_3],[c_3],[r_4],[c_4],[r_5],[c_5],LET(n,1+IF(ISOMITTED(r_2),0,1)+IF(ISOMITTED(r_3),0,1)+IF(ISOMITTED(r_4),0,1)+IF(ISOMITTED(r_5),0,1),_c2,IF(ISOMITTED(c_2),"",c_2),_c3,IF(ISOMITTED(c_3),"",c_3),_c4,IF(ISOMITTED(c_4),"",c_4),_c5,IF(ISOMITTED(c_5),"",c_5),h_r,ROWS(c_1&_c2&_c3&_c4&_c5),MAP(SEQUENCE(h_r),LAMBDA(_ri,LET(_g,LAMBDA(x,IF(ROWS(x)=1,x,INDEX(x,_ri))),c_a,SWITCH(n,1,HSTACK(_g(c_1)),2,HSTACK(_g(c_1),_g(_c2)),3,HSTACK(_g(c_1),_g(_c2),_g(_c3)),4,HSTACK(_g(c_1),_g(_c2),_g(_c3),_g(_c4)),5,HSTACK(_g(c_1),_g(_c2),_g(_c3),_g(_c4),_g(_c5))),c_m,REDUCE(1,c_a,LAMBDA(_m,_cr,LET(r_g,ROWS(_m),c_g,COLUMNS(_m),n_i,TEXTSPLIT(_cr,";"),k_v,COLUMNS(n_i),MAKEARRAY(r_g*k_v,c_g+2,LAMBDA(_r,_c,LET(o_r,INT((_r-1)/k_v)+1,n_x,MOD(_r-1,k_v)+1,IF(_c=1,INDEX(_m,o_r,1)*IF(LEFT(TRIM(INDEX(n_i,1,n_x)),1)="-",-1,1),IF(_c<=c_g,INDEX(_m,o_r,_c),LET(t,TRIM(INDEX(n_i,1,n_x)),in,LEFT(t,1)="-",_t,IF(in,TRIM(MID(t,2,255)),t),hc,ISNUMBER(SEARCH(":",_t)),IF(_c=c_g+1,IF(hc,">="&TEXTBEFORE(_t,":"),_t),IF(hc,"<="&TEXTAFTER(_t,":"),_t))))))))))),SUM(MAP(SEQUENCE(ROWS(c_m)),LAMBDA(_i,INDEX(c_m,_i,1)*SWITCH(n,1,SUMIFS(s_r,r_1,INDEX(c_m,_i,2),r_1,INDEX(c_m,_i,3)),2,SUMIFS(s_r,r_1,INDEX(c_m,_i,2),r_1,INDEX(c_m,_i,3),r_2,INDEX(c_m,_i,4),r_2,INDEX(c_m,_i,5)),3,SUMIFS(s_r,r_1,INDEX(c_m,_i,2),r_1,INDEX(c_m,_i,3),r_2,INDEX(c_m,_i,4),r_2,INDEX(c_m,_i,5),r_3,INDEX(c_m,_i,6),r_3,INDEX(c_m,_i,7)),4,SUMIFS(s_r,r_1,INDEX(c_m,_i,2),r_1,INDEX(c_m,_i,3),r_2,INDEX(c_m,_i,4),r_2,INDEX(c_m,_i,5),r_3,INDEX(c_m,_i,6),r_3,INDEX(c_m,_i,7),r_4,INDEX(c_m,_i,8),r_4,INDEX(c_m,_i,9)),5,SUMIFS(s_r,r_1,INDEX(c_m,_i,2),r_1,INDEX(c_m,_i,3),r_2,INDEX(c_m,_i,4),r_2,INDEX(c_m,_i,5),r_3,INDEX(c_m,_i,6),r_3,INDEX(c_m,_i,7),r_4,INDEX(c_m,_i,8),r_4,INDEX(c_m,_i,9),r_5,INDEX(c_m,_i,10),r_5,INDEX(c_m,_i,11)))))))))))
Ví dụ: =SUMIFSPLUS(C2:C100,A2:A100,"A;B;-C",B2:B100,"10:20",D2:D100,"HN;HCM") → Tính A + B − C với điều kiện cột B từ 10 đến 20 và cột D là HN hoặc HCM.
Phiên bản 7 cặp tham số có phân luồng và Đóng gói cụm INDEX(c_m, _i, ...) lặp đi lặp lại thành một hàm Lambda nội bộ tên là X(c).chạy nhanh & Spillable
Tóm tắt: Tính tổng có trọng số theo tối đa 7 điều kiện, hỗ trợ nhiều tiêu chí (;), khoảng giá trị (a:b), hệ số cộng/trừ (-) và xử lý mảng kết quả hai chiều.
Hàm: =LAMBDA(s_r,r_1,c_1,[r_2],[c_2],[r_3],[c_3],[r_4],[c_4],[r_5],[c_5],[r_6],[c_6],[r_7],[c_7],LET(n,1+IF(ISOMITTED(r_2),0,1)+IF(ISOMITTED(r_3),0,1)+IF(ISOMITTED(r_4),0,1)+IF(ISOMITTED(r_5),0,1)+IF(ISOMITTED(r_6),0,1)+IF(ISOMITTED(r_7),0,1),_c2,IF(ISOMITTED(c_2),"",c_2),_c3,IF(ISOMITTED(c_3),"",c_3),_c4,IF(ISOMITTED(c_4),"",c_4),_c5,IF(ISOMITTED(c_5),"",c_5),_c6,IF(ISOMITTED(c_6),"",c_6),_c7,IF(ISOMITTED(c_7),"",c_7),b_m,c_1&_c2&_c3&_c4&_c5&_c6&_c7,MAKEARRAY(ROWS(b_m),COLUMNS(b_m),LAMBDA(_ri,_ci,LET(_g,LAMBDA(x,INDEX(x,IF(ROWS(x)=1,1,_ri),IF(COLUMNS(x)=1,1,_ci))),c_a,SWITCH(n,1,HSTACK(_g(c_1)),2,HSTACK(_g(c_1),_g(_c2)),3,HSTACK(_g(c_1),_g(_c2),_g(_c3)),4,HSTACK(_g(c_1),_g(_c2),_g(_c3),_g(_c4)),5,HSTACK(_g(c_1),_g(_c2),_g(_c3),_g(_c4),_g(_c5)),6,HSTACK(_g(c_1),_g(_c2),_g(_c3),_g(_c4),_g(_c5),_g(_c6)),7,HSTACK(_g(c_1),_g(_c2),_g(_c3),_g(_c4),_g(_c5),_g(_c6),_g(_c7))),c_m,REDUCE(1,c_a,LAMBDA(_m,_cr,LET(r_g,ROWS(_m),c_g,COLUMNS(_m),n_i,TEXTSPLIT(_cr,";"),k_v,COLUMNS(n_i),MAKEARRAY(r_g*k_v,c_g+2,LAMBDA(_r,_c,LET(o_r,INT((_r-1)/k_v)+1,n_x,MOD(_r-1,k_v)+1,IF(_c=1,INDEX(_m,o_r,1)*IF(LEFT(TRIM(INDEX(n_i,1,n_x)),1)="-",-1,1),IF(_c<=c_g,INDEX(_m,o_r,_c),LET(t,TRIM(INDEX(n_i,1,n_x)),in,LEFT(t,1)="-",_t,IF(in,TRIM(MID(t,2,255)),t),hc,ISNUMBER(SEARCH(":",_t)),IF(_c=c_g+1,IF(hc,">="&TEXTBEFORE(_t,":"),_t),IF(hc,"<="&TEXTAFTER(_t,":"),_t))))))))))),SUM(MAP(SEQUENCE(ROWS(c_m)),LAMBDA(_i,LET(X,LAMBDA(c,INDEX(c_m,_i,c)),X(1)*SWITCH(n,1,SUMIFS(s_r,r_1,X(2),r_1,X(3)),2,SUMIFS(s_r,r_1,X(2),r_1,X(3),r_2,X(4),r_2,X(5)),3,SUMIFS(s_r,r_1,X(2),r_1,X(3),r_2,X(4),r_2,X(5),r_3,X(6),r_3,X(7)),4,SUMIFS(s_r,r_1,X(2),r_1,X(3),r_2,X(4),r_2,X(5),r_3,X(6),r_3,X(7),r_4,X(8),r_4,X(9)),5,SUMIFS(s_r,r_1,X(2),r_1,X(3),r_2,X(4),r_2,X(5),r_3,X(6),r_3,X(7),r_4,X(8),r_4,X(9),r_5,X(10),r_5,X(11)),6,SUMIFS(s_r,r_1,X(2),r_1,X(3),r_2,X(4),r_2,X(5),r_3,X(6),r_3,X(7),r_4,X(8),r_4,X(9),r_5,X(10),r_5,X(11),r_6,X(12),r_6,X(13)),7,SUMIFS(s_r,r_1,X(2),r_1,X(3),r_2,X(4),r_2,X(5),r_3,X(6),r_3,X(7),r_4,X(8),r_4,X(9),r_5,X(10),r_5,X(11),r_6,X(12),r_6,X(13),r_7,X(14),r_7,X(15))))))))))))
=LAMBDA(sr,r¹,c¹,[r²],[c²],[r³],[c³],[r⁴],[c⁴],[r⁵],[c⁵],[r⁶],[c⁶],[r⁷],[c⁷],LET(n,1+IF(ISOMITTED(r²),0,1)+IF(ISOMITTED(r³),0,1)+IF(ISOMITTED(r⁴),0,1)+IF(ISOMITTED(r⁵),0,1)+IF(ISOMITTED(r⁶),0,1)+IF(ISOMITTED(r⁷),0,1),_c2,IF(ISOMITTED(c²),"",c²),_c3,IF(ISOMITTED(c³),"",c³),_c4,IF(ISOMITTED(c⁴),"",c⁴),_c5,IF(ISOMITTED(c⁵),"",c⁵),_c6,IF(ISOMITTED(c⁶),"",c⁶),_c7,IF(ISOMITTED(c⁷),"",c⁷),b_m,c¹&_c2&_c3&_c4&_c5&_c6&_c7,MAKEARRAY(ROWS(b_m),COLUMNS(b_m),LAMBDA(_ri,_ci,LET(_g,LAMBDA(x,INDEX(x,IF(ROWS(x)=1,1,_ri),IF(COLUMNS(x)=1,1,_ci))),c_a,SWITCH(n,1,HSTACK(_g(c¹)),2,HSTACK(_g(c¹),_g(_c2)),3,HSTACK(_g(c¹),_g(_c2),_g(_c3)),4,HSTACK(_g(c¹),_g(_c2),_g(_c3),_g(_c4)),5,HSTACK(_g(c¹),_g(_c2),_g(_c3),_g(_c4),_g(_c5)),6,HSTACK(_g(c¹),_g(_c2),_g(_c3),_g(_c4),_g(_c5),_g(_c6)),7,HSTACK(_g(c¹),_g(_c2),_g(_c3),_g(_c4),_g(_c5),_g(_c6),_g(_c7))),c_m,REDUCE(1,c_a,LAMBDA(_m,_cr,LET(r_g,ROWS(_m),c_g,COLUMNS(_m),n_i,TEXTSPLIT(_cr,";"),k_v,COLUMNS(n_i),MAKEARRAY(r_g*k_v,c_g+2,LAMBDA(_r,_c,LET(o_r,INT((_r-1)/k_v)+1,n_x,MOD(_r-1,k_v)+1,IF(_c=1,INDEX(_m,o_r,1)*IF(LEFT(TRIM(INDEX(n_i,1,n_x)),1)="-",-1,1),IF(_c<=c_g,INDEX(_m,o_r,_c),LET(t,TRIM(INDEX(n_i,1,n_x)),in,LEFT(t,1)="-",_t,IF(in,TRIM(MID(t,2,255)),t),hc,ISNUMBER(SEARCH(":",_t)),IF(_c=c_g+1,IF(hc,">="&TEXTBEFORE(_t,":"),_t),IF(hc,"<="&TEXTAFTER(_t,":"),_t))))))))))),SUM(MAP(SEQUENCE(ROWS(c_m)),LAMBDA(_i,LET(X,LAMBDA(c,INDEX(c_m,_i,c)),X(1)*SWITCH(n,1,SUMIFS(sr,r¹,X(2),r¹,X(3)),2,SUMIFS(sr,r¹,X(2),r¹,X(3),r²,X(4),r²,X(5)),3,SUMIFS(sr,r¹,X(2),r¹,X(3),r²,X(4),r²,X(5),r³,X(6),r³,X(7)),4,SUMIFS(sr,r¹,X(2),r¹,X(3),r²,X(4),r²,X(5),r³,X(6),r³,X(7),r⁴,X(8),r⁴,X(9)),5,SUMIFS(sr,r¹,X(2),r¹,X(3),r²,X(4),r²,X(5),r³,X(6),r³,X(7),r⁴,X(8),r⁴,X(9),r⁵,X(10),r⁵,X(11)),6,SUMIFS(sr,r¹,X(2),r¹,X(3),r²,X(4),r²,X(5),r³,X(6),r³,X(7),r⁴,X(8),r⁴,X(9),r⁵,X(10),r⁵,X(11),r⁶,X(12),r⁶,X(13)),7,SUMIFS(sr,r¹,X(2),r¹,X(3),r²,X(4),r²,X(5),r³,X(6),r³,X(7),r⁴,X(8),r⁴,X(9),r⁵,X(10),r⁵,X(11),r⁶,X(12),r⁶,X(13),r⁷,X(14),r⁷,X(15))))))))))))
Ví dụ: =SUMIFSPLUS(C2:C100,A2:A10,"A;B",-B2:B10,"10:20",D2:D10,"HN;HCM",E2:E10,"2026") → Tính A + B theo nhiều điều kiện, hỗ trợ danh sách tiêu chí, khoảng giá trị và trả về kết quả dạng mảng theo các tiêu chí đầu vào.
Tóm tắt: Trả về giá trị tại giao điểm của một hàng và một cột trong bảng dữ liệu theo tên nhãn.
Hàm: =LAMBDA(data_table,row_label,column_label,INDEX(data_table,XMATCH(row_label,TAKE(data_table,,1),0),XMATCH(column_label,TAKE(data_table,1),0)))
Ví dụ: =TABLELOOKUP(A1:F10,"A01","Sales") → Trả về giá trị tại giao điểm giữa hàng có nhãn "A01" và cột "Sales" trong bảng A1:F10.
=LAMBDA(txt,TEXTJOIN("",,MAP(SEQUENCE(LEN(txt)),LAMBDA(i,IFERROR(MID("⁰¹²³⁴⁵⁶⁷⁸⁹abcdefghijklmnopqrstuvwxyz",FIND(MID(txt,i,1),"0123456789abcdefghijklmnopqrstuvwxyz"),1),MID(txt,i,1))))))
=LAMBDA(txt,TEXTJOIN("",,MAP(SEQUENCE(LEN(txt)),LAMBDA(i,IFERROR(MID("₀₁₂₃₄₅₆₇₈₉ₐᵦ𝒸𝒹ₑ𝒻𝓰ₕᵢⱼₖₗₘₙₒₚ𝓆ᵣₛₜᵤᵥ𝓌ₓᵧ𝓏",FIND(MID(txt,i,1),"0123456789abcdefghijklmnopqrstuvwxyz"),1),MID(txt,i,1))))))
Tóm tắt: tạo chuỗi mô tả phạm vi dữ liệu theo định dạng [TênSheet$CộtĐầu:CộtCuối], giúp xác định rõ sheet và cột của vùng được tham chiếu để dùng trong báo cáo hoặc công thức động.
Hàm: =LAMBDA(rng,SUBSTITUTE("["&TEXTAFTER(CELL("filename",INDEX(rng,1,1)),"]")&"$"&IF(ROWS(rng)=1,ADDRESS(ROW(INDEX(rng,1,1)),COLUMN(INDEX(rng,1,1)),4)&":"&SUBSTITUTE(ADDRESS(ROW(INDEX(rng,1,1)),COLUMN(INDEX(rng,COLUMNS(rng))),4),ROW(INDEX(rng,1,1)),""),ADDRESS(ROW(INDEX(rng,1,1)),COLUMN(INDEX(rng,1,1)),4)&":"&ADDRESS(ROW(INDEX(rng,ROWS(rng),COLUMNS(rng))),COLUMN(INDEX(rng,ROWS(rng),COLUMNS(rng))),4))&"]",". ","# "))
Ví dụ:
Tóm tắt: nối các giá trị trong vùng dữ liệu thành chuỗi dạng [Field1],[Field2],..., đồng thời loại bỏ ô trống và thay ký tự xuống dòng bằng _ cùng dấu chấm bằng #.
Hàm: =LAMBDA(rngs,TEXTJOIN(",",TRUE,MAP(rngs,LAMBDA(r,TEXTJOIN(",",TRUE,MAP(FILTER(r,LEN(r)),LAMBDA(c,"["&SUBSTITUTE(SUBSTITUTE(c,CHAR(10),"_"),".","#")&"]")))))))
Ví dụ:
Tóm tắt: trả về địa chỉ phạm vi dữ liệu (ví dụ A1:C10) từ một vùng được chọn trong Excel, giúp xác định rõ phần ô mà công thức tham chiếu.
Hàm: =LAMBDA(rng,ADDRESS(ROW(INDEX(rng,1,1)),COLUMN(INDEX(rng,1,1)),4)&":"&ADDRESS(ROW(INDEX(rng,ROWS(rng),COLUMNS(rng))),COLUMN(INDEX(rng,ROWS(rng),COLUMNS(rng))),4))
Ví dụ: