按公式对Excel数据进行排序 [英] sorting excel data formulaicly

问题描述

利用我组织的组策略设置，强制用户明确单击以启用Office文件中的宏，我希望使用vba来实现相同的结果(而不必单击任何按钮来作为额外的奖励).数据透视表也是不可能的.

我有一个工作表，其位置为列标题(A1:E1)，事件为行标题(A2:A50).剩余的单元格(B2:E50)中填充有表示何时举行每个位置的下一个事件的日期.并非所有地点都为每个事件服务，因此可以使用"N/A".活动可以在同一天的同一地点举行.

事件数据:

我的目标是提供数据的辅助视图，以显示每个位置(列标题)下当前和下个月即将发生的事件(作为行条目).这(相对)很简单，只需向上和向下复制一个数组公式即可.

公式:=IFERROR(INDEX(Master!$A$6:$A$15,SMALL(IF(Master!B$6:B$15<>"N/A",IF((Master!B$6:B$15<DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))*(Master!B$6:B$15>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)),ROW(Master!B$6:B$15)-5)),ROWS(A$3:A3))),"")

输出:

但是，事件是按它们在主表中显示的顺序列出的，而不是按我希望的时间顺序排列.我在每个事件旁边添加了一个日期列，但这不是可排序的，因为数组公式使所有内容保持动态.

所需的输出摘录:

Tampa   
19-Jan-15   Intermediate I
24-Jan-15   Introduction
26-Jan-15   Beginner I

解决方案

在即将上线的工作表中，如果A1中的January实际上是一个日期，即相关月份的第一天，那么会更容易.案例2015年1月1日.

如果需要，您可以将A1格式自定义为"mmmm"以仅显示January或"mmmm-yy"以显示January-15

现在在A3中使用此公式可按顺序获取有关月份和城市的日期(无论是否重复)都可以使用:

=IFERROR(SMALL(IF(Master!B$6:B$15>=$A$1,IF(Master!B$6:B$15<=EOMONTH($A$1,0),Master!B$6:B$15)),ROWS(A$3:A3)),"")

已通过 CTRL + SHIFT + ENTER 确认并向下复制了列

注意:您不必在公式中明确排除"N/A"文本值-无论如何这些值都会被忽略

现在，在B3中，如果您没有特定城市的重复日期，则可以使用此非数组公式:

=IF(A3="","",INDEX(Master!$A$6:$A$15,MATCH(A3,Master!B$6:B$15,0)))

如果您可能有重复的日期，请使用此数组公式"代替说明

=IF(A3="","",INDEX(Master!$A$6:$A$15,SMALL(IF(A3=Master!B$6:B$15,ROW(Master!$A$6:$A$15)-ROW(Master!$A$6)+1),COUNTIF(A$3:A3,A3))))

with my organization's group policy settings forcing users to explicitly click to enable macros in office files, i'd like a workaround to using vba to achieve the same result (and without having to click anything as an added bonus). pivot tables are also out of the question.

i have a sheet with locations as column headings (A1:E1), and events as row headings (A2:A50). the remaining cells (B2:E50) are populated with dates representing when the next event at each location will be held. not all locations serve each event, so "N/A"s are possible. events may be held at the same location on the same day.

Event data:

my goal is to offer a secondary view of the data showing the current and next month's upcoming events (as row entries) beneath each location (column heading). this is (relatively) simple enough with an array formula copied over and down.

formula: =IFERROR(INDEX(Master!$A$6:$A$15,SMALL(IF(Master!B$6:B$15<>"N/A",IF((Master!B$6:B$15<DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))*(Master!B$6:B$15>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)),ROW(Master!B$6:B$15)-5)),ROWS(A$3:A3))),"")

Output:

however, the events are listed as they appear in the master sheet, not chronologically as i'd prefer. i added a date column next to each event, but this is not sortable, as the array formula keeps all the content dynamic.

desired output excerpt:

Tampa   
19-Jan-15   Intermediate I
24-Jan-15   Introduction
26-Jan-15   Beginner I

解决方案

In Upcoming worksheet it's easier if January in A1 is actually a date, the 1st of the relevant month, so in this case 1/1/2015.

You can custom format A1, if required, as "mmmm" to show just January or "mmmm-yy" to show January-15

Now use this formula in A3 to get the dates in order for the month and city in question (works whether you have duplicates or not):

=IFERROR(SMALL(IF(Master!B$6:B$15>=$A$1,IF(Master!B$6:B$15<=EOMONTH($A$1,0),Master!B$6:B$15)),ROWS(A$3:A3)),"")

confirmed with CTRL+SHIFT+ENTER and copied down column

Note: you don't have to explicitly exclude "N/A" text values in the formula - these will be ignored anyway

Now in B3 copied down you can use this non-array formula if you don't ever have duplicate dates for a specific city:

=IF(A3="","",INDEX(Master!$A$6:$A$15,MATCH(A3,Master!B$6:B$15,0)))

If you might have duplicate dates use this "array formula" instead for the description

=IF(A3="","",INDEX(Master!$A$6:$A$15,SMALL(IF(A3=Master!B$6:B$15,ROW(Master!$A$6:$A$15)-ROW(Master!$A$6)+1),COUNTIF(A$3:A3,A3))))

这篇关于按公式对Excel数据进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！