跳到主要內容

如何根據Excel中的類別更改氣泡圖顏色?

如果要根據Excel氣泡圖中的不同類別更改不同的氣泡顏色,如下面的屏幕截圖所示,本教程可以為您提供幫助。
Doc-Bubble Color-1

根據類別更改氣泡圖顏色


箭頭藍色右氣泡 根據類別更改氣泡圖顏色

1.您需要復制x軸值,然後將其首先粘貼到原始數據范圍旁邊的空白列中。

Doc-Bubble Color-2

2.然後在數據范圍正上方的單元格中鍵入類別名稱,並在每兩個類別之間保留一個空白單元格。 看截圖:

Doc-Bubble Color-3

3.然後在每個類別下的單元格中,鍵入y,然後在每個空白單元格下的單元格中鍵入Bubble in。 請參閱以下屏幕截圖:

Doc-Bubble Color-4

4.輸入此公式 = IF($ A3 = F $ 1,$ C3,NA()) 進入細胞 Y 單元格,然後將填充手柄拖到所需的範圍。

備註:$ A3表示原始數據范圍內的類別名稱,F $ 1表示此列中所需的類別,$ C3表示原始數據范圍內的y值。

Doc-Bubble Color-5

5.然後鍵入此公式 = IF(ISNA(F3),NA(),$ D3) 進入細胞 泡沫 單元格並將填充手柄拖到所需的範圍。

備註:F3表示保留到活動單元格的單元格,$ D3表示原始數據范圍內的氣泡值。

Doc-Bubble Color-6

6.選擇在上述步驟中創建的新數據范圍,然後單擊 插入 > 其他圖表 > 泡沫,然後選擇所需的氣泡類型。

Doc-Bubble Color-9

在Excel 2013中,單擊 插入 > 插入散點圖(XY)或氣泡圖 > 泡沫.

現在,您可以看到具有相同氣泡顏色的相同類別。

Doc-Bubble Color-8

如果要基於值更改圖表顏色,可以轉到本教程 如何根據Excel中的值更改圖表顏色.

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (13)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Nice 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...
This comment was minimized by the moderator on the site
If you clear the cell that reads 'x' you should be able to get the values
This comment was minimized by the moderator on the site
wow what wizardry is this? it works but no idea why
This comment was minimized by the moderator on the site
Thanks 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? ~/R
This comment was minimized by the moderator on the site
continue from previous msg.... X axis = period, Y axis = value, bubble = value with customer name
This comment was minimized by the moderator on the site
Hi 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! Casey
This comment was minimized by the moderator on the site
I 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.
This comment was minimized by the moderator on the site
you 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
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
The 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.
This comment was minimized by the moderator on the site
[quote]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?By David[/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.
This comment was minimized by the moderator on the site
Hi Becky,


Thanks for sharing this with us. Can you please explain this with an example? It would be really helpful. Thank you.
This comment was minimized by the moderator on the site
An alternative to this is editing the data to not include the row header (ie, the "x"). This seems to make the values correspond with the x-axis.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations