- 重用任何东西： 将最常用或最复杂的公式，图表和其他任何内容添加到您的收藏夹中，并在将来快速重复使用它们。
- 超过20文本功能： 从文本字符串中提取数字; 提取或删除部分文本; 将数字和货币转换为英语单词...
- 合并工具：多个工作簿和表格合二为一; 合并多个单元格/行/列而不丢失数据; 合并重复行和总和...
- 拆分工具：根据价值将数据拆分为多个表格; 一个工作簿到多个Excel，PDF或CSV文件; 一列到多列......
- 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 创建邮件列表和 通过Cell的价值发送电子邮件...
- 超级过滤器： 创建高级过滤方案并应用于任何工作表; 排序 按周，日，频率等; 筛选 通过大胆，公式，评论......
- 超过300强大的功能; 适用于Office 2007-2019和365; 支持所有语言; 在公司轻松部署; 全功能60天免费试用。
2。 然后在数据范围正上方的单元格中输入类别名称，并在每两个类别之间保留一个空白单元格。 看截图：
3。 然后在每个类别下的单元格中键入y，然后在每个空白单元格下键入Bubble。 请参阅下面的截图：
4。 键入此公式 = IF（$ A3 = F $ 1，$ C3，NA（）） 进入单元格下 Y 单元格，然后将填充手柄拖动到您需要的范围内。
注意：$ A3表示原始数据范围中的类别名称，F $ 1表示您在此列中需要的类别，$ C3表示原始数据范围中的y值。
5。 然后输入这个公式 = IF（ISNA（F3），NA（），$ D3） 进入单元格下 泡沫 单元格并将其填充到您需要的范围。
6。 选择您在上述步骤中创建的新数据范围，然后单击 插页 > 其他图表 > 泡沫，并选择你需要的气泡类型。
在Excel 2013中，单击 插页 > 插入散点图（XY）或气泡图 > 泡沫.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 years agoNice except that the graph comes out with other values on the x axis than what is in the data table you make the graph on...
To post as a guest, your comment is unpublished.· 2 years agoThanks for this guide - it's solved a big issue for me.
Well, almost. I've got this set up, but also have Data Labels on the bubbles. However, I am having an issue with the Data Label dissapearing from the bubble when the Category (bubble colour) is changed. I've tried a few things, the thing that has come closest to solving the issue is:
Changing all my data to the same category, configuring the Data Labels to the right format (the label content is coming from a range of cells), then changing all the data to the next category, and setting up the formats... repeating for all caegories. Once that is done then I can happily change the categories and the bubble colours change and the bubbles remain labeled...
However.. If I save the document, close it and reopen then changing the category results in the bubble changing colour, but the data label on the bubble disspears. Looking at the settings nothing has changed but the label content does not show. Changing the cateogry back to the original value results in the label coming back..
Has anyone seen this before? ... and if so, any ideas on a solution?
To post as a guest, your comment is unpublished.· 2 years agocontinue from previous msg....
X axis = period, Y axis = value, bubble = value with customer name
To post as a guest, your comment is unpublished.· 2 years agoHi there,
I would like to know is it possible to have the bubble's colour based on risk level with the following information availbale below and the colour of the bubble will depend on risk level (example, Medium = Orange, Low = Green, High = Red, Converted = Blue). I tried follow the example you showed above but couldn't get it working...
customer Period Value Risk level
A Jan-17 $15,000 Medium
B Dec-16 $20,000 Low
C Mar-17 $25,000 Converted
D Feb-17 $10,000 High
E Jan-17 $30,000 Medium
F Apr-17 $8,000 Low
Thanks in advance for your help!
To post as a guest, your comment is unpublished.· 3 years agoI see that these steps work, but there is no explanation of why.
for example you use the formula "=IF(ISNA(H10),NA(),$D10)", what does this mean and why are you using it.
Is there a document available that not only shows a user how to change the colours but explains the process.
To post as a guest, your comment is unpublished.· 3 years agoyou are probably doing what I did initially which is selecting the header rows when selecting the data, if you only select the values and no headers it will work fine
To post as a guest, your comment is unpublished.· 3 years agoThanks for this walkthrough. A problem that I have, and I see you have it too based on the screen shots, is that the x axis no longer reflects the values from your data. The position of the bubbles along the x axis may be accurately scaled but the actual values are missing. How do you fix that?
To post as a guest, your comment is unpublished.· 2 years agoThe x-axis displays the number of items in the list and the position of each one. The only workaround that I could figure out was to selected Edit Data and then select each category and specify the cell reference rather than leaving it as a range.
To post as a guest, your comment is unpublished.· 2 years ago[quote name="David"]Thanks for this walkthrough. A problem that I have, and I see you have it too based on the screen shots, is that the x axis no longer reflects the values from your data. The position of the bubbles along the x axis may be accurately scaled but the actual values are missing. How do you fix that?[/quote]
The values along the x-axis reflect the position within the selection, not the actual values for the x-axis. eg., the first bubble is position 2 (second row), second bubble is position 3 and so forth. The values generated along the x-axis will reflect the number of items in the list. The only workaround that I could discover was to Edit Data and then select each category and manually adjust the cell references for x-axis, y-axis, and bubble size to reflect the actual cell, not a range.