我正在尝试找到我们团队在此示例数据集的特定日期处理的独特数量的客户（订单），Q（报价）和D（草稿）。请注意，数据集中有 重复 “ quote/quote/quote/order＃”。我需要在给定的一天中找出Q/O/D 的唯一数字。
我已经弄清楚了所有预期输出表中浅橙色的字段以外的所有值。有人可以帮助我按照上述要求的这四个值找出MS Excel公式吗？
以下是给定的数据集。请注意，日期可以有空值。但是这些总是在表的底部几行中找到：
| 日期 |
作业＃ |
作业类型 |
QUOTE/ORDR＃ |
零件 |
客户 |
man-hr |
| 4-apr-22 |
1 |
o |
307585 |
1 |
fru |
0.35 |
| 4-apr-22 |
2 |
D |
307267 |
28 |
AMT |
4.00 |
| 4-APR-22 |
2 |
D |
307267 |
25 |
ATM |
3.75 |
| 4-APR-22 |
2 |
D |
307267 |
6 |
ATM |
0.17 |
| 4-APR-22 |
3 |
D |
307438 |
3 |
ELCTRC |
0.45 |
| 4-APR-22 |
4 |
D |
307515 |
7 |
ATM |
0.60 |
| 4-APR-22 |
4 |
D |
307515 |
5 |
ATM |
0.55 |
| 4-APR- |
22 4 |
D |
307515 |
4 |
ATM |
0.35 |
| 4-APR-22 |
5 |
O |
307587 |
4 |
脉冲 |
0.30 |
| 4-APR-22 |
6 |
O |
307588 |
3 |
PULSE |
0.40 |
| 5-APR-22 |
1 |
O |
307623 |
1 WST 0.45 1 |
WST |
0.45 |
| 5-APR-22 |
2 |
O |
307629 |
4 |
CG |
0.50 |
| 5-APR |
-22 3 |
O |
307630 |
10 |
SUPER |
1.50 |
| 5-APR-22 |
4 |
O |
307631 |
3 |
SUPER |
0.60 |
| 5-APR-22 |
5 |
O |
307640 |
7 |
CAM |
0.40 |
| 5-APR-22 |
6 |
Q |
307527 |
6 |
WG |
0.55 |
| 5-APR-22 |
6 |
Q |
307527 |
3 |
WG |
0.30 |
| 5-APR-22 |
|
|
|
|
|
|
为了找出4月4日的唯一“工作数”，我使用了Excel公式：
= maxifs（$ k $ 3：$ k $ 20，$ j $ 3：$ j $ 20，r3）其中，r3 ='4-apr-22'，
以找出独特的d（草稿） ）作业：“我使用了Excel公式：
= sumifs（$ p $ 3：$ p $ 20，$ j $ 3：$ j $ 20，r3，$ 3 $ 3：$ l $ 20，“ d”）
[
[
2
I am trying to find the unique number of Customers, O (Orders), Q (Quotations) and D (Drafts) our team has dealt with on a particular day from this sample dataset. Please note that there are repeated "Quote/Order #"s in the dataset. I need to figure out the unique numbers of Q/O/D on a given day.
I have figured out all the values except the fields highlighted in light orange color of my Expected output table. Can someone help me figure out the MS Excel formula for these four values as requested above?
Below is the given dataset. Please note that there can be empty values against a date. But those will always be found in the bottom few rows of the table:
| Date |
Job # |
Job Type |
Quote/Ordr # |
Parts |
Customer |
man-hr |
| 4-Apr-22 |
1 |
O |
307585 |
1 |
FRU |
0.35 |
| 4-Apr-22 |
2 |
D |
307267 |
28 |
ATM |
4.00 |
| 4-Apr-22 |
2 |
D |
307267 |
25 |
ATM |
3.75 |
| 4-Apr-22 |
2 |
D |
307267 |
6 |
ATM |
0.17 |
| 4-Apr-22 |
3 |
D |
307438 |
3 |
ELCTRC |
0.45 |
| 4-Apr-22 |
4 |
D |
307515 |
7 |
ATM |
0.60 |
| 4-Apr-22 |
4 |
D |
307515 |
5 |
ATM |
0.55 |
| 4-Apr-22 |
4 |
D |
307515 |
4 |
ATM |
0.35 |
| 4-Apr-22 |
5 |
O |
307587 |
4 |
PULSE |
0.30 |
| 4-Apr-22 |
6 |
O |
307588 |
3 |
PULSE |
0.40 |
| 5-Apr-22 |
1 |
O |
307623 |
1 |
WST |
0.45 |
| 5-Apr-22 |
2 |
O |
307629 |
4 |
CG |
0.50 |
| 5-Apr-22 |
3 |
O |
307630 |
10 |
SUPER |
1.50 |
| 5-Apr-22 |
4 |
O |
307631 |
3 |
SUPER |
0.60 |
| 5-Apr-22 |
5 |
O |
307640 |
7 |
CAM |
0.40 |
| 5-Apr-22 |
6 |
Q |
307527 |
6 |
WG |
0.55 |
| 5-Apr-22 |
6 |
Q |
307527 |
3 |
WG |
0.30 |
| 5-Apr-22 |
|
|
|
|
|
|
To figure out the unique "Number of Jobs" on Apr 4, I used the Excel formula:
=MAXIFS($K$3:$K$20,$J$3:$J$20,R3) Where, R3 ='4-Apr-22'
To figure out the unique "Number of D (Draft) Jobs" I used the Excel formula:
=SUMIFS($P$3:$P$20,$J$3:$J$20,R3,$L$3:$L$20,"D")
[1
[
2
发布评论