Ways to display and invoke pictures in Excel using the Excel camera feature

As shown in the following illustration, the left side contains four pictures that are inserted into Excel, and each picture has an index identification of the English alphabet. The user can enter a different index ID at the location of the "call target" on the right by setting, so that Excel automatically displays the corresponding picture in the call result position.

The main principle of realizing this function comes from Excel's picture link function, which is the legendary "camera" function. As I mentioned earlier, the camera feature in Excel does not have to be able to use the "Camera" command button by customizing the menu. Because its essence is "the picture that contains the link", so it can be implemented in a simpler way. The specific methods are as follows:

1, select the range of cells that need to be photographed, such as A1:d5 area, press

2, in the table to select a place to put "photos", then, if you are in version 2003, you need to press and hold the key, and then click Edit on the menu bar, and the "Paste Picture link" command appears in the Drop-down menu (if you don't press and hold the key, this command will not appear in the dropdown menu), click the Life The order generates a mirrored picture of the target area at the current location.

As shown in the following illustration:

If you are in version 2007, you do not need to press shift in step 2nd and then click the "Paste" drop-down button on the Start tab to select the Picture button with the chain, as shown in the following figure.

The above procedure is called "Camera" function, the resulting "picture link" is actually a mirror image of the target area, change the content of the target area, will be reflected in real-time in this picture, like a real-time monitoring window. Select this picture to see in the formula bar of Excel that it actually contains a link formula for a cell address: =A1:D5, as shown in the following figure. So the essence of this function is the picture with the link.

This linked picture shows more than just the contents of the target area cell, and it's more like a satellite photograph that takes all the content that can be displayed within the range of the target cell, including pictures, graphs, and even charts that appear in this area. Although these objects do not belong to a specific cell, they can be faithfully recorded by the camera's function as long as their position overlaps the range of the target cell. As shown in the following figure.

Based on the above principle, if you place different pictures in different ranges of cells, you can display different pictures by linking to the corresponding cell address. As shown in the following figure.

The following two aspects are required to perform an evidence-based picture display call based on the index condition:

1, through the formula, the index entry and the picture is the address of the cell to establish an association relationship. This can be accomplished with a number of query reference functions, such as the combination of indirect+match functions.

2, because the picture link formula in addition to directly using the cell address, and can not directly use other function formulas. So here you have to use the "Define name" feature to build a reference bridge.

Take the first picture example in this article as an example, the following methods:

1, according to

Pic=indirect ("B" &match (sheet1! $H $2,sheet1! $A $: $A $4,0))

The formula means very simply, looking through the match function to find the index target in the H2 cell in the column A, and then referring to the location of the corresponding picture through the indirect function in column B.

2, after the definition name is completed, select the "Call results" location of the "photo" picture, in the formula bar, enter "=pic" can be.

At this point, you can change different indexed content in cell H2 to automatically display the corresponding different target pictures in the photos on the left.