Khi làm việc với các bảng Excel, thường là cần thiết để thực hiện lựa chọn về một tiêu chí cụ thể hoặc trong một số điều kiện. Chương trình có thể làm điều này theo nhiều cách khác nhau bằng cách sử dụng một loạt các công cụ. Hãy tìm hiểu cách tạo một mẫu trong Excele bằng cách sử dụng nhiều tùy chọn khác nhau.
Lấy mẫu
Mẫu dữ liệu bao gồm quy trình lựa chọn từ tổng số các kết quả đó đáp ứng các điều kiện đã chỉ định, theo sau là đầu ra của chúng trên một trang tính của một danh sách riêng hoặc trong phạm vi nguồn.Phương pháp 1: Áp dụng AutoFilt mở rộng
Cách dễ nhất để lựa chọn là việc sử dụng AutoFilter mở rộng. Hãy xem xét làm thế nào để làm điều này trên một ví dụ cụ thể.
- Chọn khu vực trên trang tính, trong số các dữ liệu bạn muốn tạo một mẫu. Trong tab Trang chủ, nhấp vào nút "sắp xếp và lọc". Nó được đặt trong khối cài đặt chỉnh sửa. Trong danh sách mở sau danh sách này, nhấp vào nút "Bộ lọc".
Có một cơ hội để làm và khác nhau. Để thực hiện việc này, sau khi chọn khu vực trên trang tính, chúng tôi chuyển sang tab "Dữ liệu". Nhấp vào nút "Bộ lọc", được đặt trên băng trong nhóm sắp xếp và bộ lọc.
- Sau hành động này trong tiêu đề bảng, chữ tượng hình xuất hiện để bắt đầu lọc dưới dạng cạnh của các hình tam giác nhỏ ở cạnh phải của các ô. Nhấp vào biểu tượng này trong tiêu đề của cột đó, theo đó chúng tôi muốn tạo một mẫu. Trong menu đang chạy menu, hãy đi qua mục "Bộ lọc văn bản". Tiếp theo, chọn vị trí "Bộ lọc có thể tùy chỉnh ...".
- Cửa sổ lọc người dùng được kích hoạt. Trong đó, bạn có thể đặt giới hạn mà lựa chọn sẽ được thực hiện. Trong danh sách thả xuống cho một cột của ô định dạng số, ví dụ chúng ta sử dụng, bạn có thể chọn một trong năm loại điều kiện:
- bằng nhau;
- không công bằng;
- hơn;
- nhiều hơn hoặc bằng nhau;
- nhỏ hơn.
Chúng ta hãy đặt điều kiện làm ví dụ để chỉ chọn các giá trị mà số lượng doanh thu vượt quá 10.000 rúp. Chúng tôi thiết lập một công tắc cho vị trí "nhiều hơn". Trong trường phù hợp với giá trị "10.000". Để thực hiện hành động, nhấp vào nút "OK".
- Như chúng ta thấy, sau khi lọc, chỉ các dòng vẫn còn, trong đó số lượng doanh thu vượt quá 10.000 rúp.
- Nhưng trong cùng một cột, chúng ta có thể thêm điều kiện thứ hai. Để làm điều này, hãy quay lại cửa sổ lọc tùy chỉnh. Như chúng ta có thể thấy, ở phần dưới của nó có một điều kiện chuyển đổi khác và trường tương ứng để đầu vào. Bây giờ chúng ta hãy cài đặt đường viền chọn cao hơn 15.000 rúp. Để thực hiện việc này, hãy đặt công tắc sang vị trí "ít hơn" và trong trường bên phải phù hợp với giá trị "15000".
Ngoài ra, vẫn còn một sự chuyển đổi của các điều kiện. Anh ta có hai điều khoản "và" và "hoặc". Theo mặc định, nó được cài đặt ở vị trí đầu tiên. Điều này có nghĩa là chỉ các dòng sẽ vẫn còn trong mẫu đáp ứng cả hai hạn chế. Nếu nó được đặt thành "hoặc" vị trí, thì các giá trị phù hợp với bất kỳ điều kiện nào trong hai điều kiện sẽ vẫn còn. Trong trường hợp của chúng tôi, bạn cần đặt công tắc sang vị trí và vị trí, đó là để lại cài đặt mặc định này. Sau khi tất cả các giá trị được nhập, nhấp vào nút OK.
- Bây giờ chỉ là các dòng duy trì trong bảng, trong đó số tiền của doanh thu không ít hơn 10.000 rúp, nhưng không vượt quá 15.000 rúp.
- Tương tự, bạn có thể định cấu hình các bộ lọc trong các cột khác. Có thể duy trì việc lọc và các điều kiện trước đó được đặt trong các cột. Vì vậy, hãy xem cách thực hiện lựa chọn bằng cách sử dụng bộ lọc cho định dạng ngày. Nhấp vào biểu tượng lọc trong cột tương ứng. Luôn bằng cách nhấp vào danh sách "Bộ lọc theo ngày" và bộ lọc.
- Cửa sổ chạy tự động tùy chỉnh bắt đầu lại. Thực hiện lựa chọn kết quả trong bảng từ 4 đến 6 tháng 5 năm 2016 bao gồm. Trong công tắc lựa chọn, như bạn có thể thấy, thậm chí nhiều tùy chọn hơn so với định dạng số. Chọn vị trí "sau hoặc bằng". Trong lĩnh vực bên phải, đặt giá trị "04.05.2016". Trong khối dưới cùng, chúng tôi đặt công tắc đến vị trí "tối đa hoặc bằng". Trong trường phù hợp, nhập giá trị "ngày 06.05.2016". Công tắc tương thích điều kiện để lại ở vị trí mặc định - "và". Để áp dụng lọc trong hành động, nhấn nút "OK".
- Như bạn có thể thấy, danh sách của chúng tôi đã từ chối nhiều hơn nữa. Bây giờ chỉ còn lại dòng trong đó, trong đó số tiền doanh thu thay đổi từ 10.000 đến 15.000 rúp trong khoảng thời gian từ 04.05 đến 06.05.2016.
- Chúng tôi có thể đặt lại lọc trong một trong các cột. Hãy làm điều đó cho các giá trị doanh thu. Nhấp vào biểu tượng AutoFilter trong cột tương ứng. Trong danh sách thả xuống, nhấp vào mục "Xóa bộ lọc".
- Như bạn có thể thấy, sau những hành động này, mẫu của số tiền thu sẽ bị vô hiệu hóa, nhưng sẽ chỉ nhận lựa chọn theo ngày (từ 04/05/2016 đến 06.05.2016).
- Bảng này có một cột khác - "tên". Nó chứa dữ liệu ở định dạng văn bản. Chúng ta hãy xem cách tạo thành một mẫu bằng cách sử dụng Lọc thông qua các giá trị này.
Nhấp vào biểu tượng bộ lọc trong tên của cột. Luôn luôn truy cập tên của danh sách "Bộ lọc văn bản" và "Bộ lọc có thể tùy chỉnh ...".
- Cửa sổ AutoFilter của người dùng sẽ mở lại. Hãy tạo một mẫu bằng tên của "khoai tây" và "thịt". Trong khối đầu tiên, công tắc điều kiện được đặt thành vị trí "bằng". Trong lĩnh vực bên phải của nó phù hợp với từ "khoai tây". Việc chuyển đổi của đơn vị thấp hơn cũng sẽ đặt vị trí "bằng". Trong lĩnh vực đối diện, tôi thực hiện một bản ghi - "thịt". Và bây giờ chúng tôi thực hiện những gì họ chưa từng có: Đặt công tắc tương thích sang vị trí "hoặc". Bây giờ dòng chứa bất kỳ điều kiện được chỉ định nào sẽ được hiển thị trên màn hình. Nhấp vào nút "OK".
- Như bạn có thể thấy, có những hạn chế vào ngày trong mẫu mới (từ 04.05.2016 đến 06.05.2016) và theo tên (khoai tây và thịt). Không có hạn chế về số lượng doanh thu.
- Bạn hoàn toàn có thể xóa bộ lọc bằng cùng các phương thức được sử dụng để cài đặt nó. Và nó không quan trọng phương pháp nào đã được sử dụng. Để đặt lại bộ lọc, trong khi trong tab "Dữ liệu", nhấp vào nút "Bộ lọc", nằm trong nhóm "Sắp xếp và Bộ lọc".
Tùy chọn thứ hai liên quan đến việc chuyển đổi sang tab "Trang chủ". Chúng tôi thực hiện nhấp chuột vào ruy-băng trên nút "sắp xếp và lọc" trong thiết bị chỉnh sửa. Trong danh sách kích hoạt, nhấp vào nút "Bộ lọc".
Khi sử dụng bất kỳ trong số hai phương thức trên, việc lọc sẽ bị xóa và kết quả mẫu được làm sạch. Đó là, bảng sẽ được hiển thị toàn bộ mảng dữ liệu mà nó có.
Bài học: Chức năng lọc tự động trong Excel
Phương pháp 2: Ứng dụng của công thức mảng
Làm cho lựa chọn cũng có thể áp dụng công thức phức tạp của mảng. Ngược lại với phiên bản trước, phương thức này cung cấp cho đầu ra của kết quả trong một bảng riêng biệt.
- Trên cùng một tờ, chúng ta tạo một bảng trống có cùng tên các cột trong tiêu đề là nguồn.
- Phân bổ tất cả các ô trống của cột đầu tiên của bảng mới. Cài đặt con trỏ trong chuỗi công thức. Chỉ cần ở đây sẽ được nhập bởi một công thức tạo ra một mẫu theo tiêu chí được chỉ định. Chúng tôi sẽ chọn dòng, số lượng doanh thu trong đó vượt quá 15.000 rúp. Trong ví dụ cụ thể của chúng tôi, công thức được giới thiệu sẽ trông như sau:
= Index (A2: A29; nhỏ nhất (nếu (15000
Đương nhiên, trong mỗi trường hợp cụ thể, địa chỉ của các tế bào và phạm vi sẽ là của bạn. Trong ví dụ này, bạn có thể so sánh công thức với tọa độ trên hình minh họa và điều chỉnh nó cho nhu cầu của bạn.
- Vì đây là một công thức mảng, để áp dụng nó hoạt động, bạn cần nhấn nút ENTER, nhưng tổ hợp phím Ctrl + Shift + Enter. Chúng tôi làm điều đó.
- Có tô sáng cột thứ hai với ngày và cài đặt con trỏ trong chuỗi công thức, chúng tôi giới thiệu biểu thức sau:
= Index (B2: B29; nhỏ nhất (nếu (15000
Nhấp vào Ctrl + Shift + Enter Tổ hợp phím.
- Tương tự, trong một cột có doanh thu, nhập công thức của nội dung sau:
= Index (C2: C29; nhỏ nhất (nếu (15000
Một lần nữa, nhập tổ hợp phím Ctrl + Shift + Enter.
Trong cả ba trường hợp, chỉ có giá trị đầu tiên của tọa độ đang thay đổi, và phần còn lại của công thức hoàn toàn giống hệt nhau.
- Như chúng ta thấy, bảng chứa đầy dữ liệu, nhưng sự xuất hiện của nó không hoàn toàn hấp dẫn, hơn nữa, các giá trị của ngày được điền vào nó không chính xác. Nó là cần thiết để sửa những thiếu sót này. Sự không chính xác của ngày liên quan đến thực tế là định dạng của các ô của cột tương ứng là phổ biến và chúng ta cần đặt định dạng ngày. Chúng tôi tô sáng toàn bộ cột, bao gồm các ô có lỗi và nhấp vào tô sáng nút chuột phải. Trong danh sách xuất hiện, hãy đi qua "Định dạng ô" ".
- Trong cửa sổ định dạng mở, hãy mở tab "Số". Trong khối "định dạng số" phân bổ giá trị "ngày". Ở bên phải của cửa sổ, bạn có thể chọn loại hiển thị ngày mong muốn. Sau khi cài đặt được trưng bày, nhấp vào nút "OK".
- Bây giờ ngày được hiển thị chính xác. Nhưng, như chúng ta thấy, toàn bộ đáy của bảng chứa đầy các ô chứa giá trị sai số "# Số!". Về bản chất, đây là những tế bào, dữ liệu từ mẫu mà chúng không có đủ. Sẽ hấp dẫn hơn nếu chúng được hiển thị hoàn toàn trống rỗng. Đối với những mục đích này, chúng tôi sử dụng định dạng có điều kiện. Chúng tôi nhấn mạnh tất cả các tế bào bàn, ngoại trừ tiêu đề. Trong khi trong tab Trang chủ, nhấp vào nút "Định dạng có điều kiện", nằm trong khối công cụ "Styles". Trong danh sách xuất hiện, chọn mục "Tạo quy tắc ...".
- Trong cửa sổ mở ra, chọn loại các ô chỉ định dạng "quy tắc chứa". Trong trường đầu tiên, dưới định dạng dòng chữ "chỉ định dạng các ô mà các ô sau," chọn vị trí "lỗi". Tiếp theo, nhấp vào nút "Định dạng ...".
- Trong cửa sổ định dạng chạy, đi đến tab "phông chữ" và chọn màu trắng trong trường thích hợp. Sau những hành động này, nhấp vào nút "OK".
- Trên nút có cùng tên, nhấp sau khi quay lại cửa sổ Tạo Tiến hành.
Bây giờ chúng tôi có một mẫu hoàn thành trên giới hạn quy định trong một bảng được trang trí đúng cách riêng biệt.
Bài học: Định dạng có điều kiện trong Excel
Phương pháp 3: Lấy mẫu trên một số điều kiện bằng cách sử dụng công thức
Giống như khi sử dụng bộ lọc, sử dụng công thức bạn có thể chọn trong một số điều kiện. Ví dụ: lấy cùng một bảng nguồn, cũng như một bảng trống, trong đó kết quả sẽ là đầu ra, với định dạng số và có điều kiện đã được thực hiện. Chúng tôi sẽ thiết lập giới hạn đầu tiên của biên giới thấp hơn của việc lựa chọn bằng doanh thu trong 15.000 rúp và điều kiện thứ hai của biên giới trên của 20.000 rúp.
- Nhập vào một cột riêng, điều kiện biên để lấy mẫu.
- Như trong phương thức trước, cách nhau phân bổ các cột trống của bảng mới và nhập ba công thức tương ứng trong đó. Trong cột đầu tiên, chúng tôi giới thiệu biểu thức sau:
= Chỉ mục (A2: A29; Nhỏ nhất (nếu ((($ D $ 2 = C2: C29); Dòng (C2: C29); ""); Chuỗi (C2: C29) -strkok ($ C $ 1)) - Dòng ($ C $ 1))
Trong các cột tiếp theo, phù hợp với các công thức giống hệt nhau, chỉ bằng cách thay đổi tọa độ ngay sau tên của toán tử, chỉ mục đến các cột tương ứng chúng ta cần, bằng cách tương tự với phương thức trước đó.
Mỗi lần sau khi nhập, đừng quên kết hợp phím Ctrl + Shift + Enter.
- Ưu điểm của phương pháp này trước đó là nếu chúng ta muốn thay đổi ranh giới của mẫu, điều này sẽ không cần thiết phải thay đổi chính công thức rắn, trong đó chính nó là khá có vấn đề. Nó là đủ trong cột các điều kiện trên trang tính để thay đổi các số ranh giới trên những người dùng cần thiết bởi người dùng. Kết quả của lựa chọn sẽ ngay lập tức tự động thay đổi.
Phương pháp 4: Lấy mẫu ngẫu nhiên
Trong exile, với sự trợ giúp của một công thức đặc biệt, cũng có thể áp dụng lựa chọn ngẫu nhiên. Nó là cần thiết trong một số trường hợp khi làm việc với một lượng lớn dữ liệu khi bạn cần xuất trình một hình ảnh chung mà không cần phân tích toàn diện về tất cả các dữ liệu của mảng.
- Ở bên trái của bảng Bỏ qua một cột. Trong ô của cột tiếp theo, nằm đối diện với ô đầu tiên với dữ liệu của bảng, hãy nhập công thức:
= Keo dính ()
Tính năng này hiển thị một số ngẫu nhiên. Để kích hoạt nó, nhấp vào nút ENTER.
- Để tạo toàn bộ một cột số ngẫu nhiên, hãy đặt con trỏ đến góc dưới bên phải của ô, đã chứa công thức. Các điểm đánh dấu xuất hiện. Tôi kéo dài nó bằng nút chuột trái song song với bảng với dữ liệu cho đến khi kết thúc.
- Bây giờ chúng ta có một loạt các ô chứa đầy các số ngẫu nhiên. Nhưng, anh ta chứa công thức của Calc. Chúng ta cần làm việc với các giá trị sạch sẽ. Để làm điều này, sao chép vào một cột trống ở bên phải. Chọn phạm vi của các ô với các số ngẫu nhiên. Nằm trong tab "Home", nhấp vào biểu tượng "Sao chép" trên ruy-băng.
- Chúng tôi tô sáng một cột trống và nhấp chuột phải vào, gọi menu ngữ cảnh. Trong thanh công cụ "Chèn tham số", chọn mệnh đề "giá trị", được mô tả dưới dạng chữ tượng hình có số.
- Sau đó, trong khi trong tab "Home", nhấp vào biểu tượng đã quen thuộc của biểu tượng "Sắp xếp và Bộ lọc". Trong danh sách thả xuống, hãy dừng lựa chọn ở mục "Sắp xếp tùy chỉnh".
- Cửa sổ cài đặt sắp xếp được kích hoạt. Hãy chắc chắn cài đặt một đánh dấu đối diện với tham số "Dữ liệu của tôi chứa tiêu đề" nếu có nắp có sẵn, nhưng không có dấu tích. Trong trường "Sắp xếp theo", chỉ định tên của cột đó trong đó các giá trị được sao chép của số ngẫu nhiên được chứa. Trong trường "sắp xếp", để lại cài đặt mặc định. Trong trường "Order", bạn có thể chọn tham số là "tăng dần" và "giảm dần". Đối với một mẫu ngẫu nhiên, giá trị này không có. Sau khi cài đặt được sản xuất, nhấp vào nút "OK".
- Sau đó, tất cả các giá trị của bảng được xây dựng theo thứ tự tăng dần hoặc giảm số ngẫu nhiên. Bạn có thể lấy bất kỳ số dòng đầu tiên nào từ bảng (5, 10, 12, 15, v.v.) và chúng có thể được coi là kết quả của một mẫu ngẫu nhiên.
Bài học: Sắp xếp và lọc dữ liệu vào Excel
Như bạn có thể thấy, mẫu trong bảng Excel có thể được tạo, cả hai đều sử dụng AutoFilter và áp dụng các công thức đặc biệt. Trong trường hợp đầu tiên, kết quả sẽ được hiển thị trong bảng nguồn và trong giây thứ hai - trong một khu vực riêng biệt. Có thể tạo ra lựa chọn, cả một điều kiện và một số. Ngoài ra, bạn có thể tạo một mẫu ngẫu nhiên bằng cách sử dụng hàm keo.