@JP_ZhangTo create a combination chart, you need to select the ranges that your data is in and go to the insert tab. On the Insert tab, in the Charts group, there is a combo symbol that you must click. This symbol has a line graph above a bar graph (it is in the second row to the left of the PivotChart symbol). Once you click on the combo symbol, the chart dialogue box will appear. From here since you want a custom combination chart, you need to click the combo symbol with a blue pencil. This will allow you to customize where you want your data to be placed or what you wish to as a line or bar graph. Finally, to see your results, you will click to OK, which is located at the bottom of the dialog box. I hope this helps!
Hello @JP_Zhang , I'm having trouble visualising the chart you want to create. Can you mock it up with the drawing tools and post a picture? To post a picture, click on the camera icon in the toolbar.
If you want to plot the count and the average, you first need to calculate these numbers somewhere in the workbook. In the screenshot, I have manually entered the words in column E and then used Countif and AverageIF formula.
Count =COUNTIF(B2:B24,E2) in F2 copied down,
Average =AVERAGEIF($B$2:$B$24,E2,$A$2:$A$24) in G2 copied down.
Then I added a clustered column chart and changed the Average series to a line chart.