如何在一个单元格中显示图片取决于另一个单元格的值 [英] How to show a picture in a cell depending on the value of another cell

问题描述

说我有一个带有天气类型"的两列表格.第一列具有天气类型(例如，雨，雪，冰，云，太阳)，第二列具有表示天气类型的图片图标.

Say I have a two column table with "Weather Types". The first column has a weather type (eg rain, snow, ice, cloud, sun) and the second column has a picture icon that represents the weather type.

说我有第二个工作日"表，该表使用相同的两列再次显示了本周的预期天气，但是这次有7行，即7天.但是，假设该表的第一列每周自动更新一次，如果我希望第二列中显示的图片针对相应的天气类型自动与第一张表中的图片匹配.

Say I have a second "Weekdays" table that shows the weather expected this week again with the same two columns, but this time with 7 rows ie for the 7 days. However, let's say the first column of this table is updated automatically every week, what if I want the picture displayed in the second column to automatically match the picture in the first table for the corresponding weather type.

推荐答案

这种情况很容易解决.

要实现的最重要的事情是:

The most important things to realise is:

如果选择任何图片，然后在公式栏中键入单元格引用(例如 = A1 或 = A1:D1 )，则该公式将由图片以确定要显示的内容.该公式使相框查找"位于公式中引用的单元格上方的任何形状对象.然后，选定的图片将显示公式引用中单元格上方的部分形状(即图片).

If you select any picture and then type a cell reference into the formula bar (eg =A1, or =A1:D1), that formula is used by the picture to determine what to display. The formula causes the picture frame to "look for" any shape objects that are positioned over the cell(s) referenced in the formula. The selected picture will then display the part of the shapes (ie pictures) that are over the cells in the formula reference.

这意味着通常需要在单个单元格中完全显示要查找其图像的形状.

This means that the shapes whose images are being looked up will usually need to be displayed fully within a single cell.

Capiche！?

因此，在我提出问题的情况下，工作日"表的每一行都需要一张空白图片，并使用公式得出的结果引用了具有天气图片的天气类型"表中的某个单元格超过它.这比您预期的要棘手，因为空白图片无法知道"它结束了哪个单元格.

So in the scenario I painted in my question, the "Weekdays" table would need a empty picture on every line, with a formula that RESULTED in a reference to a cell in the "weather types" table that has the weather picture "over" it. This gets trickier than you might expect as the empty picture does not "know" which cell is it over.

这意味着每张具有要显示的图像的图片都需要链接到一个单元格.这是通过为每个需要查找其图片的图片创建一个命名函数或一个命名范围来实现的.此命名的函数/范围引用与该单张图片相关的单元格(我以后将其称为 RELATED_CELL )

This means that every picture which is having the image it displays looked up needs to be linked to a cell. This is achieved by creating a named function or a named range for each picture that needs to have its picture looked up. This named function/range references a cell related to that single picture (I refer to this later as the RELATED_CELL)

有多种使用命名函数返回对单元格的引用的方法.下面是两个示例:

There are a number of ways of using the named function to return a reference to a cell. Two examples are below:

示例方法1

此示例依赖于 RELATED_CELL ，其中包含命名范围的部分名称.然后，使用INDIRECT获取由指定范围引用的单元格的引用.这需要为包含WeatherTypeName的列中的每个单独单元格指定一个命名范围，该范围将用于查找图片.

This example relies on the RELATED_CELL containing part of the name of a named range. Then INDIRECT is used to get the reference of the cell referenced by the named range. This requires a named range for every individual cell in the column containing the WeatherTypeName that will used to lookup the pictures.

在此处中对此技术进行了很好的描述.

This technique is described very well in an article here.

示例方法2(我的首选方法)

在此示例中， RELATED_CELL 包含要查找的WeatherType名称.它不依赖于天气类型表中所有被称为范围的单元格.相反，它使用查找"方法扫描并检查天气类型名称值以查找图片.

In this example the RELATED_CELL contains the WeatherType name to be looked up. It does not rely on all the cells in the the weather types table being named ranges. Instead it uses a "lookup" method that scans and inspects the weather type name values to find the picture.

在页面此处

评论者提供的附件非常有用，请参见此处

The attachment provided by the commenter is very useful see here

请注意，您不能使用VLOOKUP本身，因为它会返回单元格的值，而不是对它的引用.而是使用 MATCH ， ADDRESS 和 INDIRECT 在 RELATED_CELL 中查找值.

Note that you can't use VLOOKUP itself because it returns the value of the cell rather than a reference to it. Instead MATCH, ADDRESS, and INDIRECT are used to lookup the value in the RELATED_CELL.

我想补充一点，我从给定的链接中学到了这些技术，并且模仿了pdf中的示例.希望以上我解释的要点将帮助您更轻松地理解这些方法.

I want to add that I learnt these techniques from the links given and have mimicked the example in the pdf. I hope the points I explain above will help you more easily understand the approaches.

哈维

这篇关于如何在一个单元格中显示图片取决于另一个单元格的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！