Farbcodierte Fraktionsverteilung [OC] (Helfen Sie mehr, mehr zu verschönern)

Quellinformationen und Tools, die auch in meinem Kommentar verwendet werden
Quelle Info 1
https://www.torn.com/factions.php?step=profile&id=48790

Quelle Info 2
https://www.torn.com/factions.php?step=profile&id=52917

Tools UED: BSP (Battle Stathredictor)
Dieses Tool wird benötigt, um das zu bekommen "Statistiken" Für jeden Spieler auf den Quellseiten
https://www.torn.com/forums.php#/p=threads&f=67&t=16290324&b=0&a=0

Ich habe die Regeln durchgesehen und bin zuversichtlich, dass dies kein persönlicher Info -Diagramm ist, aber bitte entfernen Sie, ob ich am Montag zurückkommen sollte

Ich bitte also um ein wenig Hilfe bei der Verschönerung sowie bei der automatischen/bedingten Formatierung. Ich spiele ein Spiel namens Torn (www.torn.com) Und wir haben wöchentliche Kriege. Ich mag es, Berichte für meine Fraktion so zu berichten, dass sie feindliche Statistiken auf einen Blick (Diagramm mit Namen und Statistiken) sehen und unsere Fraktionenstatistiken auch mit den feindlichen Fraktionen auf einen Blick (Histogramme) vergleichen können.

Das ist alles in Excel!

Ich bitte um einige Verschönerungsspitzen dieser Visualisierung. Ich mag es nicht, dass die beiden Histogramme nicht übereinstimmen (rechts hat eine blaue Linie, die ich nicht entfernen kann). Ich würde auch gerne wissen, ob es eine Möglichkeit gibt, die Zahlenbereiche automatisch mit Farben in den Histogrammen zu assoziieren, damit ich die Farben für die Spalten nicht manuell eingeben muss. Jedes Produkt von 10 hat eine neue Farbe, die auf den grundlegenden vorgeschlagenen Farben in Excel basiert.

Ich muss auch einen Weg finden, um die programmgesteuert zu färben "Statistiken" Die Spalte im Diagramm, da dies auch etwas ist, das ich aufgrund des Suffix von (k/m/b/t) manuell hinzufügen muss. Dies war tatsächlich eine Art Schmerz, um in der Tabelle zu programmieren, und landete die Quelldaten in separate Spalten auf, die eine andere Spalte mit etwas wie = ifs (Buchstabencell = K, 1000, 1000, Lettercell = M, 1000000, Lettercell = B, 1000000000) und multiplizieren mit der linken Nummer von Original Split. Das ermöglichte es mir, tatsächlich vom größten zum kleinsten zu sortieren. Ich mag das Aussehen von K, M, B angehängte Zahlen besser als Engineering/Scientific Notation, aber ohne eine ordnungsgemäße ganze Zahl fand es es sehr schwierig, eine bedingte Formatierung mit der entsprechenden Spalte zu verbinden. Ich kann auch keinen Weg finden, eine Indexnummer in eine andere Zelle in die Konditionierung zu übersetzen. Z.B. Farbe Spalte A If Spalte B = (Argument)

Ich hoffe, dies ist eine Einschränkung meiner Excel -Fähigkeiten, anstatt nur ein Excel -Problem zu sein. Ty für Ihre Überlegung

Von DwnldYoutubeRevanced

DwnldYoutubeRevanced on 01.10.2025 10:45 p.m.

SOURCE INFO 1
[https://www.torn.com/factions.php?step=profile&ID=48790](https://www.torn.com/factions.php?step=profile&ID=48790)
SOURCE INFO 2
[https://www.torn.com/factions.php?step=profile&ID=52917](https://www.torn.com/factions.php?step=profile&ID=52917)
TOOLS UED: BSP (Battle Stat Predictor)
this tool is needed to get the „STATS“ for each player on the source pages
[https://www.torn.com/forums.php#/p=threads&f=67&t=16290324&b=0&a=0](https://www.torn.com/forums.php#/p=threads&f=67&t=16290324&b=0&a=0)

Some more basic info:

I just paste the data from the factions into an excel sheet, this drops each bit of data into ONE column for some stupid formatting reason. I could have written a script to create CSVs but then I would still have to copy the CSV from one sheet to the master sheet so instead what I did is used the following functions (and I will start with a visual)

https://preview.redd.it/msw0o29ixksf1.png?width=779&format=png&auto=webp&s=0e41d7edafd1d04f01af9e815b3c14cd485c0543

Actual index: Used for vlookup on next page that collates all this data, will expound after this bit
STATS: =IF(A4=““,““,A4)
NAME: =IF(A5=““,““,A5)
LEVEL: =IF(A6=““,““,A6)

not sure why I specified those like that, I think I originally had a plan to allow me to copy row 3’s formulas down the whole page and have the right hand info show up on a specific que but I decided to just manually input the rows to read left hand data. Maybe it showed an N/A when it tried calling a blank column and that screwed up my array? anyway…

left: =IF(OR(RIGHT(D3,1)=“k“,RIGHT(D3,1)=“m“,RIGHT(D3,1)=“b“,RIGHT(D3,1)=“t“)=TRUE,IFERROR(LEFT(D3, LEN(D3)-1),““),D3)
right: =IF(OR(RIGHT(D3,1)=“k“,RIGHT(D3,1)=“m“,RIGHT(D3,1)=“b“,RIGHT(D3,1)=“t“)=TRUE,RIGHT(D3, 1),1)

yeah this could have been written a million times better… honestly I think I followed a shitty guide on this like a year ago and just never fixed it. but this is taking at most 100 rows of source data so idc to fix it.

right integer: =IF(H3=“k“,1000, IF(H3=“m“,1000000, IF(H3=“b“,1000000000, IF(H3=“t“,1000000000000, 1))))
sort by: =IFERROR(I3*G3,““)

iferr here just to wrap up errant errs I think idk

then on a separate sheet I basically collapse all the data on this sheet into one readable and easily transferable package to be put in the „functional spaces“ that read data for the histograms, sum total stats, and are sorted largest to smallest.

I just use a vlookup over the whole range on sheet1 starting at C3 indexing off of a 1-100 list as the „lookup value“

One other note is that my histograms are based off of the log10 of people’s stats as that condenses the histograms efficiently while keeping the information actionable. when doing this in order to ensure data falls into appropriate bins the formula giving data that the histogram reads actually looks like this:

=IFS(N30>0,IFERROR(ROUNDDOWN(LOG10(N29),0),NA())+0.1,N29>99,IFERROR(ROUNDDOWN(LOG10(N29),0),NA()),N29>0,1)

I will explain this in reverse order

IF3 is for edge cases where BSP gives a „wait“ which kind of screws up all the stat data and leaves it empty. This usually indicates stats of less than 100 (so less than 2 for log10 result) and this basically turns anything between 0 and 99 (non inclusive of 0/99) into a „1“ bin on the histogram. These have to be a 1 and not a 1.1 or 1.x because if you start with any number OTHER than an integer (no decimal) then the bins will go from 1.x-2.x by default.

IF2 is the arg that is ran on a minimum stat amount between 99 and technically no + bound but the previous functions are only suited to handle 3 digit trillions at most (which will cover 99.9% of interactions in game. Why Rounddown? This rounddown keeps the minimum number as a flat integer and stops bins from starting at some decimal.

IF1 is the typical output and just adds .1 to the result of each log to isolate data into the correct bins. If my min is 1 then all stats until 100 will be included in that bin, but log10(100) is 2 flat and since the bins are 1-2, 2-3, 3-4 (last number inclusive) that means 100 will be included with the 10-99 stat data. This +.1 allows me to make cleaner more accurate bins. I realize there are other ways to do this but when I was writing this it was my easiest work around.

View 1 Comment

1 Kommentar

DwnldYoutubeRevanced on 01.10.2025 10:45 p.m.

SOURCE INFO 1
[https://www.torn.com/factions.php?step=profile&ID=48790](https://www.torn.com/factions.php?step=profile&ID=48790)
SOURCE INFO 2
[https://www.torn.com/factions.php?step=profile&ID=52917](https://www.torn.com/factions.php?step=profile&ID=52917)
TOOLS UED: BSP (Battle Stat Predictor)
this tool is needed to get the „STATS“ for each player on the source pages
[https://www.torn.com/forums.php#/p=threads&f=67&t=16290324&b=0&a=0](https://www.torn.com/forums.php#/p=threads&f=67&t=16290324&b=0&a=0)

Some more basic info:

I just paste the data from the factions into an excel sheet, this drops each bit of data into ONE column for some stupid formatting reason. I could have written a script to create CSVs but then I would still have to copy the CSV from one sheet to the master sheet so instead what I did is used the following functions (and I will start with a visual)

https://preview.redd.it/msw0o29ixksf1.png?width=779&format=png&auto=webp&s=0e41d7edafd1d04f01af9e815b3c14cd485c0543

Actual index: Used for vlookup on next page that collates all this data, will expound after this bit
STATS: =IF(A4=““,““,A4)
NAME: =IF(A5=““,““,A5)
LEVEL: =IF(A6=““,““,A6)

not sure why I specified those like that, I think I originally had a plan to allow me to copy row 3’s formulas down the whole page and have the right hand info show up on a specific que but I decided to just manually input the rows to read left hand data. Maybe it showed an N/A when it tried calling a blank column and that screwed up my array? anyway…

left: =IF(OR(RIGHT(D3,1)=“k“,RIGHT(D3,1)=“m“,RIGHT(D3,1)=“b“,RIGHT(D3,1)=“t“)=TRUE,IFERROR(LEFT(D3, LEN(D3)-1),““),D3)
right: =IF(OR(RIGHT(D3,1)=“k“,RIGHT(D3,1)=“m“,RIGHT(D3,1)=“b“,RIGHT(D3,1)=“t“)=TRUE,RIGHT(D3, 1),1)

yeah this could have been written a million times better… honestly I think I followed a shitty guide on this like a year ago and just never fixed it. but this is taking at most 100 rows of source data so idc to fix it.

right integer: =IF(H3=“k“,1000, IF(H3=“m“,1000000, IF(H3=“b“,1000000000, IF(H3=“t“,1000000000000, 1))))
sort by: =IFERROR(I3*G3,““)

iferr here just to wrap up errant errs I think idk

then on a separate sheet I basically collapse all the data on this sheet into one readable and easily transferable package to be put in the „functional spaces“ that read data for the histograms, sum total stats, and are sorted largest to smallest.

I just use a vlookup over the whole range on sheet1 starting at C3 indexing off of a 1-100 list as the „lookup value“

One other note is that my histograms are based off of the log10 of people’s stats as that condenses the histograms efficiently while keeping the information actionable. when doing this in order to ensure data falls into appropriate bins the formula giving data that the histogram reads actually looks like this:

=IFS(N30>0,IFERROR(ROUNDDOWN(LOG10(N29),0),NA())+0.1,N29>99,IFERROR(ROUNDDOWN(LOG10(N29),0),NA()),N29>0,1)

I will explain this in reverse order

IF3 is for edge cases where BSP gives a „wait“ which kind of screws up all the stat data and leaves it empty. This usually indicates stats of less than 100 (so less than 2 for log10 result) and this basically turns anything between 0 and 99 (non inclusive of 0/99) into a „1“ bin on the histogram. These have to be a 1 and not a 1.1 or 1.x because if you start with any number OTHER than an integer (no decimal) then the bins will go from 1.x-2.x by default.

IF2 is the arg that is ran on a minimum stat amount between 99 and technically no + bound but the previous functions are only suited to handle 3 digit trillions at most (which will cover 99.9% of interactions in game. Why Rounddown? This rounddown keeps the minimum number as a flat integer and stops bins from starting at some decimal.

IF1 is the typical output and just adds .1 to the result of each log to isolate data into the correct bins. If my min is 1 then all stats until 100 will be included in that bin, but log10(100) is 2 flat and since the bins are 1-2, 2-3, 3-4 (last number inclusive) that means 100 will be included with the 10-99 stat data. This +.1 allows me to make cleaner more accurate bins. I realize there are other ways to do this but when I was writing this it was my easiest work around.

Du musst angemeldet sein, um einen Kommentar abzugeben.