将数据从121个文本文件移动到具有特定布局的Excel电子表格中 [英] Move data from 121 text files into an Excel spreadsheet, with particular layout
问题描述
我有121个文本文件,在单独的子目录中,我需要在Excel表格中以某种方式编译的数据。一个示例文本文件:
I have 121 text files, in separate subdirectories, with data that I need compiled in a certain way in an Excel sheet. An example text file:
我需要Excel电子表格(忽略着色):
and how I need the Excel spreadsheet (ignore coloring):
推荐答案
如果您可以将所有文件加载到excell(其中有一些建议),
和格式如下可以看到在 A1:C12
中,您可以将其转换为您在 C1:G31
中看到的格式,方法是使用 COUTIFS
公式:
IF you can get all of your files loaded into excell (advice of which there is aplenty),
and formatted like you can see below in A1:C12
, you can then transform it into a format you see in C1:G31
by using a COUTIFS
formula:
E2:
= SUBSTITUTE(COUNTIFS($ A $ 2: $ A $ 11 E $ 1,$ B $ 2:$ B $ 11 < = &安培; $ D2,$ C $ 2:$ C $ 11 > = &安培; $ D2),0, )
然后拖到 F2
和 G2
,然后填写。
then drag across to F2
and G2
, and then fill down.
A B C D E F G
=======================================================
1 || file range to 1AAPA 1BBCC 1DEFG
2 || 1AAPA 3 5 1 1
3 || 1AAPA 8 12 2 1
4 || 1AAPA 15 26 3 1 1
5 || 1AAPA 28 30 4 1 1
6 || 1BBCC 14 16 5 1 1
7 || 1BBCC 20 22 6
8 || 1BBCC 25 30 7
9 || 1DEFG 1 5 8 1
10 || 1DEFG 10 13 9 1
11 || 1DEFG 22 26 10 1 1
12 || 11 1 1
13 || 12 1 1
14 || 13 1
15 || 14 1
16 || 15 1 1
17 || 16 1 1
18 || 17 1
19 || 18 1
20 || 19 1
21 || 20 1 1
22 || 21 1 1
23 || 22 1 1 1
24 || 23 1 1
25 || 24 1 1
26 || 25 1 1 1
27 || 26 1 1 1
28 || 27 1
29 || 28 1 1
30 || 29 1 1
31 || 30 1 1
这篇关于将数据从121个文本文件移动到具有特定布局的Excel电子表格中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!