I am attempting to make a table in Excel whereby I have stock prices in one column and corresponding dates in the form 'dd/mm/yyyy' in the next column:
我正在尝试用Excel做一个表格，其中一栏是股票价格，下一栏是相应的日期，形式是dd/mm/ yyyyy:
date | share price
I am trying to include the conversion rate of USD to GBP, however this is a monthly reading.
我试着把美元换算成GBP，不过这是一个月的读数。
Is there a way to copy the corresponding monthly conversion next to each entry of the stock price so that it resembles the table below?
是否有一种方法可以将每个月对应的转换复制到股票价格的每个条目，使其类似于下面的表格?
date | share price | USD/GBP
01/01/2000 | 150 | 0.6
02/01/2000 | 155 | 0.6
...
01/02/2000 | 149 | 0.65
1 个解决方案
#1
1
If you have a named range (say MYrange) with the likes of 1|2000 immediately to the left of 0.6 (and 2|2000 of 0.65 etc) where MYrange is a two-column array of month number (1 representing January through to 12 for December) concatenated with delimiter (|) and the year number in the left hand column, and in the right-hand column and matching row the relevant exchange rate.
如果你有一个名叫范围(比如MYrange)与1 | 2000立即左边的0.6(0.65和2 | 2000等)MYrange在哪一个两列的月数(1代表1月到12月12日)连接分隔符(|)和年左手列数,在右边的列和匹配行相关的汇率。
Then assuming date is in A1, in C2 and copied down to suit:
然后假设日期在A1, C2，然后复制下来以适应:
=VLOOKUP(MONTH(A2)&"|"&YEAR(A2),MYrange,2,0)
where the formula extracts the month number (with MONTH) concatenates it (with &) with a delimiter (I chose pipe) and with the year number (from YEAR) to seek this in the left-hand column of MYrange and return the value if found (set by 0) from the cell immediately to the right (set by 2).
公式提取月(月)连接数量以分隔符(&)(我选择管)和数量(每年)寻求这左栏的MYrange和返回值如果发现(由0)从细胞立即向右(组2)。
#1
1
If you have a named range (say MYrange) with the likes of 1|2000 immediately to the left of 0.6 (and 2|2000 of 0.65 etc) where MYrange is a two-column array of month number (1 representing January through to 12 for December) concatenated with delimiter (|) and the year number in the left hand column, and in the right-hand column and matching row the relevant exchange rate.
如果你有一个名叫范围(比如MYrange)与1 | 2000立即左边的0.6(0.65和2 | 2000等)MYrange在哪一个两列的月数(1代表1月到12月12日)连接分隔符(|)和年左手列数,在右边的列和匹配行相关的汇率。
Then assuming date is in A1, in C2 and copied down to suit:
然后假设日期在A1, C2，然后复制下来以适应:
=VLOOKUP(MONTH(A2)&"|"&YEAR(A2),MYrange,2,0)
where the formula extracts the month number (with MONTH) concatenates it (with &) with a delimiter (I chose pipe) and with the year number (from YEAR) to seek this in the left-hand column of MYrange and return the value if found (set by 0) from the cell immediately to the right (set by 2).
公式提取月(月)连接数量以分隔符(&)(我选择管)和数量(每年)寻求这左栏的MYrange和返回值如果发现(由0)从细胞立即向右(组2)。