Đôi khi, bạn có thể muốn viết một công thức để trả về các điểm bắt đầu và kết thúc của một danh sách số hoặc ngày tháng trong Google Sheets. Điều này có thể giúp bạn xác định các giá trị bị thiếu trong chuỗi.
Điều này rất dễ dàng trong Google Sheets khi bạn sử dụng chuỗi số hoặc ngày tháng.
- Điểm bắt đầu là số nhỏ nhất trong chuỗi và điểm kết thúc là số lớn nhất trong chuỗi. Bạn có thể tìm chúng bằng cách sử dụng các hàm MIN và MAX và kết hợp chúng bằng một dấu gạch ngang.
Tuy nhiên, sẽ có nhiều điểm bắt đầu và kết thúc khi có các số bị thiếu trong chuỗi. Điều này xảy ra vì các số bị thiếu sẽ tạo ra khoảng trống trong chuỗi, dẫn đến nhiều điểm bắt đầu và kết thúc.
Ví dụ, nếu bạn xem xét danh sách {5,6,7,35,36,39,40,41,42,43}, các điểm bắt đầu và kết thúc sẽ là các khoảng 5-7, 35-36 và 39-43.
Để tạo ra loại khoảng như vậy, chúng ta có thể cần viết một công thức phức tạp nhưng dễ sử dụng.
Công thức Mảng để Trả về các Điểm Bắt Đầu và Kết Thúc từ Một Danh Sách trong Google Sheets
Tôi có hai công thức, một dành cho số và một dành cho ngày tháng.
Các công thức này gần giống nhau. Sự khác biệt duy nhất là phần định dạng ngày được thêm vào công thức trả về các điểm bắt đầu và kết thúc từ danh sách ngày tháng.
Vì vậy, tôi sẽ cung cấp công thức để trả về các khoảng ngày từ danh sách và nhấn mạnh phần bạn nên loại bỏ khi sử dụng nó với một cột số.
Công thức chính:
=ARRAYFORMULA(LET(list, E2:E, seq, SPLIT(FLATTEN(SPLIT(TEXTJOIN("|",TRUE,IFNA( XLOOKUP( SEQUENCE(MAX(list)-MIN(list)+1,1,MIN(list)), list, list ),",")),",")),"|"), fltr, FILTER(seq,CHOOSECOLS(seq,1)<>"|"), BYROW(fltr,LAMBDA(r, TEXTJOIN("-",TRUE,TO_DATE(MIN(r)),IF(MAX(r)=MIN(r),,TO_DATE(MAX(r))))) ) ) )
Đúng, bạn cần bỏ các hàm TO_DATE ra khỏi công thức khi bạn sử dụng nó với một danh sách số.
Ngoài ra, bạn chỉ cần thay thế E2:E bằng phạm vi chứa danh sách trong bảng của bạn.
Nếu bạn là người quan tâm đến việc học các công thức nâng cao trong Google Sheets, bạn có thể muốn biết làm cách nào công thức này trả về nhiều điểm bắt đầu và kết thúc từ một danh sách trong một bước duy nhất.
Trước tiên, tôi sẽ giải thích điều đó. Sau đó, tôi sẽ chia sẻ với bạn cách sử dụng kết quả trả về để đếm hoặc tổng các giá trị trong một cột nằm trong khoảng này.
Cấu trúc của Công thức
Có bốn bước quan trọng trong công thức trên. Dưới đây là chúng:
Bước 1
Công thức bước đầu tiên trong ô H2 là một công thức mảng XLOOKUP.
Công thức:
=ARRAYFORMULA(XLOOKUP(SEQUENCE(MAX(F2:F)-MIN(F2:F)+1,1,MIN(F2:F)),F2:F,F2:F))
Cú pháp:
XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
Trong đó:
search_keylà SEQUENCE(MAX(F2:F)-MIN(F2:F)+1,1,MIN(F2:F)), được điền bằng SEQUENCE sử dụng các giá trị MIN và MAX trong danh sách.lookup_rangelà F2:Fresult_rangelà F2:F
Công thức tìm các khóa tìm kiếm trong khoảng tìm kiếm và trả về các giá trị tương ứng từ khoảng kết quả. Mục đích là loại bỏ các giá trị bị thiếu trong danh sách khỏi chuỗi.
Lưu ý: Kết quả sẽ là các giá trị ngày nếu danh sách chứa các ngày. Đó là lý do tại sao chúng ta đã sử dụng TO_DATE trong công thức chính để định dạng lại chúng thành ngày.
Đây là một trong những bước chính trong việc trả về các điểm bắt đầu và kết thúc từ danh sách ngày tháng hoặc số trong Google Sheets.
Bước 2
Vui lòng xem kết quả bước đầu tiên trong phạm vi H2:H16. Hàm XLOOKUP đặt #N/A vào mọi nơi mà chuỗi có khoảng trống. Trong bước tiếp theo, chúng ta sẽ thay thế chúng bằng dấu phẩy và sau đó sử dụng TEXTJOIN để kết hợp tất cả các giá trị. Dấu phân cách sẽ là dấu đường ống.
Đây là công thức bước 2 được sử dụng trong ô I2.
=ARRAYFORMULA(TEXTJOIN("|",TRUE,IFNA(H2:H,",")))
Bước 3
Trong bước tiếp theo, chúng ta sẽ phân tách kết quả trên hai lớp và sau đó FLATTEN chúng. Tôi đã đặt công thức sau trong ô I3.
=ARRAYFORMULA(SPLIT(FLATTEN(SPLIT(I2,",")),"|"))
Bước 4
Công thức tiếp theo trong ô I11 lọc các hàng chứa dấu đường ống trong cột đầu tiên.
=FILTER(I3:N9,I3:I9<>"|")
Bước 5 (Cuối cùng)
Chúng ta chỉ cần trích xuất các giá trị tối thiểu và tối đa từ mỗi hàng của kết quả bước 4 và kết hợp chúng bằng một dấu gạch ngang.
Chúng ta có thể sử dụng BYROW LHF sau đây cho việc đó.
=BYROW(I11:N12,LAMBDA(r,TEXTJOIN("-",TRUE,MIN(r),MAX(r))))
Như vậy, chúng ta có thể trả về các điểm bắt đầu và kết thúc từ danh sách ngày tháng hoặc số trong Google Sheets.
Tổng hợp dữ liệu tương ứng với các Điểm Bắt Đầu và Kết Thúc được phát sinh từ một Danh Sách
Ở đầu bài viết này, tôi đã giải thích mục tiêu chính của việc trả về các điểm bắt đầu và kết thúc từ một danh sách số hoặc ngày tháng trong Google Sheets. Đó là để tìm các giá trị bị thiếu trong chuỗi.
Mục đích khác là tóm tắt dữ liệu dựa trên khoảng trả về bởi công thức. Tôi sẽ giải thích nó thông qua hai ví dụ. Tôi chắc chắn bạn sẽ thấy nó thú vị.
Trong ví dụ sau, chúng ta có ngày mua hàng, hàng hóa và số lượng mua hàng trong phạm vi A1:C.
Công thức trong ô E2 trả về các điểm bắt đầu và kết thúc của danh sách ngày (ngày mua hàng) trong phạm vi A2:A. Tôi không lặp lại công thức đó, vì bạn chỉ cần thay thế E2:E trong công thức chính của tôi bằng A2:A.
Chúng ta tập trung vào các công thức trong các cột F và G.
Tổng giá trị trong khoảng ngày Điểm Bắt Đầu và Kết Thúc (SUMIF)
Tôi đã sử dụng SUMIF sau đây trong ô F2 để tổng các giá trị nằm trong khoảng bắt đầu và kết thúc, còn được gọi là khoảng ngày, trong ô E2.
Cú pháp: SUMIF(range, criterion, [sum_range])
Công thức:
=SUMIF( ISBETWEEN($A$2:$A,CHOOSECOLS(SPLIT(E2,"-"),1),CHOOSECOLS(SPLIT(E2,"-"),2)), TRUE, $C$2:$C )
Trong đó:
rangelà ISBETWEEN($A$2:$A,CHOOSECOLS(SPLIT(E2,”-“),1),CHOOSECOLS(SPLIT(E2,”-“),2))criterionlà TRUEsum_rangelà C$2:$C
Tôi đã sao chép và dán công thức này vào các ô F3 và F5.
Công thức trong ô F4 là =SUMIF($A$2:$A,E6,$C$2:$C), tôi cũng đã sao chép và dán vào ô F6.
Lưu ý: Dấu phân cách trong hàm SPLIT là dấu gạch ngang nối (-), không phải dấu gạch ngang (-).
Đếm giá trị trong khoảng ngày Điểm Bắt Đầu và Kết Thúc (COUNTIF)
Chúng ta có thể đếm các giá trị tương ứng với các điểm bắt đầu và kết thúc được trả về từ danh sách ngày mua hàng trong phạm vi A2:A. Để làm điều đó, chúng ta có thể sử dụng hàm COUNTIF.
Cú pháp: COUNTIF(range, criterion)
Tôi đã sử dụng công thức COUNTIF sau đây trong ô G2:
=COUNTIF( ISBETWEEN($A$2:$A,CHOOSECOLS(SPLIT(E2,"-"),1),CHOOSECOLS(SPLIT(E2,"-"),2)), TRUE )
Trong đó:
rangelà ISBETWEEN($A$2:$A,CHOOSECOLS(SPLIT(E2,”-“),1),CHOOSECOLS(SPLIT(E2,”-“),2))criterionlà TRUE
Tôi đã sao chép và dán công thức này vào các ô G3 và G5.
Công thức COUNTIF trong ô G4 là =COUNTIF(A2:A,E4), tôi cũng đã sao chép và dán vào ô G6.
Đó là cách chúng ta có thể trả về các điểm bắt đầu và kết thúc từ danh sách ngày hoặc số trong Google Sheets và tổng hợp dữ liệu tương ứng với chúng.
Hy vọng bài viết này đã giúp bạn hiểu cách trả về các điểm bắt đầu và kết thúc từ một danh sách trong Google Sheets và làm thế nào để tổng hợp dữ liệu dựa trên các khoảng trả về.
