如何在工作表中的列之间映射值 [英] How to map values between columns in worksheets

问题描述

我有以下工作表

文件表文件A

请注意，如果您的工作表位于不同的文件中，请将'Cash Flow'！2:2 和'Cash Flow'！1:1 替换为:

  IMPORTRANGE("YourURLString"，'现金流'！2:2") 

和

  IMPORTRANGE("YourURLString"，'现金流'！1:1") 

分别

实际公式:

  = iferror(index(IMPORTRANGE("https://docs.google.com/spreadsheets/d/153trbK2CvrUbEiIF2YZlAhskBq4MqQCckxAxutPOnEQ/edit?usp=sharing"，'Cash Flow'！3:3")，match(A $ 1，IMPORTRANGE("https://docs.google.com/spreadsheets/d/153trbK2CvrUbEiIF2YZlAhskBq4MqQCckxAxutPOnEQ/edit?usp=sharing"，'现金流'！2:2")，0))，0)，0)，0)，0)，0)，0)，0)，) 

---

顺便说一句:HLOOKUP也会做这项工作:

  = IFERROR(HLOOKUP(A $ 1，'Cash Flow'！$ 1:$ 2,2，FALSE)，0) 

请注意，如果工作表位于其他文件中，则将'Cash Flow'！$ 1:$ 2 替换为:

  IMPORTRANGE("YourURLString"，'现金流'！1:2") 

现在您已共享工作表:

  = IFERROR(HLOOKUP(A $ 1，IMPORTRANGE("https://docs.google.com/spreadsheets/d/153trbK2CvrUbEiIF2YZlAhskBq4MqQCckxAxutPOnEQ/edit?usp=sharing"，现金流"！2:3")，2，FALSE)，0) 

---

另一种选择；SUMIF()

  = SUMIF('现金流量'！1:1，A $ 1，'现金流量'！2:2) 

这不适用于Google表格中的交叉文件.

---

我的现金流量供参考:

I have the following worksheets

SPEARDSHEET FILE A https://docs.google.com/spreadsheets/d/153trbK2CvrUbEiIF2YZlAhskBq4MqQCckxAxutPOnEQ/edit?usp=sharing.

SPREADSHEET FILE B

https://docs.google.com/spreadsheets/d/1jjZEXSnskjiuZQdWYqnZGn4jwZS-6RUoY8sGacZq-88/edit?usp=sharing

I want to populate the WORKSHEET B with values from WORKSHEET A that corresponding Month. Note that months with 0 values are omited thus in WORKSHEET A there is no Month 5 or Month 10 by design.

How can I map the values between WORKSHEET B and A?

UPDATE

This is the closest I've come to it which imports the rows form WORKSHEET A into WORKSHEET B and tries to match the columns with the month and then return the values. but does not map it to the appropriate column

=ARRAYFORMULA(HLOOKUP(MATCH(INDEX(ARRAYFORMULA(IMPORTRANGE("153trbK2CvrUbEiIF2YZlAhskBq4MqQCckxAxutPOnEQ","Cash Flow!A1:H3"))),A1:L3),A2:L3,2))

解决方案

A simple INDEX/MATCH will do:

=IFERROR(INDEX('Cash Flow'!2:2,MATCH(A$1,'Cash Flow'!1:1,0)),0)

Put it in the first cell and copy over.

Note if your sheets are in different files replace 'Cash Flow'!2:2 and 'Cash Flow'!1:1 with:

IMPORTRANGE("YourURLString","'Cash Flow'!2:2")

and

IMPORTRANGE("YourURLString","'Cash Flow'!1:1")

respectively

Actual formula:

=iferror(index(IMPORTRANGE("https://docs.google.com/spreadsheets/d/153trbK2CvrUbEiIF2YZlAhskBq4MqQCckxAxutPOnEQ/edit?usp=sharing","'Cash Flow'!3:3"),match(A$1,IMPORTRANGE("https://docs.google.com/spreadsheets/d/153trbK2CvrUbEiIF2YZlAhskBq4MqQCckxAxutPOnEQ/edit?usp=sharing","'Cash Flow'!2:2"),0)),0)

---

BTW: HLOOKUP would do the work also:

=IFERROR(HLOOKUP(A$1,'Cash Flow'!$1:$2,2,FALSE),0)

NOTE if your sheets are in different files replace the 'Cash Flow'!$1:$2 with:

IMPORTRANGE("YourURLString","'Cash Flow'!1:2")

Now that you have shared the sheet:

=IFERROR(HLOOKUP(A$1,IMPORTRANGE("https://docs.google.com/spreadsheets/d/153trbK2CvrUbEiIF2YZlAhskBq4MqQCckxAxutPOnEQ/edit?usp=sharing","'Cash Flow'!2:3"),2,FALSE),0)

---

Another Option; SUMIF()

=SUMIF('Cash Flow'!1:1,A$1,'Cash Flow'!2:2)

This does not work cross files in google sheets.

---

My Cash Flows for reference:

这篇关于如何在工作表中的列之间映射值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！