Generując BOM ze schematu do arkusza kalkulacyjnego Excel lub pliku CSV w kolumnie „wartość” dostajemy przeważnie wartości elementów według branżowej konwencji, czyli z użyciem symboli przedrostków SI, używaniem przedrostków bądź innych liter zamiast separatora dziesiętnego, oraz dodatkowych liter jako na przykład jednostek. Na domiar złego, w tej samej kolumnie mamy często wymieszane wartości elementów pasywnych (Omy, Henry, Farady) z MPN innych podzespołów. Przykładowo:
100n 1u BAT64-05 120R 15k 39k2 47R 15uH 220p 2u2
Nie muszę chyba dodawać, że Excel całkowicie nie radzi sobie z sortowaniem wartości w takiej kolumnie.
Oto formuła, która dokona konwersji na notację naukową. W przypadku błędu, wstawi pusty ciąg znaków, dzięki czemu sortowanie nie będzie zakłócone. Zakładam, że dane znajdują się w kolumnie A od komórki A1 – zmiany wymaga tylko jedno miejsce w formule.
Formuła dla Excela w j. Polskim:
=JEŻELI.BŁĄD(LET(
tekst;USUŃ.ZBĘDNE.ODSTĘPY(A1);
poz;MIN(
JEŻELI.BŁĄD(ZNAJDŹ("p";tekst);1E+99);
JEŻELI.BŁĄD(ZNAJDŹ("n";tekst);1E+99);
JEŻELI.BŁĄD(ZNAJDŹ("u";tekst);1E+99);
JEŻELI.BŁĄD(ZNAJDŹ("µ";tekst);1E+99);
JEŻELI.BŁĄD(ZNAJDŹ("m";tekst);1E+99);
JEŻELI.BŁĄD(ZNAJDŹ("R";tekst);1E+99);
JEŻELI.BŁĄD(ZNAJDŹ("Ω";tekst);1E+99);
JEŻELI.BŁĄD(ZNAJDŹ("k";tekst);1E+99);
JEŻELI.BŁĄD(ZNAJDŹ("M";tekst);1E+99);
JEŻELI.BŁĄD(ZNAJDŹ("G";tekst);1E+99)
);
JEŻELI(LUB(tekst="";poz>DŁ(tekst));"";LET(
prefiks;FRAGMENT.TEKSTU(tekst;poz;1);
czescLewa;LEWY(tekst;poz-1);
ogon;FRAGMENT.TEKSTU(tekst;poz+1;DŁ(tekst));
dlOgonu;DŁ(ogon);
ileCyfr;JEŻELI(dlOgonu=0;0;
JEŻELI.BŁĄD(
PODAJ.POZYCJĘ(
FAŁSZ;
JEŻELI.BŁĄD(
CZY.LICZBA(--FRAGMENT.TEKSTU(ogon;SEKWENCJA(dlOgonu);1));
FAŁSZ
);
0
)-1;
dlOgonu
));
koncowka;JEŻELI(ileCyfr<dlOgonu;PRAWY(ogon;dlOgonu-ileCyfr);"");
lewaPoprawna;ORAZ(
czescLewa<>"";
SUMA(--JEŻELI.BŁĄD(
CZY.LICZBA(--FRAGMENT.TEKSTU(czescLewa;SEKWENCJA(DŁ(czescLewa));1));
FAŁSZ
))=DŁ(czescLewa)
);
koncowkaPoprawna;JEŻELI(koncowka="";PRAWDA;
SUMA(--JEŻELI.BŁĄD(
CZY.LICZBA(--FRAGMENT.TEKSTU(koncowka;SEKWENCJA(DŁ(koncowka));1));
FAŁSZ
))=0
);
mantysa;czescLewa&JEŻELI(ileCyfr>0;","&LEWY(ogon;ileCyfr);"");
wykladnik;JEŻELI(prefiks="p";"-12";
JEŻELI(prefiks="n";"-9";
JEŻELI(LUB(prefiks="u";prefiks="µ");"-6";
JEŻELI(prefiks="m";"-3";
JEŻELI(LUB(prefiks="R";prefiks="Ω");"0";
JEŻELI(prefiks="k";"3";
JEŻELI(prefiks="M";"6";
JEŻELI(prefiks="G";"9";""))))))));
JEŻELI(ORAZ(lewaPoprawna;koncowkaPoprawna;wykladnik<>"");
mantysa&"e"&wykladnik;
"")
)));
"")
Formuła dla Excela w j. Angielskim (nie testowana):
```excel
=IFERROR(LET(
text,TRIM(D2),
pos,MIN(
IFERROR(FIND("p",text),1E+99),
IFERROR(FIND("n",text),1E+99),
IFERROR(FIND("u",text),1E+99),
IFERROR(FIND("µ",text),1E+99),
IFERROR(FIND("m",text),1E+99),
IFERROR(FIND("R",text),1E+99),
IFERROR(FIND("Ω",text),1E+99),
IFERROR(FIND("k",text),1E+99),
IFERROR(FIND("M",text),1E+99),
IFERROR(FIND("G",text),1E+99)
),
IF(OR(text="",pos>LEN(text)),"",LET(
prefix,MID(text,pos,1),
leftPart,LEFT(text,pos-1),
tail,MID(text,pos+1,LEN(text)),
tailLen,LEN(tail),
digitCount,IF(tailLen=0,0,
IFERROR(
MATCH(
FALSE,
IFERROR(
ISNUMBER(--MID(tail,SEQUENCE(tailLen),1)),
FALSE
),
0
)-1,
tailLen
)),
ending,IF(digitCount<tailLen,RIGHT(tail,tailLen-digitCount),""),
leftValid,AND(
leftPart<>"",
SUM(--IFERROR(
ISNUMBER(--MID(leftPart,SEQUENCE(LEN(leftPart)),1)),
FALSE
))=LEN(leftPart)
),
endingValid,IF(ending="",TRUE,
SUM(--IFERROR(
ISNUMBER(--MID(ending,SEQUENCE(LEN(ending)),1)),
FALSE
))=0
),
mantissa,leftPart&IF(digitCount>0,"."&LEFT(tail,digitCount),""),
exponent,IF(prefix="p","-12",
IF(prefix="n","-9",
IF(OR(prefix="u",prefix="µ"),"-6",
IF(prefix="m","-3",
IF(OR(prefix="R",prefix="Ω"),"0",
IF(prefix="k","3",
IF(prefix="M","6",
IF(prefix="G","9","")))))))),
IF(AND(leftValid,endingValid,exponent<>""),
mantissa&"e"&exponent,
"")
))),
"")
```
Wynik dla wcześniej pokazanych przykładowych danych:
100e-9 1e-6 120e0 15e3 39.2e3 47e0 15e-6 220e-12 2.2e-6
Mam nadzieję, że będzie przydatna