登录查看更多内容

How to use the SORTN Function in Google Sheets

工程关注我们，每天学习??

?? Learning & Engineering

发布日期: 2024年3月19日

Returns the first n items in a data set after performing a sort.

The SORTN function in Google Sheets allows users to sort a range by one or more columns and return the top N items from the sorted range. This function is especially useful for extracting a subset of data based on specific criteria, such as the top sales, the most recent entries, or any other ranked data.

??Purchase our book to improve your Excel productivity

Benefits of Using the SORTN Function

Data Analysis Enhancement: Quickly isolate the top-performing items or most recent entries for analysis.
Reporting Efficiency: Simplify the creation of reports by dynamically pulling the most relevant data.
Flexible Sorting: Offers the ability to sort by multiple columns and specify ascending or descending order for each.
Handling Ties: Customize how ties are displayed, ensuring the output meets your specific needs.

Step-by-Step Guide

Step 1: Understanding the Syntax

SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], ...)range: The range of cells to sort.n (optional): The number of items to return.display_ties_mode (optional): Determines how ties are handled.sort_column1, is_ascending1, ... (optional): One or more pairs of sort column indexes and whether to sort them in ascending (TRUE) or descending (FALSE) order.

Step 2: Preparing Your Data

Ensure your dataset is well-organized, with each column containing uniform data types (e.g., all text, all numbers) for accurate sorting.

Step 3: Applying the SORTN Function

Select the Output Cell: Click on the cell where you want the sorted list to begin.
Input the Function: Enter =SORTN(, followed by the necessary parameters based on your data and sorting needs.

??Purchase our book to improve your Excel productivity

Example

Scenario: Monthly Sales Analysis for Top Performing Products

Imagine you're a sales analyst at a retail company, and you've been tasked with identifying the top 3 selling products each month, considering both sales quantity and revenue. Additionally, it's crucial to include any ties for the third place to ensure all top performers are recognized.

Data Setup

Your dataset includes Product IDs in column A, Sales Quantity in column B, and Total Revenue in column C, spanning from rows 2 to 200.
Column headers are in row 1: Product ID, Sales Quantity, and Total Revenue.

Objective

Create a report in another part of the sheet (or in a new sheet) that dynamically lists the top 3 products by Total Revenue, including ties for the third spot, and secondarily sorts by Sales Quantity if revenues are equal.

Step-by-Step Implementation

Step 1: Organize Your Data

Ensure your data is cleanly formatted with no blank rows or columns within the range being analyzed.
Confirm that the data types are consistent in each column (text for Product IDs, numbers for Sales Quantity and Total Revenue).

Step 2: Apply the SORTN Function

Select the output cell where you want your analysis to start, let's say cell E2.
Input the SORTN function with the necessary parameters to sort by Total Revenue and include ties:

=SORTN(A2:C200, 3, 2, C2:C200, FALSE, B2:B200, FALSE)

This formula breaks down as follows:A2:C200 is the range containing your data.3 instructs SORTN to return the top 3 items.2 as the display_ties_mode ensures that ties for the third spot are included.C2:C200, FALSE sorts the range by Total Revenue in descending order.B2:B200, FALSE adds a secondary sort by Sales Quantity in descending order, to be used in case of revenue ties.

领英推荐

Filter contains one of many in Excel

工程关注我们，每天学习?? 11 个月前

How to use the Match Function in Google Sheets

工程关注我们，每天学习?? 11 个月前

FILTER with complex multiple criteria in Excel

工程关注我们，每天学习?? 9 个月前

Step 3: Analyze the Results

In cell E1, G1, and H1, you might label the output columns as Top Products, Quantity Sold, and Revenue, respectively.
After pressing 'Enter', cells E2 downwards will list the top 3 (or more, if there are ties) products by Total Revenue, including their Sales Quantity and Total Revenue, sorted secondarily by the quantity if necessary.

Advanced Tips

Dynamic n Value: Use cell references or formulas to dynamically change the n value based on conditions in your spreadsheet.
Multiple Sort Criteria: Extend the formula to include additional sort criteria. For example, to first sort by date and then by amount, add the corresponding columns and sort orders to the formula.
Customizing Output for Reporting: Combine SORTN with QUERY or FILTER for more customized data retrieval, such as adding headers or filtering based on additional criteria.
Error Checking: Incorporate IFERROR around your SORTN function to handle any potential errors gracefully, especially when working with dynamic or volatile data ranges.