식물, 수량, 가격이 나열된 테이블이 있습니다. 어쨌든 대부분의 가격에는 마크업 승수를 적용하려고 할 때 #VALUE 오류가 발생하는 것으로 생각되는 몇 가지 선행 공백이 있습니다.
Trim, Trim(Clean), 복사/붙여넣기, 찾기/바꾸기를 시도했지만 아무것도 작동하지 않습니다. 끝에는 여전히 앞에 공백이 있는 숫자가 있습니다.
심지어 csv로 내보낸 다음 문제를 일으키는 형식이 있다는 생각으로 다시 Excel로 가져왔습니다.
이런 젠장? 그리고 검토를 위해 시트의 한 부분을 게시할 수 있는 방법이 있나요?
답변1
숫자에는 다양한 유형의 공백(줄바꿈 없는 공백)이 있습니다. 이들은 일반적으로 CHAR(160). Excel에서는 이를 유효한 문자로 인식하므로 CLEAN이 아닌 TRIM도 이를 제거하지 않습니다. 그러나 이는 "숫자" 텍스트 문자열을 생성하므로 곱할 수 없습니다.
따라서 SUBSTITUTE를 사용하여 해당 문자를 제거하고 좋은 측정을 위해 TRIM 및 CLEAN을 삽입합니다.
=TRIM(CLEAN(SUBSTITUTE(C2,CHAR(160),"")))*2.5
답변2
수많은 "잘리지 않는 공백"는 에서 처리되지 않습니다 CLEAN(). 웹 페이지는 PDF와 마찬가지로 추가 사용을 위해 데이터를 긁어 모으는 것이 아니라 시각적 표현에만 관심이 있기 때문에 웹 페이지에서 데이터를 가져올 때 매우 일반적입니다. 그러나 특히 다음과 같은 경우 데이터를 출력하는 프로그램에서 발생합니다. 그들은 데이터의 일반적인 사용을 묘사하지 않고 그것을 사용하는 제한된 사람들의 집합을 묘사합니다. 또는 28년 전에 CSV 출력 기능이 작성되었을 때 결코 업데이트되지 않았습니다.
숫자가 아닌 부분은 문자 그대로 숫자 부분만 원하기 때문에 스프레드시트에서 문제를 처리하기가 더 쉽습니다.
값이 음수라는 표시를 유지해야 한다면 더 어려울 것입니다. 또는 이러한 공백이 아닌 문자. 또는 나처럼 출력이 이산 숫자여야 하는 문자열을 얻습니다. "90.02 45.55 .062"와 같은 문자열이지만 추출할 수 있도록 보존됩니다.
그러나 단일 문자열의 숫자와 Excel에서 고려한 숫자만 있으면 됩니다. 다음 수식을 사용하면 됩니다.
=VALUE(TEXTJOIN(,TRUE,IFERROR(MID(C1,SEQUENCE(1,LEN(C1)),1)*1,"")))
SEQUENCE()1씩 증가하고 대상의 길이만큼 긴 일련의 값을 얻는 데 사용됩니다 . 그런 다음 이를 사용하여 MID()대상의 문자 행렬을 출력하는 함수의 시작 지점을 지정합니다. 그런 다음 *1행렬의 숫자에 대해 작동하는 수학 연산( )을 수행 하지만 숫자(0-9)가 아닌 모든 것에 대해서는 오류를 제공합니다. IFERROR()오류가 발생한 모든 곳(대상에 숫자가 없는 곳)에서 해당 문자열을 ""가 있는 숫자로 변환합니다. TEXTJOIN()그런 다음 해당 행렬을 간단한 출력 문자열로 변환하고 해당 "" 요소를 무시하므로 이제 대상에 존재하는 숫자만으로 구성된 유용한 문자열을 갖게 됩니다. 텍스트로 간주되어 표시됩니다.
현재로서는 상당히 최신 버전의 Excel을 사용하는 경우 이 수식이 유용합니다. 그 이유는 Excel이 텍스트로 형식이 지정된 경우에도(결국 기본적인 방식으로 변경되지 않고 형식이 지정된 경우에도) 숫자 문자열을 REQUIRES 또는 합리적으로 기대하는 함수에 제시된 경우 숫자로 인식하기 때문입니다. , 입력으로 숫자 데이터만 사용됩니다. 따라서 "C1+1"은 해당 텍스트를 숫자로 간주하여 오류를 발생시키거나 0+1=1을 제공하는 대신 추가 작업을 수행합니다.하지만,모든 함수가 출력을 이런 방식으로 처리하는 것은 아닙니다. VLOOKUP()이 경우에는 텍스트로 간주하고 숫자 표에서 일치하는 항목을 찾지 못해 오류가 발생합니다. 그래서 VALUE()결과를 숫자로 간주하고 결과를 조회 함수의 입력으로 사용하면 다른 사람처럼 작동할 수 있도록 위의 내용을 마무리했습니다 .
따라서 해당 기능을 삭제해도 원하는 대로 마크업 곱셈이 계속 수행됩니다.
그러나 새 기능이 없거나 SPILL특히 꽤 오래된 Excel 버전을 사용하는 경우 다음 수식은 기본적으로 Windows용 Excel의 모든 버전에서 작동하며 항상 숫자 출력을 생성합니다.
{=ROUND(NPV(-0.9,0,IFERROR(MID(L1,LEN(C1)-ROW(INDIRECT("C1:I"&LEN(C1)))+1,1)/100,"")),3)}
참고로 이는CSE공식. 일반 수식으로 사용되면 마지막 두 자리가 제거되고 때로는... 출력 형식이 통화로 지정됩니다. (Excel이 도움이 됩니다... NPV()금융 기능은 반드시 통화 형식으로 지정해야 하기 때문입니다. 그렇죠?)
위의 공식은 "아주 오래 전 인터넷 어딘가"에서 나온 것입니다... 출처를 밝히고 싶지만 출처가 기억나지 않습니다. 도움이 된 전문가에게 사과드립니다.
제가 많은 곳에 그런 정보를 갖고 있고 어떻게 SEQUENCE()도움이 될 수 있는지 업데이트해야 했기 때문에 질문해 주셔서 감사합니다. 함수의 내부 행렬에서 출력되는 유용한 문자열로의 변경 사항을 바꾸는 것은 말할 것도 없습니다. TEXTJOIN()이제 그것도 사용할 수 있기 때문입니다! 즉각적인 혜택! 정말 멋진 일이에요!