编译不带宏的不同Excel表格 [英] Compiling Different Excel sheets WITHOUT a macro

问题描述

我有4张不同的表格，其数据类似，但格式不同。例如：

I have 4 different sheets with similar data, but in different formats. As an example:

工作表A

Date        Buy-In     Game    Winnings
11/25/2013  $10        NFL     $18
11/28/2013  $10        NBA     $0

表B

Sport       Buy-In     Date         Winnings
NFL         $5         11/26/2013   $9
NBA         $2         11/29/2013   $3.60

表C

Buy-In      Game     Date         Winnings
$5          NFL      11/24/2013   $9
$2          NFL      11/21/2013   $3.60

Sport       Buy-In     Date         Winnings
NFL         $5         11/20/2013   $9
NBA         $2         11/22/2013   $3.60

我想将它们组合成一张 / strong>使用宏。因此，组合表格将如下所示：

I want to combine them into one sheet without using a macro. So the combined sheet would look like this:

组合表

Game      Date        Buy-In      Winnings
NFL       11/20/2013  $5          $9
NFL       11/21/2013  $2          $3.60
NBA       11/22/2013  $2          $3.60
NFL       11/24/2013  $5          $9
NFL       11/25/2013  $10         $18
NFL       11/26/2013  $5          $9
NBA       11/28/2013  $10         $0
NBA       11/29/2013  $2          $3.60

这是可行还是可行？

推荐答案

您可以使用 INDIRECT 函数和支持表！

You can do this using the INDIRECTfunction and a support table!

首先，您需要构建一个保留每个输入表参数的小型支持表：

First you need to build a small support table that keeps the parameters for each input sheet:

• 在列B中，使用以下公式确定数字的行： = COUNTA（INDIRECT（'& A5&！A：A）） - 1 。


• 对于范围C5：F8，我使用公式 = MATCH（C $ 4，INDIRECT（'& $ A5&！ ），0） - 这将适用于具有正确列名称的所有列 - 只需要手动输入C6和C8，因为您在此使用运动而不是游戏

• In column B, use the following formula to determine the number of rows: =COUNTA(INDIRECT("'"&A5&"'!A:A"))-1.
• For the range C5:F8, I used the formula =MATCH(C$4,INDIRECT("'"&$A5&"'!1:1"),0) - this will work for all columns that have the "proper" column name - only C6 and C8 needed to be entered manually, as you used "Sport" instead of "Game" here

根据此支持表，您可以构建合并表。这有两个部分 - 再次3个支持列来确定工作表和行号 - 和数据列：

Based on this support table, you can build your consolidation table. This has two sections - again 3 support columns to determine the sheet and the row number - and the data columns:

使用以下公式：

• 列H： = IF（ISTEXT（H4），1，IF（I5 = 1，H4 + 1，H4））逻辑：从1开始，每次增加1行ID重置为一个，否则保留上面的表格ID


• 列I： = IF（ISTEXT（I4），1，IF（I4 = 1，I4 + 1））逻辑：从1开始并增加1，直到上面行中的行ID等于上面的表格中的行数。在这种情况下，重新启动1
  
  
  
  
  • 列J： = INDEX（$ B $ 5：$ B $ 8，H5） - 从配置表中获取当前工作表的行数
  
  
  • 列K：N： = OFFSET（INDIRECT（'&指数（$ A $ 5：$ A $ 8，$ H5）&'！A1），$ I5，INDEX（C $ 5：C $ 8，$ H5）-1） - 这是魔法发生了！ ;-)逻辑：从配置表中获取工作表名称，在INDIRECT函数中使用该名称从该工作表中检索单元格A1。然后从支持列中的行ID偏移，并从配置表中再次检索该表的列ID。
  
  
  • Column H: =IF(ISTEXT(H4),1,IF(I5=1,H4+1,H4)) Logic: start with 1 and increase by 1 every time the row Id is reset to one - else keep the sheet ID from above
  • Column I: =IF(ISTEXT(I4),1,IF(I4=J4,1,I4+1)) Logic: start at 1 and increase by 1 until row ID in the row above is equal to the number of rows in the sheet from above. In the case, restart at 1
    • Column J: =INDEX($B$5:$B$8,H5) - get the number of rows for the current sheet from the config table
    • Column K:N: =OFFSET(INDIRECT("'"&INDEX($A$5:$A$8,$H5)&"'!A1"),$I5,INDEX(C$5:C$8,$H5)-1) - This is where the magic happens! ;-) Logic: Get the sheet name from the config table, use this in the INDIRECT function to retrieve the cell A1 from that sheet. Then offset by the row ID from the support column - and by the column ID for that sheet retrieved again from the config table.

    查看此文件！

    这篇关于编译不带宏的不同Excel表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！