Namig: drugi jeziki so prevedeni v Google. Lahko obiščete English različico te povezave.
Vpiši se
x
or
x
x
Registracija
x

or

Kako spremeniti barvo barvnega diagrama glede na kategorije v Excelu?

Če želite spremeniti različne barve mehurčkov, ki temeljijo na različnih kategorijah v mehurčkovnem grafikonu v Excelu, kot je prikazano spodaj, vam ta vadnica lahko pomaga.
doc-bubble-color-1

Spremenite barvo barvnega diagrama glede na kategorije


puščica modra desno mehurček Spremenite barvo barvnega diagrama glede na kategorije


1. Morate kopirati vrednosti x osi in jih prilepiti v prazen stolpec poleg izvornega podatkovnega območja.

doc-bubble-color-2

2. Nato vnesite imena kategorij v celice tik nad obsegom podatkov in shranite prazno celico med dvema kategorijama. Prikaz slike:

doc-bubble-color-3

3. Potem v celicah pod vsako kategorijo vnesite y in vnesite Bubble v celice pod vsako prazno celico. Glej prikaz zaslona spodaj:

doc-bubble-color-4

4. Vnesite to formulo = IF ($ A3 = F $ 1, $ C3, NA ()) v celico pod Y celico in povlecite ročico za polnjenje v obseg, ki ga potrebujete.

Opombe: $ A3 označuje ime kategorije v prvotnem podatkovnem območju in F $ 1 označuje kategorijo, ki jo potrebujete v tem stolpcu, $ C3 označuje vrednost y v prvotnem podatkovnem območju.

doc-bubble-color-5

5. Nato vnesite to formulo = IF (ISNA (F3), NA (), $ D3) v celico pod bubble celico in vlečico za polnjenje do obsega, ki ga potrebujete.

Opombe: F3 označuje celico, ki je levo v aktivno celico, in $ D3 označuje vrednost mehurčka v izvornem podatkovnem območju.

doc-bubble-color-6

6. V zgornjih korakih izberite novo območje podatkov, ki ga ustvarite, in kliknite Vstavi > Drugi grafikoni > bubblein izberite vrsto mehurčka, ki jo potrebujete.

doc-bubble-color-9

V Excelu 2013 kliknite Vstavi > Vstavi scatter (XY) ali Bubble chart > bubble.

Zdaj lahko vidite isto kategorijo z isto barvo mehurčka.

doc-bubble-color-8

Če želite spremeniti barvo grafikona glede na vrednost, lahko obiščete to vadnico Kako spremeniti barvo grafikona glede na vrednost v Excelu.


Priporočena orodja za produktivnost za Excel

zavihek kte 201905

Kutools za Excel vam pomaga, da vedno končate delo pred časom in izstopite iz množice

  • Več kot zmogljive napredne funkcije 300, zasnovane za 1500 delovne scenarije, ki povečujejo produktivnost z 70%, vam dajejo več časa za skrb za družino in uživanje v življenju.
  • Ne potrebujete več pomnilniških formul in VBA kod, od zdaj naprej pa dajate svojim možganom počitek.
  • Postanite strokovnjak za Excel v minutah 3, zapletene in ponavljajoče se operacije lahko opravite v nekaj sekundah,
  • Vsak dan zmanjšajte število operacij tipkovnice in miške, zdaj se poslovite od poklicnih bolezni.
  • 110,000 visoko učinkovite ljudi in 300 + svetovno priznanih podjetij izbiro.
  • Brezplačna preizkusna različica 60 dneva. 60-dnevno jamstvo vračila denarja. 2 let brezplačne nadgradnje in podpore.

Prinaša kartično brskanje in urejanje za Microsoft Office, veliko močnejši od zavihkov brskalnika

  • Office Tab je namenjen za Word, Excel, PowerPoint in druge Office aplikacije: Založnik, Dostop, Visio in Projekt.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Z 50% poveča vašo produktivnost in vsak dan zmanjša na stotine klikov z miško!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jon · 1 years ago
    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...
    • To post as a guest, your comment is unpublished.
      Anurag Halder · 4 months ago
      If you clear the cell that reads 'x' you should be able to get the values
  • To post as a guest, your comment is unpublished.
    racefface · 2 years ago
    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
  • To post as a guest, your comment is unpublished.
    Casey Fong · 2 years ago
    continue from previous msg....
    X axis = period, Y axis = value, bubble = value with customer name
  • To post as a guest, your comment is unpublished.
    Casey Fong · 2 years ago
    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
  • To post as a guest, your comment is unpublished.
    Mel · 2 years ago
    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.