Excel技术问询：统计指定数字出现次数及无限Item IDS下的Top10热销商品统计方法

Hey there! Let's break down your two Excel questions one by one with practical, easy-to-follow methods:

1. 统计特定数字的出现次数 #

Here are a few go-to approaches depending on your needs:

• 基础单条件统计：COUNTIF函数
  If you want to count how many times a specific number (like 8) appears in column A, use this formula directly:
  =COUNTIF(A:A, 8)
  For cells that contain the number (e.g., cells with "8th" or "18"), use wildcards:
  =COUNTIF(A:A, "*8*")

• Range/multi-condition counting: COUNTIFS or nested COUNTIF
  To count numbers in column A that are greater than 5 and less than 15, use COUNTIFS:
  =COUNTIFS(A:A, ">5", A:A, "<15")
  Alternatively, subtract two COUNTIF results:
  =COUNTIF(A:A, "<15") - COUNTIF(A:A, "<=5")

• Visual quick count: PivotTable
  Select your data column, insert a PivotTable, drag the field to both the "Rows" and "Values" areas (set Values to "Count"). You'll see a full breakdown of all numbers' frequencies, and can filter for specific ones instantly.

Pro tip: If your cells are formatted as text instead of numbers, COUNTIF might miss matches—double-check the cell format first!

2. Find Top 10 Most Purchased Items with Unlimited Item IDs #

When dealing with tons of unique item names, manual COUNTIF is impossible. Try these efficient methods:

Method 1: PivotTable (works for all Excel versions) #

This is the most reliable option for any Excel version:

• Select all your purchase data (including headers)
• Click Insert → PivotTable and choose where to place it (e.g., a new worksheet)
• In the PivotTable Fields pane:
  • Drag "Item IDS" to the "Rows" area
  • Drag your purchase volume field (e.g., "Quantity") to the "Values" area—use "Count" if each row is one purchase, or "Sum" if you have a quantity column
• Click the "Item IDS" header in the PivotTable, select Sort → Sort Z to A, then just grab the top 10 rows

Method 2: Dynamic Array Functions (Excel 365/2021+) #

Use a combination of UNIQUE, COUNTIF, SORT, and TAKE for a one-step, auto-updating solution. Assuming Item IDS are in column B:

• Extract all unique item IDs: =UNIQUE(B:B)
• Count purchases per ID: =COUNTIF(B:B, UNIQUE(B:B)) (swap with SUMIF(B:B, UNIQUE(B:B), C:C) if you need to sum quantities from column C)
• Combine, sort, and take top 10 with one formula:
  =TAKE(SORT(HSTACK(UNIQUE(B:B), COUNTIF(B:B, UNIQUE(B:B))), 2, -1), 10)
  This will output the top 10 items and their counts automatically, updating whenever your data changes.

Note: If you're on an older Excel version without dynamic arrays, stick to the PivotTable—it's simple and foolproof!

内容的提问来源于stack exchange，提问作者Thodoris