Formuła ogólna
=LEFT(A1,LEN(A1)-2)/10^((MATCH(RIGHT(A1,2),("PB","TB","GB","MB","KB"),0)-3)*3)
Podsumowanie
Aby znormalizować jednostki do gigabajtów (lub megabajtów, kilobajtów itp.), Możesz użyć sprytnej formuły opartej na funkcjach MATCH, LEFT i RIGHT. W przedstawionym przykładzie formuła w C5 to:
=LEFT(B5,LEN(B5)-2)/10^((MATCH(RIGHT(B5,2),("PB","TB","GB","MB","KB"),0)-3)*3)
Uwaga: dla uproszczenia używamy wartości dziesiętnych (podstawa 10), ale istnieje również standard binarny. Zobacz poniżej.
Wyjaśnienie
Ważne: w tej formule przyjęto, że jednostki to ostatnie 2 znaki ciągu, który zawiera zarówno liczbę, jak i jednostkę miary.
Ta formuła działa, ponieważ jednostki cyfrowe mają zależność „potęga 10”.
Zasadniczo ta formuła oddziela część liczbową rozmiaru od jednostki, a następnie dzieli liczbę przez odpowiedni dzielnik w celu znormalizowania do gigabajtów. Dzielnik jest obliczany jako potęga 10, więc wzór sprowadza się do tego:
=number/10^power
Aby uzyskać liczbę, formuła wyodrębnia wszystkie znaki od lewej strony do jednostek, ale bez jednostek:
LEFT(B5,LEN(B5)-2)
Aby uzyskać „moc”, formuła dopasowuje jednostkę w zakodowanej na stałe tablicy stałej:
MATCH(RIGHT(B5,2),("PB","TB","GB","MB","KB"),0)
Zwraca pozycję jednostki w stałej tablicy. Na przykład dla wzoru w C5 jednostką jest „KB”, więc pozycja to 5. Ten wynik jest korygowany przez odjęcie 3, a następnie pomnożenie wyniku przez 3, co daje 6 jako potęgę, która jest używana jako wykładnik aby obliczyć poprawny wynik w gigabajtach:
=900/10^6 =900/1000000 =0.0009
Standardowa formuła binarna
Komputery używają systemu liczb binarnych do przechowywania i raportowania rozmiaru danych, ale przedrostki, takie jak „kilo”, „mega”, „giga” itp., Są oparte na systemie metrycznym. Jest to mylący temat, ale użycie jednostek rozmiaru dziesiętnego do przechowywania na komputerze nie jest naprawdę poprawne, a rozbieżność rośnie, gdy jednostki stają się większe. Poniższy wzór zostanie znormalizowany do jednostek binarnych.
=LEFT(A1,LEN(A1)-2)/2^((MATCH(RIGHT(A1,2),("PB","TB","GB","MB","KB"),0)-3)*10)
Dzięki tej formule technicznie uzyskujesz gigabajty (GiB), a nie gigabajty. Więcej informacji tutaj i tutaj.
