根据行ID为特定列标题行数据 [英] Row data for specific column headers based on row ID

问题描述

我有一个包含几百行员工数据的工作表。在最基本的层面上，它列出了 EmployeeID 的 名称 并列出他们拥有的 技能 。

我所做的是在另一个工作表（在我的 Employee Lookup 工作簿）我使用了索引和匹配函数的组合来查找我输入的 EmployeeID ，并从员工技能数据表中返回技能级别。

我想要做的就是尽量简化数据的获取方式（我对vba的知识有限，但有一些excel公式知识，因此嵌套索引匹配）。 Employee Skills 工作表包含 Column A 和 EmployeeID code> s和具有技能名称的行A （和之前的标题）。

我可以设置索引 / 匹配每一行然后我需要每个技能的列号，然后查找 employeeID 并返回技能（即使它们是 blank ）。

我不会想知道如何开始编写代码，但我会认为会有一个更简单的方法来做到这一点，而不是做一个 Index / 匹配每个参考技能的公式（每个部门有〜15-50个技能）当我协助4个部门时）。
$ b $ < Index / Match 函数需要很多的时间和已经有一个组。我不认为我想要的东西可以在Excel中完成，但不幸的是，这是我留下的数据资源。

电子表格布局示例：

解决方案

简短回答：

^{（详细的&颜色编码的屏幕截图细分公式进一步缩小，或者全屏 .PNG}

D16 中公式的短版为：

  = INDEX（$ C $ 4：$ I $ 8，MATCH（$ D $ 11，$ A $ 4：$ A $ 8,0） ，MATCH（C16，$ C $ 3：$ I $ 3,0））
  

表单ula我使用的看起来很可怕，但它只是上面的公式两次（复制/粘贴）以及一个 IF ，这样如果源单元格为空白（就像Jane's Partying的分数）...

pre code = = IF（INDEX（$ C $ 4：$ I $ 8，MATCH（$ d $ 11 $ A $ 4：$ A $ 8,0），MATCH（C16，$ C $ 3：$ I $ 3,0））= 0 ，INDEX（$ C $四：$ I $ 8 MATCH（$ d $ 11 ，$ A $ 4：$ A $ 8,0），MATCH（C16，$ C $ 3：$ I $ 3,0）））

一旦这个公式在 D16 中，我只需填写（或复制/粘贴）到其他技能。

• 请注意，上述公式中的 全部 单元格引用为绝对除了 C16 之外。

我们希望 所有 单元格范围保持不变，即使我们复制/粘贴（或填充）单元格到另一个单元格... ，除了：C16：这就是我们的查找基于的基础，因此一旦完成当我们在其他地方复制单元格时需要更改。

---

二维索引/匹配示例： / em>

< h2>下载 .xslx 示例文件：

要更好地了解它的工作原理，您可以从我的屏幕截图下载 Excel文件 _{（来源 ）}

---

>

区域性反思：

在回答有关 ，我想知道 $ 是国际符号绝对参考，或者这个符号是否跨越整个池塘。

$ b

（Brexit将会改变UK Absolutes from =€A€1 至 =£A£1 ?!）

快速搜索显示 $ 确实是用于Excel Absolutes的全球符号 - 但是有些用户（从未输入过 $ 之前）找不到它。这里有一个链接，描述如何输入 $ 非美式键盘上的美元符号 ...

---

祝您的项目顺利！如果您有任何疑问，请告诉我...

I have a worksheet with a few hundred rows of employee data. At its most basic level it lists EmployeeID's, Name and lists the skills that they have.

What I have done is on another worksheet (in my Employee Lookup workbook) I have used a combination of the index and match functions to look up an EmployeeID that I enter and return the skill level from the employee skills data sheet.

What I want to do is try to simplify how the data is obtained (I have limited vba knowledge but some excel formula knowledge, hence nesting index match). The Employee Skills sheet has Column A with EmployeeIDs and Row A with skill names (and prior headers).

I can set up the Index/Match for each row but then I need the column numbers for each skill and look up that employeeID and return the skills (even if they are blank).

I wouldn't really bother with trying to figure out how to begin to code this but I would think there would be an easier way to do this than making an Index/Match formula for each referenced skill (with there being anywhere from ~15-50 skills per department when I assist with 4 departments).

The Index/Match functions would take a lot of time and already has for one group. I don't think what I am wanting can be done in Excel but unfortunately that is the data resource I am left with.

Example of spreadsheet layout:

解决方案

Short Answer:

^{(A detailed & color-coded screenshot breaking down the formula is further down, or full-screen .PNG here.)}

Here's a solution using sample data similar to yours:

The "short version" of the formula in D16 is:

=INDEX($C$4:$I$8,MATCH($D$11,$A$4:$A$8,0),MATCH(C16,$C$3:$I$3,0))

The actual formula I used looks scarier but it's just the above formula twice (copy/pasted) along with an IF so that it will bring over a blank cell if the "source cell" is blank (like Jane's Partying score)...

=IF(INDEX($C$4:$I$8,MATCH($D$11,$A$4:$A$8,0),MATCH(C16,$C$3:$I$3,0))=0,"",INDEX($C$4:$I$8,MATCH($D$11,$A$4:$A$8,0),MATCH(C16,$C$3:$I$3,0)))

Once that formula is in D16, I just "filled down" (or copy/paste) to the other skills.

• Note that all cell references in the above formula are "Absolute" except for C16.

We want all cell ranges to stay the same even if we copy/paste (or fill) the cell to another cell... except for C16: that's what our lookups are based on so that once does need to change when we copy the cell elsewhere.

---

"Two-dimensional Index/Match" Example:

Download .xslx sample file:

To get a better idea of how it works, you can download the Excel file from my screenshot here to experiment, modify, and adapt as desired.

^{It's a direct-download link to a Macro-free .XSLX, hosted by Jumpshare, which allows you to view online but it doesn't like complex formulae, and the columnar formatting is messed up on some browsers (however you can also download from the online viewer with the DOWNLOAD button at the ↗top-right↗ of the viewer.}

---

INDEX & MATCH Function Documentation & More Examples:

• Microsoft (Office.com): MATCH and INDEX functions (with videos)

• Microsoft: Switch between relative, absolute, and mixed references

• ExcelJet: Two-way lookup with INDEX and MATCH

• MSDN: Performing Two-Dimensional Lookups

• mbaExcel: Tutorial: How to Decide Which Excel Lookup Formula to Use

• Stack Overflow: Index/Match with VBA

• ExcelTip: How To Use Index/Match for 2 Criteria with VBA

---

A Crash-Course on Absolute vs. Relative Cell References

Cell Reference — A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. _(Source)

By default, a cell reference is relative. For example, when you refer to cell A2 from cell C2, you are actually referring to a cell that is two columns to the left (C minus A), and in the same row (2). A formula that contains a relative cell reference changes as you copy it from one cell to another.

As an example, if you copy the formula =A2+B2 from cell C2 to D2, the formula in D2 adjusts to the right by one column and becomes =B2+C2. If you want to maintain the original cell reference in this example when you copy it, you make the cell reference absolute by preceding the columns (A and B) and row (2) with a dollar sign ($). Then, when you copy the formula =$A$2+$B$2 from C2 to D2, the formula stays exactly the same.

In less frequent cases, you may want to make a cell reference "mixed" by preceding either the column or the row value with a dollar sign to "lock" either the column or the row (for example, $A2 or B$3).

_(Source)

---

>

"Regional Afterthought":

While answering a question about Excel's regional differences, I wondered if $ is the international symbol for Absolute References, or whether the symbol varied "across the pond".

("Will Brexit change "U.K. Absolutes" from =€A€1 to =£A£1?!")

A quick search revealed that the $ is indeed the "worldwide" symbol used for Excel Absolutes — however some users (having never typed a $ before) had trouble locating it. Here's a link describing How to type the $ Dollar Sign on Non-American Keyboards...

---

Good luck with your project! Let me know if you have any questions...

这篇关于根据行ID为特定列标题行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！