公式排序excel数据 [英] sorting excel data formulaicly

问题描述

我的组织的组策略设置强制用户明确单击以启用办公文件中的宏，我想要一种使用 vba 实现相同结果的解决方法(并且无需单击任何内容作为额外奖励).数据透视表也是不可能的.

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.

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

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.

事件数据:

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

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.

公式:=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)),ROW(Master!B$6:B$15)-5)),ROWS(A$3:A3))),"")

输出:

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

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.

所需的输出摘录:

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

推荐答案

在 Upcoming 工作表中，如果 A1 中的 January 实际上是一个日期，相关的第一个月，因此在本例中为 1/1/2015.

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.

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

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

现在在 A3 中使用这个公式来获取有关月份和城市的日期(无论您是否有重复):

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)),"")

用 CTRL+SHIFT+ENTER 确认并向下复制列

confirmed with CTRL+SHIFT+ENTER and copied down column

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

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

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

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屋！