Shulou(Shulou.com)11/24 Report--

The original title: "when I used this chart in the report, my boss actually wanted to give me a raise?" "

There is such a function in Excel, Der Spiegel said, it is the only function that cannot be replaced by 👉.

And I prefer to respect it as a god, it is the hyperlink function HYPERLINK!

You may not believe it, but the divine gift of HYPERLINK has nothing to do with the basic functions of its jump and link!

Let's first look at the effect, there is a picture and the truth!

Yes, you can dynamically switch charts by hovering over the mouse!

How do you do that? Listen to Xiaohua dismantle it for you!

Create the basic chart ▋ Step 01: build the query based on the data source and build a query result area based on specific variables (G1 cells).

This particular variable will change according to the mouse hover, and then change the query results.

The G2 cell formula is as follows:

= OFFSET ($Aph2VRO 13 MagneMath match (Gloss1 recorder BMUB1GRAPHING 1))

👉 formula description:

The MATCH function matches the column order of the provinces shown in the G1 cell in the data source area B1:E1, and then shifts the corresponding number of columns from column A to the right through the OFFSET function to get the monthly value of the corresponding province.

Some of the OFFSET functions used here are relatively unfamiliar. I enclose a small card to review the old and know what is new.

▋ Step 02: use the data in the query result area G2:G13 in Step 01 to draw the required table. Here the floret is simply drawn as a line chart, and friends can DIY it by themselves.

Just note that the area referenced in the chart must be the query result in Step 01, so that when the G1 cell changes, the chart will also change due to the change of the query result area, so the chart will "move".

The operation of the hovering trigger mechanism PART 01 has established the relationship between the G1 cell and the chart. At this time, if the G1 cell can dynamically change according to the mouse hover, then the problem of using the mouse to dynamically switch the chart can be realized.

This is the purpose of HYPERLINK, which refreshes the calculation results when the mouse moves over its cell.

Therefore, we only need to build a custom function that can assign the target value to the G1 cell and nest it into the HYPERLINK function formula to achieve the dynamic update of G1.

If you understand the principle, the operation will be very clear.

▋ Step 01: custom function Press [Alt+F11] or click the [Visual Basic] button in the [Development tools] tab to open the VBE window, click [insert]-[Module], copy the following code directly into the module, close the VBE window, and save the file as xls or xlsm format.

Function hover (rng As Range) 'where hover is the function name. [G1] = rng.Value' [G1] indicates the cell address of the special variable, and End Function can be customized.

▋ Step 02: set the HYPERLINK formula to select the appropriate location for mouse swiping, and set the formula consisting of HYPERLINK and the custom function hover, where the hover function refers to the cell where the target variable is located. (in the case, choose to set the formula in the M column. )

The M4 formula is as follows:

= IFERROR (HYPERLINK (hover (B1)), B1)

Similarly, M6, M8, and M10 refer to C1, D1, and E1 cells, respectively.

At the same time, in order to enable the mouse to switch over the four cells without accurately hovering over the corresponding text, we can change the horizontal alignment of the cells to alignment at both ends.

At this point, a dynamic chart that can be automatically switched according to the mouse slide is completed. The line chart shows the data of the corresponding provinces in which cell the mouse is moved to. It's amazing!

Extension and expansion of the Excel mouse hover technology realized by HYPERLINK and custom functions can make some thorny or difficult problems to be properly and novelty solved.

For example, when the broken line is too dense to distinguish, you only need to add a highlight broken line based on hover technology to the original chart to achieve the need to view the broken line in the dense broken line.

Even, we can combine the definition name to achieve the switch between different chart types to meet the reporting needs of different occasions.

Naturally, a simple analog tab switch is not to mention!

Other HYPERLINK mouse hover technology applications, friends can try their own, floret only click so far.

In the end, Xiaohua takes you to review the principles and operating essentials of HYPERLINK mouse hover technology:

❶ establishes a formula query based on specific cells and creates charts based on the query result data

❷ inserts code to create custom functions that dynamically assign target values to specific cells

❸ uses custom functions and HYPERLINK functions to create formulas that reference the specific cells mentioned above

❹ mouse hover triggers the HYPERLINK function to refresh the calculation results, change specific cells, and then achieve chart switching.

The above is the principle and operation essentials of HYPERLINK mouse hover technology. Have you learned it?

You might as well start by trying to complete several applications expanded by PART 03, and gradually realize all kinds of whimsical things!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiaohua, Editor: Xiaoyin

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.