Excel 2010 - 仅在SUBTOTAL单元格中计数唯一值 [英] Excel 2010 - Count Unique values only in a SUBTOTAL cell
问题描述
信息: Excel 2010(无宏)
数据Origns: MySQL查询/ phpMyAdmin 服务器 br>
代码通过以下方式运行:服务器SQL查询(复制并粘贴在phpMyAdmin中)或MySQL Workbench中或使用自定义购物车经理。
导出到: Excel(.csv然后到销售报告的.xlsx)
注意:工作簿使用我的查询以及其他数据
Info: Excel 2010 (no macros)
Data Origns: MySQL Query / phpMyAdmin Server: Apache
Code is run via: Server SQL Query (copy & paste in the phpMyAdmin) or in MySQL Workbench or using a custom shopping cart manager.
Exports to: Excel (.csv then to .xlsx for sales reports)
Notes: The workbook uses my Query as well as other data
问题
我有2个公式。 1工作,1是接近但最终错误。
1)获取唯一OrderID的总数(在我的情况下是2342),但是当我过滤我的时候不会减少到SUBTOTAL数据。
= SUMPRODUCT((A8:A30001<>)/ COUNTIF(A8:A30001,A8:A30001&))
= SUM(IF(FREQUENCY(IF(LEN(A8:A10001))> 0,MATCH(A8:A10001,A8:A10001, 0),),IF(LEN(A8:A10001)> 0,MATCH(A8:A10001,A8:A10001,0),))> 0,1))
(SHIFT + CTRL + ENTER)
=SUM(IF(FREQUENCY(IF(LEN(A8:A10001)>0,MATCH(A8:A10001,A8:A10001,0),""), IF(LEN(A8:A10001)>0,MATCH(A8:A10001,A8:A10001,0),""))>0,1))
(SHIFT+CTRL+ENTER)
2)返回为 SUBTOTAL
但是计数为1(返回8 ,不是7):
= SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A7,ROW(A7:A10000)-ROW(A7),, 1) ),IF(A7:A10000 - ,MATCH(〜& A7:A10000,A7:A10000,0))),ROW(A7:A10000)-ROW(A7)+1) ,1))
(SHIFT + CTRL + ENTER)
2) Returns as SUBTOTAL
however the count is out by 1 (Returns 8, not 7):
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A7,ROW(A7:A10000)-ROW(A7),,1)), IF(A7:A10000<>"",MATCH("~"&A7:A10000,A7:A10000&"",0))),ROW(A7:A10000)-ROW(A7)+1),1))
(SHIFT+CTRL+ENTER)
= SUM( - (FREQUENCY(IF(SUBTOTAL (A7:A10000,A7:A10000,ROW(A7:A10000))-1,0,1)),COUNTIF(A7:A10000,& A7:A10000) ,),COUNTIF(A7:A10000,&l t& A7:A10000))> 0))
(SHIFT + CTRL + ENTER)
=SUM(--(FREQUENCY(IF(SUBTOTAL(3, OFFSET(A7:A10000, MATCH(ROW(A7:A10000), ROW(A7:A10000))-1, 0, 1)), COUNTIF(A7:A10000, "<"&A7:A10000), ""), COUNTIF(A7:A10000, "<"&A7:A10000))>0))
(SHIFT+CTRL+ENTER)
strong>
这已被过滤:订单月份,产品ID = 7单位
Data
This has been filtered: Month of Order, Products ID = 7 Units
OrdID ProdName Qty
2525 test 1
2538 test 1
2553 test 1
2555 test 1
2556 test 1
2564 test 1
2566 test 1
提前感谢您的见解。
推荐答案
我希望在 2)中的公式可以正常工作,你确定结果的差异不是由不同的范围引起的 - 你的第一个公式从第8行开始,第二个在第7行开始?你是否在数据中包含标题行,因此8是结果而不是7?
I would expect formula in 2)
to work OK, are you sure the discrepancy in results isn't caused by different ranges - your first formula starts at row 8 and the second at row 7? Are you including the header row in the data hence 8 as the result rather than 7?
如果列A中的订单ID是数字的,那么这个版本也应该工作
If order Ids in column A are numeric then this version should also work
= SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A7,ROW(A7:A10000)-ROW(A7),, 1) ),A7:A10000),A7:A10000),1))
这篇关于Excel 2010 - 仅在SUBTOTAL单元格中计数唯一值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!