Cách tìm phân vị nếu có tiêu chí trong Excel
Trong bài viết này, chúng ta sẽ tìm hiểu Cách tìm giá trị phân vị nếu với tiêu chí đã cho trong Excel
Tình huống:
Nói một cách dễ hiểu, khi làm việc với một tập dữ liệu dài rải rác, đôi khi chúng ta cần tìm phần trăm của các con số với một số tiêu chí trên đó.
Ví dụ: tìm phần trăm tiền lương trong một bộ phận cụ thể hoặc có nhiều tiêu chí theo ngày tháng, tên, bộ phận hoặc thậm chí có thể đánh số dữ liệu như lương dưới giá trị hoặc số lượng trên giá trị.
Đối với điều này, bạn phải trích xuất các số được yêu cầu theo cách thủ công và sau đó tính toán phần trăm của một mảng với các tiêu chí. Sử dụng hàm IF với công thức mảng.
Làm thế nào để giải quyết vấn đề?
Bạn phải suy nghĩ làm thế nào điều này có thể thực hiện được, để thực hiện các phép toán logic trên mảng bảng bằng cách sử dụng hàm IF. Hàm IF trong excel rất hữu ích, Nó sẽ giúp bạn vượt qua một số nhiệm vụ khó khăn trong Excel hoặc bất kỳ ngôn ngữ mã hóa nào khác. Hàm IF kiểm tra các điều kiện trên mảng tương ứng với các giá trị bắt buộc và trả về kết quả là mảng tương ứng với điều kiện Đúng là 1 và Sai là 0 Đối với vấn đề này, chúng ta sẽ sử dụng các hàm sau:
Chúng tôi sẽ yêu cầu các chức năng trên và một số cảm giác cơ bản về hoạt động dữ liệu. các điều kiện logic trên mảng có thể được áp dụng bằng các toán tử logic. Các toán tử logic này hoạt động trên cả văn bản và số. Dưới đây là công thức chung. \ {} * dấu ngoặc nhọn là công cụ kỳ diệu để thực hiện các công thức mảng với hàm IF.
Công thức chung:
|
\{* = op crit), percentile_array ), k ) }* |
Lưu ý: Đối với dấu ngoặc nhọn (\ {}) Sử dụng Ctrl + Shift + Enter * khi làm việc với mảng hoặc phạm vi trong Excel. Điều này sẽ tạo ra các Dấu ngoặc nhọn trên công thức theo mặc định. KHÔNG cố gắng mã cứng các ký tự dấu ngoặc nhọn.
range: mảng, trong đó điều kiện áp dụng op: operator, phép toán áp dụng crit: tiêu chí áp dụng trên range percentile_array: array, trong đó phân vị là k: kth percentile (ví dụ: 33% → k = 0.33 giá trị là số)
Ví dụ:
Tất cả những điều này có thể khó hiểu. Vì vậy, hãy kiểm tra công thức này bằng cách chạy nó trên ví dụ được hiển thị bên dưới. Ở đây chúng tôi có dữ liệu của các sản phẩm nhận được từ các khu vực khác nhau cùng với ngày, số lượng và giá cả tương ứng. Ở đây chúng tôi có dữ liệu và chúng tôi cần tìm phân vị thứ 25 hoặc giá trị tương ứng với 25% xem xét các đơn đặt hàng chỉ nhận được từ khu vực “Đông”. Bây giờ bạn đã sẵn sàng để có được kết quả mong muốn bằng cách sử dụng công thức dưới đây.
Sử dụng công thức:
|
\{* = = “East” , D2:D11 ) , 0.25 ) } |
Giải thích:
-
Vùng B2: B11 = “Đông”: kiểm tra các giá trị trong vùng của mảng để khớp với “Đông”.
-
Toán tử logic trả về True cho giá trị đã so khớp và False cho giá trị chưa khớp.
-
Bây giờ hàm IF chỉ trả về các giá trị giá tương ứng với 1s và False như nó vốn có. Dưới đây là phạm vi được trả về bởi hàm IF và nhận được bởi hàm PERCENTILE.
\{ SAI ; 303,63; SAI ; 153,34; SAI ; 95,58; SAI ; SAI ; 177; SAI } . Hàm PERCENTILE trả về giá phân vị 25% (k = 0,25) cho mảng được trả về.
Ở đây, các mảng được cung cấp bằng cách sử dụng tham chiếu ô. Bây giờ giá tương ứng với 25% của mảng được đưa ra bên dưới.
Như bạn có thể thấy, giá sẽ là 138,9, từ ba đơn đặt hàng từ “Đông” có giá trị 303,63, 153,34 và 95,58. Bây giờ, nhận giá trị giá có phân vị khác nhau chỉ bằng cách thay đổi giá trị thứ k thành 0,5 cho 50%, 0,75 cho 75% và 1 cho giá trị phân vị 100%.
Như bạn có thể thấy, chúng tôi có tất cả các giá trị phần trăm tương ứng với các tiêu chí nhất định. Bây giờ sử dụng công thức với giá trị Ngày làm tiêu chí.
phân vị nếu có tiêu chí ngày tháng trong Excel:
Ngày làm tiêu chí là một điều khó khăn trong Excel. Vì Excel lưu trữ các giá trị ngày tháng dưới dạng số. Vì vậy, nếu giá trị ngày tháng không được excel công nhận thì lỗi trả về công thức. Ở đây, chúng tôi cần tìm giá trị phân vị 25% cho các đơn đặt hàng nhận được sau ngày 15 tháng 1 năm 2019.
Sử dụng công thức:
|
\{* = > H3 , D2:D11 ) , 0.25 ) } |
>: toán tử lớn hơn được áp dụng trên mảng ngày.
Ở đây, các mảng được cung cấp bằng cách sử dụng tham chiếu ô. Bây giờ giá tương ứng với 25% của mảng được đưa ra bên dưới.
Như bạn có thể thấy, 90,27 là giá trị phân vị thứ 25 có ngày sau ngày 15 tháng 1 năm 2019. Bây giờ hãy lấy phân vị cho giá trị thứ k khác.
Đây là tất cả các giá trị phần trăm dưới dạng kết quả Đây là tất cả các lưu ý quan sát liên quan đến việc sử dụng công thức.
Ghi chú:
-
Percentile_array trong công thức chỉ hoạt động với các số.
-
KHÔNG dùng dấu ngoặc nhọn mã cứng với công thức.
-
Nếu công thức trả về lỗi #VALUE, hãy kiểm tra xem dấu ngoặc nhọn phải có trong công thức như được hiển thị trong các ví dụ trong bài viết.
-
Các phép toán như bằng (=), nhỏ hơn bằng (⇐), lớn hơn (>) hoặc không bằng (<> *) có thể được thực hiện trong một công thức được áp dụng, chỉ với số.
Hy vọng bài viết này về Cách tìm phân vị nếu với tiêu chí trong Excel là giải thích. Tìm thêm các bài viết về Công thức tính tổng tại đây. Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với những người bạn thân của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected]