SUBSTITUTE FORMULA EXCEL - przełączanie numerów

0

Próbuję zastąpić zestaw liczb 1,2,3,4,5 odwrotną liczbą w ramach obliczeń, które należy wykonać. 1 = 5, 2 = 4, 3 = 3, 4 = 2, 5 = 1.

Formuła, której używam, to

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F13,"2","4",1 ),"1", "5", 1 ),"4", "2", 1 ),"5", "1", 1 ),"2","4", 1)

Działa jednak tylko dla liczb 2 i 5 i nie zastępuje 1 i 4.

microsoft-excel worksheet-function

— Ryann
źródło

czy możesz podać jakieś dane testowe i oczekiwany wynik?

— Scott Craner

Czy twój numer to tylko numer, czy mają ,jak wyżej?

— Scott Craner

Jeśli nie masz ochoty na MAKRO, Ican sugeruje, że proste Makro przepisze zawartość komórki w odwrotnej kolejności.

— Rajesh S

0

Spowoduje to zastąpienie liczb (bez tekstu)

=SUMPRODUCT((6-MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1))*10^(LEN(A1)-ROW($A$1:INDEX(A:A,LEN(A1)))))

Po prostu iteruje liczbę odejmując każdą część od 6, aby uzyskać liczbę odwróconą, a następnie mnoży ją przez współczynnik 10, aby ułożyć we właściwej kolejności.

Trochę komplikuje się z łańcuchami. możemy użyć tablicowej wersji CONCAT, która została wprowadzona wraz z Office 365 Excel.

=CONCAT(IF(ISNUMBER(--MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)),6-MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1),MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)))

Będąc formułą tablicową, należy wyjść z trybu edycji za pomocą Ctrl-Shift-Enter zamiast Enter.

To także iteruje ciąg i jeśli jest liczbą, odejmuje 6 i zwraca z powrotem concat.

Jeśli chcesz kontynuować zagnieżdżone SUBSTITUTES, musisz zmienić dwie liczby na coś innego przed zamianą dwóch pozostałych. Następnie przełącz znaki specjalne na wyjście pożądania.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,"!"),2,"@"),4,2),5,1),"@",4),"!",5)

— Scott Craner
źródło

Dziękuję bardzo, to było bardzo pomocne! Mam do pracy ze skorygowaną formułą SUBSTITUTE.

— Ryann

Proszę uznać oznaczenie za poprawne, klikając znacznik wyboru obok odpowiedzi.

— Scott Craner

0

Napisz tę formułę macierzy w komórce B2:

{=RIGHT(SUM(VALUE(MID(A2&"1",ROW(INDIRECT("1:"&LEN(A2&"1"))),1))*10^(ROW(INDIRECT("1:"&LEN(A2&"1")))-1)),LEN(A2))}

Uwaga: zakończ formułę za pomocą Ctrl+Shift+Enter.

W przypadku oddzielonej przecinkami listy liczb / tekstu Skopiuj i wklej poniższy kod VBA jako moduł z arkuszem.

Function ReverseTxt(ByVal Target As Range)
    myLength = Len(Target.Value)
    For x = 0 To myLength
        ReverseTxt = Left(WorksheetFunction.Substitute(Target.Value, Left(Target.Value, x), ""), 1) & ReverseTxt
    Next x
End Function

Napisz UDF w komórce B4i wypełnij:=ReverseTxt(A2)

NB

Powyżej pokazany UDF nadaje się do listy pojedynczych cyfr / znaków.
Jeśli spróbujesz UDF, 12, 13to będzie 31, 21i DAbędzie AD.

— Rajesh S.
źródło