Macro để chọn dữ liệu từ một bảng. Yêu cầu lựa chọn dữ liệu (công thức) trong MS EXCEL. Lấy mẫu ngẫu nhiên trong Excel

Làm cách nào để có được danh sách các giá trị duy nhất (không lặp lại)?

Hãy tưởng tượng một danh sách lớn các tên khác nhau, tên đầy đủ, mã số nhân sự, v.v. Và từ danh sách này, cần phải để lại một danh sách có tất cả các tên giống nhau, nhưng để chúng không lặp lại - tức là. xóa tất cả các mục trùng lặp khỏi danh sách này. Như cách gọi khác: tạo danh sách các phần tử duy nhất, danh sách không lặp lại, không trùng lặp. Có một số cách để thực hiện việc này: các công cụ Excel tích hợp sẵn, các công thức tích hợp sẵn và cuối cùng là sử dụng mã Visual Basic cho ứng dụng (VBA) và các bảng trụ. Trong bài viết này chúng ta sẽ xem xét từng lựa chọn.

Sử dụng các khả năng tích hợp của Excel 2007 trở lên
Trong Excel 2007 và 2010, việc này rất dễ thực hiện - có một lệnh đặc biệt tên là - . Nó nằm trên tab Dữ liệu tiểu mục Làm việc với dữ liệu (Công cụ dữ liệu)

Cách sử dụng lệnh này. Chọn một cột (hoặc một số) với dữ liệu cần loại bỏ các bản ghi trùng lặp. Chuyển đến tab Dữ liệu -Loại bỏ các bản sao.

Nếu bạn chọn một cột, nhưng bên cạnh nó có các cột khác có dữ liệu (hoặc ít nhất một cột), thì Excel sẽ đưa ra lựa chọn: mở rộng phạm vi lựa chọn với cột này hoặc giữ nguyên vùng chọn và chỉ xóa dữ liệu trong cột này. phạm vi đã chọn. Điều quan trọng cần nhớ là nếu bạn không mở rộng phạm vi, dữ liệu sẽ chỉ được thay đổi trong một cột và dữ liệu ở cột liền kề sẽ không có một chút thay đổi nào.

Một cửa sổ với các tùy chọn loại bỏ trùng lặp sẽ xuất hiện.

Chọn các hộp bên cạnh các cột cần loại bỏ các bản sao và nhấp vào OK. Nếu phạm vi đã chọn cũng chứa các tiêu đề dữ liệu thì tốt hơn nên đặt cờ Dữ liệu của tôi chứa các tiêu đềđể tránh vô tình xóa dữ liệu trong bảng (nếu chúng đột nhiên trùng khớp hoàn toàn với ý nghĩa trong tiêu đề).

Cách 1: Bộ lọc nâng cao
Trong trường hợp của Excel 2003, mọi thứ phức tạp hơn. Không có công cụ nào như Loại bỏ các bản sao. Nhưng có một công cụ tuyệt vời như Bộ lọc nâng cao. Vào năm 2003, công cụ này có thể được tìm thấy trong Dữ liệu -Lọc -Bộ lọc nâng cao. Cái hay của phương pháp này là với sự trợ giúp của nó, bạn không thể làm hỏng dữ liệu gốc mà tạo ra một danh sách ở một phạm vi khác. Excel 2007-2010 cũng có nhưng hơi ẩn. Nằm trên tab Dữ liệu, nhóm Sắp xếp & Lọc - Ngoài ra (Nâng cao)
Cách sử dụng: chạy công cụ đã chỉ định - một hộp thoại xuất hiện:

• Sự đối đãi: Chọn Sao chép sang vị trí khác.
• Phạm vi danh sách: Chọn một phạm vi có dữ liệu (trong trường hợp của chúng tôi là A1:A51).
• Phạm vi tiêu chí: trong trường hợp này chúng tôi để trống.
• Đặt kết quả vào một phạm vi (Sao chép vào): chỉ ra ô đầu tiên hiển thị dữ liệu - bất kỳ ô trống nào (trong hình - E2).
• Đánh dấu Chỉ các bản ghi duy nhất.
• Nhấp chuột ĐƯỢC RỒI.

Ghi chú: nếu bạn muốn đặt kết quả trên một trang tính khác thì việc chỉ chỉ định một trang tính khác sẽ không có tác dụng. Bạn sẽ có thể chỉ định một ô trên một trang tính khác, nhưng...Than ôi và à...Excel sẽ hiển thị thông báo rằng nó không thể sao chép dữ liệu sang các trang tính khác. Nhưng điều này cũng có thể được tránh và khá đơn giản. Bạn chỉ cần khởi động Bộ lọc nâng cao từ trang tính mà chúng tôi muốn đặt kết quả. Và làm dữ liệu ban đầu, chúng tôi chọn dữ liệu từ bất kỳ trang tính nào - điều này được cho phép.

Bạn cũng không thể đưa kết quả vào các ô khác mà lọc dữ liệu tại chỗ. Dữ liệu sẽ không bị ảnh hưởng dưới bất kỳ hình thức nào - đó sẽ là quá trình lọc dữ liệu bình thường.

Để thực hiện việc này, bạn chỉ cần chọn trong mục Đang xử lý Lọc danh sách tại chỗ.

Cách 2: Công thức
Phương pháp này khó hiểu hơn đối với người dùng thiếu kinh nghiệm, nhưng nó tạo ra một danh sách các giá trị duy nhất mà không thay đổi dữ liệu gốc. Chà, nó năng động hơn: nếu bạn thay đổi dữ liệu trong bảng nguồn, kết quả sẽ thay đổi. Đôi khi điều này rất hữu ích. Tôi sẽ cố gắng giải thích bằng ngón tay của mình là gì: giả sử danh sách dữ liệu của bạn nằm ở cột A (A1:A51, trong đó A1 là tiêu đề). Chúng tôi sẽ hiển thị danh sách trong cột C, bắt đầu từ ô C2. Công thức ở C2 sẽ như sau:
(=INDEX($A$2:$A$51,SMALL(IF(COUNTIF($C$1:C1, $A$2:$A$51)=0,ROW($A$1:$A$50)),1)) )
(=INDEX($A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1)) )
Một phân tích chi tiết về cách thức hoạt động của công thức này được đưa ra trong bài viết:
Cần lưu ý rằng công thức này là một công thức mảng. Điều này có thể được biểu thị bằng dấu ngoặc nhọn kèm theo công thức này. Và công thức này được nhập vào một ô bằng phím tắt - Điều khiển+Sự thay đổi+Đi vào. Khi chúng ta đã nhập công thức này vào C2, chúng ta phải sao chép và dán nó qua nhiều dòng để tất cả các phần tử duy nhất được hiển thị chính xác. Khi công thức ở các ô phía dưới trả về #CON SỐ!- điều này có nghĩa là tất cả các phần tử đều được hiển thị và không có ích gì khi mở rộng công thức bên dưới. Để tránh lỗi và làm cho công thức trở nên phổ biến hơn (không cần phải kéo nó ra mỗi lần cho đến khi lỗi xuất hiện), bạn có thể sử dụng một cách kiểm tra đơn giản:
cho Excel 2007 trở lên:
(=IFERROR(INDEX($A$2:$A$51 ;SMALL(IF(COUNT($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1 ));""))
(=IFERROR(INDEX($A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1 ));""))
cho Excel 2003:
(=IF(EOSH(SMALL(IF(COUNTIF($C$1:C1, $A$2:$A$51)=0,ROW($A$1:$A$50)),1));"";INDEX( $A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1, $A$2:$A$51)=0,ROW($A$1:$A$50)),1))))
(=IF(ISERR(SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1));"";INDEX( $A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1))))
Sau đó thay vì một lỗi #CON SỐ! (#NUM!) bạn sẽ có ô trống (tất nhiên không hoàn toàn trống - với các công thức :-)).

Bạn có thể đọc thêm một chút về sự khác biệt và sắc thái của công thức IFERROR và IF(EOSH) trong bài viết này: Cách hiển thị 0 thay vì lỗi trong ô có công thức

Cách 3: Mã VBA
Cách tiếp cận này sẽ yêu cầu quyền macro và kiến ​​thức cơ bản khi làm việc với chúng. Nếu bạn không tự tin vào kiến ​​thức của mình, tôi khuyên bạn nên đọc những bài viết này trước:

• Macro là gì và tìm nó ở đâu? một video hướng dẫn được đính kèm vào bài viết
• Mô-đun là gì? Có những loại mô-đun nào? sẽ cần phải hiểu nơi chèn mã bên dưới

Cả hai mã dưới đây nên được đặt trong mô-đun tiêu chuẩn. Macro phải được cho phép.

Chúng ta sẽ để dữ liệu gốc theo đúng thứ tự - danh sách có dữ liệu nằm ở cột “A” (A1:A51, trong đó A1 là tiêu đề). Chỉ có điều chúng tôi sẽ hiển thị danh sách không phải ở cột C mà ở cột E, bắt đầu từ ô E2:

Sub Extract_Unique() Dim vItem, avArr, li As Long ReDim avArr(1 To Rows.Count, 1 To 1) Với Bộ sưu tập mới khi có lỗi Tiếp tục tiếp theo cho mỗi vItem trong phạm vi("A2", Cells(Rows.Count, 1) .End(xlUp)).Value "Cells(Rows.Count, 1).End(xlUp) – xác định ô được điền cuối cùng trong cột A. Thêm vItem, CStr(vItem) Nếu Err = 0 Thì li = li + 1: avArr(li, 1) = vItem Khác: Err.Clear End If Next End With If li Then .Resize(li).Value = avArr End Sub

Sử dụng mã này, bạn có thể trích xuất những mã duy nhất không chỉ từ một cột mà còn từ bất kỳ phạm vi cột và hàng nào. Nếu thay vì một dòng
Phạm vi("A2", Cells(Rows.Count, 1).End(xlUp)).Value !}
chỉ định Selection.Value , thì kết quả của mã sẽ là danh sách các phần tử duy nhất từ ​​phạm vi được chọn trên trang hoạt động. Chỉ khi đó mới tốt hơn nếu thay đổi ô giá trị đầu ra - thay vì đặt một trong đó không có dữ liệu.
Bạn cũng có thể chỉ định một phạm vi cụ thể:

Range("C2", Cells(Rows.Count, 3).End(xlUp)).Value

Mã chung để chọn các giá trị duy nhất
Mã bên dưới có thể được sử dụng cho bất kỳ phạm vi nào. Tất cả những gì bạn cần làm là chạy nó, chỉ định một phạm vi có các giá trị để chỉ chọn những giá trị không lặp lại (được phép chọn nhiều hơn một cột) và một ô để hiển thị kết quả. Các ô được chỉ định sẽ được quét, chỉ các giá trị duy nhất sẽ được chọn từ chúng (các ô trống bị bỏ qua) và danh sách kết quả sẽ được ghi bắt đầu từ ô được chỉ định.

Sub Extract_Unique() Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "yêu cầu địa chỉ ô để chọn các giá trị duy nhất Đặt rVals = Application.InputBox("Chỉ định phạm vi ô thành chọn các giá trị duy nhất" , "Yêu cầu dữ liệu", "A2:A51", Loại:=8) Nếu rVals không có gì thì "nếu nhấn nút Hủy Thoát Sub End Nếu "nếu chỉ chỉ định một ô thì không có ý nghĩa gì khi chọn If rVals.Count = 1 Then MsgBox " Để chọn các giá trị duy nhất, bạn cần chỉ định nhiều hơn một ô", vbInformation, "www.site" Exit Sub End If "cắt bỏ các hàng và cột trống ngoài phạm vi làm việc Đặt rVals = Intersect(rVals, rVals.Parent.UsedRange) "nếu chỉ có các ô trống được chỉ định bên ngoài phạm vi làm việc Nếu rVals không là gì thì MsgBox "Không đủ dữ liệu để chọn giá trị", vbInformation, "www..Value"requesting a ô để hiển thị kết quả Đặt rResultCell = Application.InputBox("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8) If rResultCell Is Nothing Then "если нажата кнопка Отмена Exit Sub End If "определяем максимально возможную размерность массива для результата ReDim avArr(1 To Rows.Count, 1 To 1) "при помощи объекта Коллекции(Collection) "отбираем только уникальные записи, "т.к. Коллекции не могут содержать повторяющиеся значения With New Collection On Error Resume Next For Each x In avVals If Len(CStr(x)) Then "пропускаем пустые ячейки.Add x, CStr(x) "если добавляемый элемент уже есть в Коллекции - возникнет ошибка "если же ошибки нет - такое значение еще не внесено, "добавляем в результирующий массив If Err = 0 Then li = li + 1 avArr(li, 1) = x Else "обязательно очищаем объект Ошибки Err.Clear End If End If Next End With "записываем результат на лист, начиная с указанной ячейки If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr End Sub!}

Phương pháp 4: Bảng tổng hợp
Một cách không chuẩn để trích xuất các giá trị duy nhất.

• Chọn một hoặc nhiều cột trong bảng và chuyển đến tab Chèn-nhóm Bàn -bảng tổng hợp
• Trong hộp thoại Tạo PivotTable kiểm tra xem phạm vi dữ liệu đã được phân bổ chính xác chưa (hoặc cài đặt nguồn dữ liệu mới)
• cho biết vị trí của Pivot Table:
  • Đến một trang tính mới (Bảng tính mới)
  • Đến một trang tính hiện có (Bảng tính hiện có)
• xác nhận việc tạo bằng cách nhấn nút ĐƯỢC RỒI

Bởi vì Bảng tổng hợp, khi xử lý dữ liệu được đặt trong khu vực hàng hoặc cột, chỉ chọn các giá trị duy nhất từ ​​​​chúng để phân tích tiếp theo, khi đó chúng tôi hoàn toàn không yêu cầu gì ngoại trừ việc tạo bảng tổng hợp và đặt dữ liệu mong muốn cột trong khu vực hàng hoặc cột.
Lấy file đính kèm bài viết làm ví dụ:

Điều bất tiện khi làm việc với Pivot trong trường hợp này là gì: nếu có thay đổi về dữ liệu nguồn, bảng Pivot sẽ phải được cập nhật thủ công: Chọn bất kỳ ô nào của bảng Pivot - Nút chuột phải - Làm cho khỏe lại hoặc tab Dữ liệu -Làm mới tất cả -Làm cho khỏe lại. Và nếu dữ liệu nguồn được cập nhật động, thậm chí tệ hơn, bạn sẽ cần phải chỉ định lại phạm vi của dữ liệu nguồn. Và một điểm trừ nữa - dữ liệu bên trong bảng tổng hợp không thể thay đổi được. Do đó, nếu bạn cần làm việc với danh sách kết quả trong tương lai, thì sau khi tạo danh sách mong muốn bằng danh sách tóm tắt, nó phải được sao chép và dán vào trang tính mong muốn.

Để hiểu rõ hơn về tất cả các thao tác và tìm hiểu cách xử lý bảng trụ, tôi thực sự khuyên bạn nên đọc bài viết Thông tin chung về bảng trụ - kèm theo video hướng dẫn trong đó tôi thể hiện rõ ràng sự đơn giản và dễ dàng khi làm việc với các khả năng cơ bản của các bảng trụ.

Trong ví dụ đính kèm, ngoài các kỹ thuật được mô tả, một biến thể phức tạp hơn một chút của việc trích xuất các phần tử duy nhất bằng cách sử dụng công thức và mã được viết, cụ thể là: trích xuất các phần tử duy nhất theo tiêu chí. Điều chúng ta đang nói đến: nếu một cột chứa họ và cột thứ hai (B) chứa một số dữ liệu (trong tệp là tháng) và bạn cần trích xuất các giá trị duy nhất của cột B chỉ cho họ đã chọn . Ví dụ về các chiết xuất độc đáo như vậy được đặt trên trang tính Truy xuất theo tiêu chí.

Tải xuống ví dụ:

(108,0 KiB, 14.152 lượt tải xuống)

Để thực hiện việc này, bạn cần mở Visual Basic Editor (tổ hợp phím “Alt+F11” hoặc nhấp chuột phải vào lối tắt của bất kỳ trang tính nào và chọn “Văn bản nguồn” hoặc trong nhóm “Mã” của tab menu “Nhà phát triển” , nhấp vào mục “Visual Basic”) ) và chèn một mô-đun tiêu chuẩn vào dự án. Và thêm hai mã chương trình vào mô-đun này (xem Hình 8.) - và .

Cách tối ưu việc chọn nhiều giá trị tìm kiếm từ các bảng khác nhau trong Excel

Khi chuẩn bị báo cáo về số lượng và điều khoản thanh toán cho một số nhà cung cấp riêng lẻ, thường cần phải sàng lọc một lượng dữ liệu (bảng) đáng kể trước khi tìm thấy thông tin bạn đang tìm kiếm.

Bạn có thể tối ưu hóa việc lựa chọn một số giá trị mong muốn (số tiền thanh toán cho các nhà cung cấp khác nhau) theo một tham số (ví dụ: theo ngày) từ một số ít bảng bằng cách sử dụng sơ đồ gần giống như với điểm khác biệt duy nhất là bảng “Mục lục sách” sẽ hiển thị danh sách tên của các bảng tham gia lựa chọn và kết quả của nó, đồng thời, một công thức tìm kiếm bảng hơi khác sẽ được sử dụng:

“=VLOOKUP($C$1,INDIRECT(A2),2,FALSE)”, trong đó:

• ô C1 (số quý) – đặt giá trị tham số;
• “INDIRECT(A2)” – xác định liên kết văn bản đến một phạm vi được đặt tên, tên của phạm vi đó nằm trong ô A2;
• “2” là số cột của bảng nguồn các nhà cung cấp, trong đó có số tiền thanh toán mà chúng tôi cần;
• “FALSE” (có thể được thay thế bằng 0) – cho biết hàm VLOOKUP cần phải khớp chính xác.

Phương pháp một: Sử dụng bộ lọc tự động nâng cao

Trên một bảng Excel, bạn cần chọn một vùng trong số dữ liệu mà bạn muốn chọn. Trong tab “Trang chủ”, nhấp vào “Sắp xếp và lọc” (nằm trong khối cài đặt “Chỉnh sửa”). Tiếp theo, nhấp vào bộ lọc.

Bạn có thể thực hiện theo cách khác: sau khi chọn khu vực, hãy chuyển đến tab “Dữ liệu” và nhấp vào “Bộ lọc”, nằm trong nhóm “Sắp xếp và Lọc”.

Khi các bước này hoàn tất, các biểu tượng sẽ xuất hiện trong tiêu đề bảng để bắt đầu lọc. Chúng sẽ được hiển thị hướng xuống dưới dạng hình tam giác nhỏ ở cạnh phải của ô. Nhấp vào biểu tượng này ở đầu cột mà bạn sẽ thực hiện lựa chọn. Khởi chạy menu, đi tới “Bộ lọc văn bản” và chọn “Bộ lọc tùy chỉnh…”.

Cửa sổ Lọc tùy chỉnh bây giờ sẽ được kích hoạt. Trong đó bạn đặt ra giới hạn mà việc lựa chọn sẽ được thực hiện. Bạn có thể chọn một trong năm loại điều kiện được đề xuất: bằng, không bằng, lớn hơn, lớn hơn hoặc bằng, nhỏ hơn.

Sau khi lọc, chỉ còn lại những dòng có số tiền doanh thu vượt quá 10.000 (làm ví dụ).

Trong cùng một cột, bạn có thể thêm điều kiện thứ hai. Bạn cần quay lại cửa sổ lọc tùy chỉnh một lần nữa và đặt giới hạn lựa chọn khác ở phần dưới của cửa sổ. Đặt công tắc sang vị trí “Ít hơn” và nhập “15000” vào trường bên phải.

Bảng sẽ chỉ chứa những hàng trong đó số tiền doanh thu không nhỏ hơn 10.000 nhưng không quá 15.000.

Trong các cột khác, lựa chọn được cấu hình theo cách tương tự. Trong cột mong muốn, nhấp vào biểu tượng bộ lọc, sau đó nhấp tuần tự vào các mục danh sách “Lọc theo ngày” và “Bộ lọc tùy chỉnh”.

Cửa sổ Bộ lọc Tự động Tùy chỉnh sẽ mở ra. Ví dụ: thực hiện chọn lọc các kết quả trong bảng từ ngày 4 tháng 5 đến ngày 6 tháng 5 năm 2016. Nhấp vào “Sau hoặc bằng” và trong trường bên phải, nhập giá trị “05/04/2016”. Ở khối bên dưới, đặt công tắc ở vị trí “Trước hoặc bằng” và ở trường bên phải nhập “05/06/2016”. Để công tắc tương thích điều kiện ở vị trí mặc định, tức là “VÀ”. Để áp dụng tính năng lọc, hãy nhấp vào OK.

Danh sách bây giờ sẽ còn giảm hơn nữa, vì sẽ chỉ còn lại các hàng có số tiền doanh thu thay đổi từ 10.000 đến 15.000 và điều này áp dụng cho khoảng thời gian từ ngày 04/05 đến ngày 06/05/2016.

Ở một trong các cột, bạn có thể đặt lại bộ lọc nếu muốn. Ví dụ: bạn có thể thực hiện việc này đối với giá trị doanh thu. Bấm vào biểu tượng bộ lọc tự động ở cột tương ứng. Chọn "Xóa bộ lọc".

Lựa chọn theo số tiền doanh thu sẽ bị vô hiệu hóa và chỉ còn lựa chọn theo ngày (từ ngày 04/05/2016 đến ngày 06/05/2016).

Có một cột khác trong bảng tên là “Tên”. Nó chứa dữ liệu ở định dạng văn bản. Sử dụng các giá trị này, bạn cũng có thể tạo thành một mẫu. Trong tên cột, nhấp vào biểu tượng bộ lọc. Đi tới “Bộ lọc văn bản” và sau đó “Bộ lọc tùy chỉnh…”.

Cửa sổ bộ lọc tùy chỉnh sẽ mở lại, trong đó bạn có thể thực hiện lựa chọn, chẳng hạn như theo tên “Thịt” và “Khoai tây”. Trong khối đầu tiên, bạn cần đặt công tắc sang vị trí “Bằng” và nhập “Khoai tây” vào trường bên phải của nó. Đặt công tắc của khối bên dưới sang vị trí “Bằng” và ở trường đối diện - “Thịt”. Bây giờ bạn nên đặt công tắc tương thích điều kiện sang vị trí “HOẶC”. Bấm vào đồng ý.

Trong mẫu mới, các hạn chế được đặt theo ngày (từ 04/05/2016 đến 06/05/2016) và theo tên (Thịt và Khoai tây). Không có hạn chế chỉ về số tiền doanh thu.

Bạn có thể loại bỏ hoàn toàn bộ lọc và việc này được thực hiện bằng các phương pháp tương tự đã được sử dụng để đặt bộ lọc. Để đặt lại tính năng lọc, trong tab “Dữ liệu”, hãy nhấp vào “Bộ lọc” trong nhóm “Sắp xếp và lọc”.

Trong tùy chọn thứ hai, bạn có thể chuyển đến tab “Trang chủ” và nhấp vào “Sắp xếp và lọc” trong “Chỉnh sửa”. Tiếp theo, nhấp vào “Bộ lọc”.

Nếu bạn sử dụng bất kỳ phương pháp nào trong số này, bảng sẽ bị xóa và kết quả lựa chọn sẽ bị xóa. Tức là bảng sẽ hiển thị tất cả dữ liệu đã nhập trước đó vào bảng.

Phương pháp hai: Sử dụng công thức mảng

Trên cùng một trang tính Excel, hãy tạo một bảng trống có cùng tên cột trong tiêu đề trong nguồn.

Tất cả các ô trống phải được chọn trong cột đầu tiên của bảng mới. Đặt con trỏ vào thanh công thức để nhập công thức - =INDEX(A2:A29,SMALL(IF(15000

Để áp dụng công thức, nhấn phím Ctrl+Shift+Enter.

Chọn cột thứ hai có ngày tháng và đặt con trỏ vào thanh công thức để nhập - =INDEX(B2:B29,SMALL(IF(15000

Theo cách tương tự, hãy nhập công thức sau vào cột doanh thu - =INDEX(C2:C29,SMALL(IF(15000

Một cửa sổ định dạng sẽ mở ra, trong đó bạn cần chọn tab Số Số. Trong "Định dạng số", chọn "Ngày". Ở phần bên phải của cửa sổ, nếu muốn, bạn có thể chọn loại ngày hiển thị và khi tất cả các cài đặt đã được thực hiện xong, hãy nhấp vào OK.

Bây giờ mọi thứ sẽ đẹp và ngày sẽ được hiển thị chính xác. Nếu các ô hiển thị giá trị “#NUMBER!” thì bạn cần áp dụng định dạng có điều kiện. Tất cả các ô của bảng phải được chọn (ngoại trừ tiêu đề) và trong tab “Trang chủ”, nhấp vào “Định dạng có điều kiện” (trong khối công cụ “kiểu”). Một danh sách sẽ xuất hiện trong đó bạn nên chọn “Tạo quy tắc…”.

Chọn quy tắc “Chỉ định dạng các ô có chứa” và trong trường đầu tiên, nằm dưới dòng “Chỉ định dạng các ô đáp ứng điều kiện sau”, chọn “Lỗi” và nhấp vào “Định dạng…”.

Một cửa sổ định dạng sẽ mở ra, trong đó hãy chuyển đến “Phông chữ” và chọn màu trắng. Bấm vào đồng ý.

Một mẫu làm sẵn theo giới hạn đã chỉ định sẽ xuất hiện trước mặt bạn và tất cả sẽ nằm trong một bảng riêng.

Phương pháp thứ ba: Lấy mẫu theo nhiều điều kiện bằng công thức

Các điều kiện biên của mẫu phải được nhập vào một cột riêng.

Lần lượt chọn các cột trống của bảng mới để nhập ba công thức cần thiết vào đó. Trong cột đầu tiên, nhập - =INDEX(A2:A29,SMALL(IF(($D$2=C2:C29),ROW(C2:C29);"");ROW(C2:C29)-ROW($C$1 ) )-ROW($C$1)). Tiếp theo, nhập các công thức tương tự vào các cột, chỉ thay đổi tọa độ sau tên của toán tử INDEX thành những công thức cần thiết và tương ứng với các cột nhất định. Mọi thứ đều tương tự như phương pháp trước đó. Mỗi lần nhập đừng quên nhấn tổ hợp phím Ctrl+Shift+Enter.

Nếu cần thay đổi ranh giới lấy mẫu, bạn chỉ cần thay đổi số ranh giới trong cột điều kiện và khi đó kết quả lựa chọn sẽ tự động được thay đổi.

Phương pháp thứ tư: Lấy mẫu ngẫu nhiên

Ở phía bên trái của bảng, bạn cần bỏ qua một cột và trong ô của cột tiếp theo, nhập công thức - =RAND() để hiển thị một số ngẫu nhiên. Để kích hoạt nó, nhấn ENTER.

Nếu bạn cần tạo toàn bộ cột gồm các số ngẫu nhiên, hãy đặt con trỏ ở góc dưới bên phải của ô chứa công thức. Một điểm đánh dấu điền sẽ xuất hiện và bạn có thể kéo xuống bằng cách giữ nút chuột trái. Việc này được thực hiện song song với bảng có dữ liệu và đến hết.

Phạm vi ô sẽ chứa công thức RAND, nhưng bạn không cần phải làm việc với các giá trị thuần túy. Sao chép vào cột trống bên phải và chọn một dãy ô có số ngẫu nhiên. Trong tab “Trang chủ”, nhấp vào “Sao chép”.

Chọn một cột trống và nhấp chuột phải để hiển thị menu ngữ cảnh. Trong nhóm công cụ “Tùy chọn chèn”, chọn “Giá trị” (hiển thị dưới dạng biểu tượng có số).

Trong tab “Trang chủ”, nhấp vào “Sắp xếp và lọc”, sau đó nhấp vào “Sắp xếp tùy chỉnh”.

Bên cạnh tùy chọn “Dữ liệu của tôi chứa tiêu đề”, hãy chọn hộp. Trong dòng "Sắp xếp theo", cho biết tên của cột chứa các giá trị số ngẫu nhiên được sao chép. Trong dòng “Sắp xếp”, các cài đặt vẫn ở mức mặc định. Trong dòng “Đặt hàng”, chọn tùy chọn “Tăng dần” hoặc “Giảm dần”. Bấm vào đồng ý.

Các giá trị trong bảng phải được sắp xếp theo thứ tự tăng dần hoặc giảm dần của các số ngẫu nhiên. Bất kỳ số hàng đầu tiên nào trong bảng đều có thể được lấy và coi là kết quả của một mẫu ngẫu nhiên.

Nếu bạn làm việc với các bảng lớn, chắc chắn bạn sẽ tìm thấy số lượng trùng lặp nằm rải rác dọc theo toàn bộ một cột. Đồng thời, bạn có thể cần chọn dữ liệu từ một bảng có giá trị số nhỏ nhất đầu tiên, có giá trị trùng lặp riêng. Chúng tôi cần lấy mẫu dữ liệu tự động dựa trên điều kiện. Trong Excel, bạn có thể sử dụng thành công công thức mảng cho mục đích này.

Cách chọn vùng trong Excel theo điều kiện

Để xác định giá trị tương ứng cho số nhỏ nhất đầu tiên, cần có mẫu từ bảng theo điều kiện. Giả sử chúng ta muốn tìm ra sản phẩm rẻ nhất đầu tiên trên thị trường từ một bảng giá nhất định:

Việc lấy mẫu tự động được chúng tôi triển khai theo công thức sẽ có cấu trúc như sau:

INDEX(data_range_for_sampling, MIN(IF(range=MIN(range),ROW(range)-ROW(column_header);””)))

Ở vị trí “data_range_for_sampling”, bạn nên chỉ định phạm vi giá trị A6:A18 để lấy mẫu từ một bảng (ví dụ: văn bản), từ đó hàm INDEX sẽ chọn một giá trị kết quả. Đối số phạm vi đề cập đến vùng ô có giá trị số mà từ đó số nhỏ nhất đầu tiên sẽ được chọn. Trong đối số “column_header” cho hàm ROW thứ hai, bạn nên chỉ định tham chiếu đến ô có tiêu đề cột chứa một phạm vi giá trị số.

Đương nhiên, công thức này phải được thực thi trong một mảng. Do đó, để xác nhận mục nhập của nó, bạn không chỉ nhấn phím Enter mà còn phải nhấn toàn bộ tổ hợp phím CTRL+SHIFT+Enter. Nếu mọi thứ được thực hiện chính xác, dấu ngoặc nhọn sẽ xuất hiện trên thanh công thức.

Hãy chú ý đến hình bên dưới, nơi công thức này được nhập vào ô B3 trong mảng:

Lấy mẫu giá trị tương ứng với số nhỏ nhất trước:

Với công thức này, chúng ta có thể chọn giá trị tối thiểu tương ứng với các con số. Tiếp theo, chúng ta sẽ phân tích nguyên lý hoạt động của công thức và từng bước phân tích toàn bộ thứ tự của tất cả các phép tính.

Cách lấy mẫu có điều kiện hoạt động

Hàm INDEX đóng một vai trò quan trọng ở đây. Nhiệm vụ danh nghĩa của nó là chọn các giá trị từ bảng nguồn (được biểu thị trong đối số đầu tiên - A6:A18) tương ứng với các số nhất định. INDEX hoạt động có tính đến các tiêu chí được xác định trong đối số thứ hai (số hàng trong bảng) và đối số thứ ba (số cột trong bảng). Vì bảng nguồn A6:A18 của chúng tôi chỉ có 1 cột nên chúng tôi không chỉ định đối số thứ ba trong hàm INDEX.

Để tính số hàng của bảng đối diện với số nhỏ nhất trong phạm vi liền kề B6:B18 và sử dụng nó làm giá trị cho đối số thứ hai, một số hàm tính toán sẽ được sử dụng.

Hàm IF cho phép bạn chọn một giá trị từ danh sách dựa trên một điều kiện. Đối số đầu tiên của nó chỉ định vị trí mỗi ô trong phạm vi B6:B18 được kiểm tra giá trị số nhỏ nhất: IFB6:B18=MINB6:B18. Bằng cách này, một mảng các giá trị logic TRUE và FALSE được tạo trong bộ nhớ chương trình. Trong trường hợp của chúng tôi, 3 phần tử mảng sẽ chứa giá trị TRUE, vì giá trị tối thiểu là 8 chứa thêm 2 phần tử trùng lặp trong cột B6:B18.

Bước tiếp theo là xác định hàng nào trong phạm vi chứa từng giá trị nhỏ nhất. Chúng ta cần điều này vì giá trị nhỏ nhất đầu tiên đã được xác định. Tác vụ này được thực hiện bằng hàm ROW; nó lấp đầy các phần tử của mảng trong bộ nhớ chương trình bằng số dòng của trang tính. Nhưng trước tiên, từ tất cả những số này, số ở đối diện hàng đầu tiên của bảng sẽ bị trừ đi - B5, tức là số 5. ​​Điều này được thực hiện vì hàm INDEX hoạt động với các số bên trong bảng chứ không phải với các số trong Bảng tính Excel. Đồng thời, hàm ROW chỉ có thể trả về số dòng của trang tính. Để tránh sự dịch chuyển, cần so sánh thứ tự số hàng của trang tính và bảng bằng cách trừ đi phần chênh lệch. Ví dụ: nếu bảng nằm ở hàng thứ 5 của trang tính thì mỗi hàng trong bảng sẽ nhỏ hơn hàng tương ứng của trang tính 5 đơn vị.

Sau khi đã chọn xong tất cả các giá trị nhỏ nhất và so sánh tất cả số hàng trong bảng, hàm MIN sẽ chọn số hàng nhỏ nhất. Hàng này sẽ chứa số nhỏ nhất đầu tiên xuất hiện trong cột B6:B18. Dựa trên số hàng này, hàm INDEX sẽ chọn giá trị tương ứng từ bảng A6:A18. Kết quả là công thức trả về giá trị này cho ô B3 dưới dạng kết quả của phép tính.

Cách chọn giá trị có số lớn nhất trong Excel

Khi đã hiểu nguyên tắc của công thức, giờ đây bạn có thể dễ dàng sửa đổi và điều chỉnh nó cho phù hợp với các điều kiện khác. Ví dụ: công thức có thể được sửa đổi để chọn giá trị lớn nhất đầu tiên trong Excel:

Nếu bạn cần thay đổi các điều kiện của công thức để có thể chọn mức tối đa đầu tiên trong Excel nhưng nhỏ hơn 70:

Cách chọn giá trị tối thiểu đầu tiên khác 0 trong Excel:

Có thể dễ dàng nhận thấy, các công thức này chỉ khác nhau ở hàm MIN và MAX cũng như các đối số của chúng.

Bây giờ không có gì giới hạn bạn. Khi đã hiểu nguyên lý hoạt động của các công thức trong mảng, bạn có thể dễ dàng sửa đổi chúng cho phù hợp với nhiều điều kiện và giải quyết nhanh chóng nhiều bài toán tính toán.

Sử dụng các công cụ Excel, bạn có thể chọn một số dữ liệu nhất định từ một phạm vi theo thứ tự ngẫu nhiên, theo một hoặc một số điều kiện. Để giải quyết những vấn đề như vậy, theo quy luật, các công thức mảng hoặc macro được sử dụng. Hãy xem xét các ví dụ.

Cách chọn vùng trong Excel theo điều kiện

Khi sử dụng công thức mảng, dữ liệu đã chọn sẽ được hiển thị trong một bảng riêng. Đây chính là ưu điểm của phương pháp này so với bộ lọc thông thường.

Bảng nguồn:

Trước tiên, hãy tìm hiểu cách tạo mẫu bằng cách sử dụng một tiêu chí số. Nhiệm vụ là chọn từ bàn những sản phẩm có giá trên 200 rúp. Một cách để giải quyết vấn đề này là sử dụng tính năng lọc. Do đó, chỉ những sản phẩm đáp ứng yêu cầu mới còn lại trong bảng nguồn.

Một giải pháp khác là sử dụng công thức mảng. Các hàng tương ứng với truy vấn sẽ được đặt trong một bảng báo cáo riêng.

Đầu tiên, chúng ta tạo một bảng trống bên cạnh bảng gốc: chúng ta sao chép tiêu đề, số hàng và cột. Bảng mới chiếm phạm vi E1:G10. Bây giờ chọn E2:E10 (cột “Ngày”) và nhập công thức sau: ( }.

Để có được công thức mảng, hãy nhấn tổ hợp phím Ctrl + Shift + Enter. Ở cột bên cạnh - “Sản phẩm” - nhập công thức mảng tương tự: ( ). Chỉ đối số đầu tiên của hàm INDEX đã thay đổi.

Trong cột “Giá”, chúng ta nhập cùng một công thức mảng, thay đổi đối số đầu tiên của hàm INDEX.

Kết quả là chúng tôi nhận được một báo cáo về hàng hóa có giá hơn 200 rúp.

Lựa chọn này mang tính linh hoạt: khi truy vấn thay đổi hoặc sản phẩm mới xuất hiện trong bảng nguồn, báo cáo sẽ tự động thay đổi.

Nhiệm vụ số 2 – chọn từ các sản phẩm trong bảng gốc đã được bán vào ngày 20 tháng 9 năm 2015. Tức là tiêu chí lựa chọn là ngày tháng. Để thuận tiện, chúng tôi nhập ngày mong muốn vào một ô riêng, I2.

Để giải quyết vấn đề, một công thức mảng tương tự được sử dụng. Chỉ thay vì tiêu chí).

Các công thức tương tự được nhập vào các cột khác (xem nguyên tắc ở trên).

Bây giờ chúng ta sử dụng tiêu chí văn bản. Thay vì ngày trong ô I2, hãy nhập văn bản “Sản phẩm 1”. Hãy thay đổi công thức mảng một chút nhé: ( }.

Một hàm lựa chọn lớn như vậy trong Excel.

Chọn theo nhiều điều kiện trong Excel

Đầu tiên, hãy lấy hai tiêu chí số:

Nhiệm vụ là chọn những sản phẩm có giá dưới 400 và hơn 200 rúp. Chúng tôi kết hợp các điều kiện với dấu “*”. Công thức mảng trông như thế này: ( }.!}

Đây là cột đầu tiên của bảng báo cáo. Đối với đối số thứ hai và thứ ba, chúng ta thay đổi đối số đầu tiên của hàm INDEX. Kết quả:

Để thực hiện lựa chọn dựa trên một số ngày hoặc tiêu chí số, chúng tôi sử dụng các công thức mảng tương tự.

Lấy mẫu ngẫu nhiên trong Excel

Khi người dùng đang làm việc với một lượng lớn dữ liệu, có thể cần lấy mẫu ngẫu nhiên để phân tích tiếp theo. Mỗi hàng có thể được gán một số ngẫu nhiên và sau đó được sắp xếp để lựa chọn.

Tập dữ liệu gốc:

Đầu tiên, chèn hai cột trống ở bên trái. Trong ô A2 chúng ta nhập công thức RAND(). Hãy nhân nó với toàn bộ cột:

Bây giờ chúng ta sao chép cột có số ngẫu nhiên và dán vào cột B. Điều này là cần thiết để những số này không thay đổi khi dữ liệu mới được nhập vào tài liệu.

Để chèn giá trị thay vì công thức, nhấp chuột phải vào cột B và chọn công cụ Paste Special. Trong cửa sổ mở ra, hãy đánh dấu bên cạnh mục “Giá trị”:

Bây giờ bạn có thể sắp xếp dữ liệu trong cột B theo thứ tự tăng dần hoặc giảm dần. Thứ tự trình bày các giá trị ban đầu cũng sẽ thay đổi. Chúng tôi chọn bất kỳ số hàng nào từ phía trên hoặc phía dưới - chúng tôi lấy một mẫu ngẫu nhiên.