Excel illustrations you really do? One-step approach to fast learning

In daily work, such as creating employee lists and product introduction tables, it is often necessary to insert a lot of pictures that correspond to texts in Excel. The conventional method is to insert photos into corresponding rows one by one, and then resize them. If you need to insert a lot of pictures, the traditional operation is obviously time-consuming and laborious. In fact, we can use batch processing and Excel formulas to generate "linked code for pictures" to achieve batch import and layout of pictures in one step. Here we take the production employee roster as an example. First, save the photo of the employee in the “D:\Photos” directory under the employee’s name.

Because Excel generates image links automatically based on the file name, we first need to extract the photo file name. The file name can be extracted using the Dir command, starting Notepad to create a batch cfan.bat, placed in the "D:\Photos" directory, the code is as follows:

Dir *.png /b >Name.txt

Code explanation: The png image in the specified directory (if it is other format, please change the code), extract the file name, and generate the "name.txt" file in the current directory (Figure 1).

Figure 1 Generate file

In this way, run the above batch process under “D:\Photo” and open “D:\Photo\Name.txt” to see the file name in the current directory. Click “Edit → Replace” in Notepad to “. "PNG" is replaced with a null character. After the replacement is complete, the file name character can be obtained (Figure 2).

Figure 2 replacement operation

Copy the above characters, then open Excel and paste the copied characters into column A. In this way, A2 starts typing the name of the employee corresponding to the photo, and enters other explanatory characters as prompted (Figure 3).

Figure 3 Paste the extracted characters into Excel

Select B2 and click "Insert → Image → From File". Select the corresponding employee's photo and insert it into cell B2. At the same time, adjust the photo size so that the photo can be displayed in the cell with the best effect. Then adjust the row height and column width of cell B2, just move the mouse to the row and column line adjustments to see the specific value displayed, try to select the integer value, such as the width in this case is 155 pixels (18.75cm)), the height is 190 pixels (142.5cm), use this as the photo size reference value (Figure 4).

Figure 4 Get cell width and height

Select the B column, click "Start → Format → Row Height" and "Start → Format → Column Width", set the row height and column width to the values ​​shown in B2, so that the cells in column B are adjusted to the agreed size, and The inserted picture can be displayed normally. Then delete the picture inserted by B2, then select B2 and enter the formula "=" in the public column.

"", and then fill in the formula down, so that column B formula will automatically read the name of column A (Figure 5).

Figure 5 Insert and fill the formula

Formula explanation:

The meaning of the formula is to insert a photo link in column B. The photo name refers to the employee name in column A, where width and heigh adjust the photo to the corresponding pixel size. These two parameters actually complete the batch adjustment of the inserted photo.

Select B2 and its fill formula cell, right-click and choose Copy, and paste the copied code into Notepad. Go back to the Excel window, right-click the cell with the inserted formula and right-click and choose Clear Content to clear the insertion formula. Then copy the above code in Notepad (Figure 6).

Figure 6 copy to get the code

Select B2 in Excel, right-click and choose Paste → Paste Special → Unicode Text. Paste the above code into Excel as a “Unicode Text” so that Excel will automatically read the link image corresponding to the code (Figure 7).

Figure 7 Paste Specially

Because Excel supports Html code, the link picture that is pasted as Unicode text will be automatically displayed in Excel, and the size is automatically adjusted by the code. The inserted picture corresponds to the employee shown in column A, so we complete the photo batch insertion and automatic adjustment (Figure 8).

Figure 8 Automatically insert photos

Since the code is used to link images, if the image is to be replaced, for example, if many employees have the same name but different genders, this can be changed quickly by changing the photo name and code. For example, if the company has both male and female employees named Zhang Xing, then Zhang Xing (female) .png can be added to the woman's Zhang Xing photo, and at the same time, column A assumes that the employee name corresponding to A5 is also changed to Zhang Xing (female). ” and then delete its corresponding B5 photo, change the code in Notepad to “

"" Paste it to B5 in a Unicode textual format to achieve photo replacement. "Zhang Xing (Men)" replaces similar operations.

hint:

The above method is to insert photos into Excel in batches. If you want to export photos that have been inserted in batches in Excel, you can save the Excel document as a web page, so you can get a “.files” directory with the same saved name. All inserted pictures. You can also insert a picture into a hyperlink when inserting a picture, the code is similar , Here is the table, body can be, but the table will be vertically arranged, and the body will be arranged horizontally.