英文:
Can I create an excel function that compares 2 values and returns the cell of the larger value
问题
我正在尝试浏览一个非常长的数据列表并快速进行比较。这些数据是关于交货日期的。我有一个数据透视表，其中在顶部列出了一周的各天，而在侧边则列出了商店以及它们的交货量大小。一些商店每周会有多次交货，因此在同一行中会显示两列数字。我试图比较那些有多个交货日期的商店，并查看它们是否真的需要多个交货日期。例如，如果一个商店在星期一下面有100，在星期二下面有20，那么星期二的交货是不必要的，应该合并到星期一。我希望能够创建一个函数，它会检查所有商店，并说，如果一个交货日少于另一个交货日的50％，它们应该合并。理想情况下，这个函数应该返回一个违反这些条件的商店列表，这样我就可以手动查看它们。Excel是否可以实现这个功能，如果不能，我应该查找哪个其他程序？
我已经按商店和交货日的大小将所有数据分开。
例如，商店1和4只有一个交货日，商店6没问题，因为它有两个交货日，但这两天是相等的。但商店2存在问题。
此外，一些商店会有3个交货日，所以这个函数是否还可以检查这些商店，如果这3天中的1天少于最多天的30％，它是否会标记它？
英文:
I am trying to go through a very long list of data and quickly compare. The data is for delivery days. I have a pivot table with the days of the week on the top and the stores with their size of delivery down the side. Some stores get multiple deliveries a week so it would show a number in two columns for the same row. I am trying to compare these stores that have multiple delivery days and see if they actually need multiple delivery days. For example, if a store has a 100 under Monday and 20 under Tuesday, that Tuesday delivery is not necessary and should be combined to Monday. My hope is that I can make a function that looks at all the stores and says, if one delivery day is less than 50% of the other delivery day, they should be combined. Ideally this function would return a list of the stores that violate these parameters so I can go through them manually. Is this something excel can do, and if not, what other program should I look into?
I have all the data broken up by store, and size of delivery categorized by what day of the week it gets deliveries.
So for example, store 1 and 4 have only one delivery, store 6 is ok because it has two delivery days but the days are equivalent. But store 2 is where there is a problem.
Also, some stores get 3 deliveries so could the function also look at these and if 1 of these 3 days has less than 30% of the most day it flags it?
答案1
得分: 1
要获得每家店应该被标记的阈值，您希望取具有最多交付的那一天，并将其除以交付天数。列I中的公式是：
=MAX(B2:G2)/COUNTA(B2:G2)
要标记一家店，您希望检查具有最少交付的那一天是否低于阈值。列J中的公式是：
=MIN(B2:G2)<I2
英文:
To get a threshold for which each store should be flagged, you want to take the day with the most deliveries, and divide it by the number of delivery days. The formula in column I is:
=MAX(B2:G2)/COUNTA(B2:G2)
To flag a store, you want to check if the day with the least deliveries is below the threshold. The formula in column J is:
=MIN(B2:G2)<I2
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论