사용 중인 보고서를 CSV 파일로 저장하고 데이터를 Excel 스프레드시트에 복사/붙여넣어 보았습니다. 아무것도 내 문제를 해결하지 못합니다.
Excel은 날짜 데이터 열을 mm/dd/yyyy 형식으로 변환하지만 데이터는 원본 데이터베이스와 CSV 파일에 dd/mm/yyyy로 저장됩니다. 따라서 데이터의 절반은 인식하지 못하고 나머지 절반은 잘못되었습니다.
예를 들어 생년월일은 2020년 1월 10일이고 날짜에서 추출하는 데이터베이스에는 2020년 10월 1일로 표시되며 Excel은 복사된 것으로 저장되지만 mm/dd/yyyy 형식을 고려하면 "날짜"를 사용하기 위한 다른 열의 계산은 날짜가 2020년 10월 1일이라고 생각합니다.
2020년 1월 24일과 같은 날짜는 2020년 1월 24일을 붙여넣은 것으로 저장되지만 24와 같은 달은 없기 때문에 이를 사용하여 계산하지 않습니다. 개별 셀을 검사하여 10을 사용하는 형식을 확인했습니다. /01/2020은 English America 형식으로 표시됩니다.
English Australia로 변경하면 dd/mm 값이 바뀌므로 값이 여전히 올바르지 않습니다. 숫자로 변환하면 애초에 올바른 날짜 형식으로 읽히지 않기 때문에 작동하지 않습니다. 이 형식 문제에 대한 해결책이 있습니까?
고마워요 나탈리
답변1
Excel에서는 컴퓨터의 지역 설정을 사용하여 csv 파일을 해석하고 Excel에 표시합니다.
날짜가 국제 형식인 dd/mm/yyyy인 경우(Windows 10을 사용한다고 가정) Windows 작업 표시줄(Excel이 아님)에서 지역 설정을 검색한 다음 "추가 날짜, 시간 및 지역 설정"을 검색하면 다음 위치에서 열립니다. 클래식 Windows 7 제어판. 날짜 설정으로 이동하여 csv 파일과 동일한 형식으로 변경하세요. 가져오기를 수행한 후 다시 변경할 수 있으므로 이전 설정을 기록해 두십시오.
예를 들어 에서 소수점 기호를 변경할 수도 있습니다. csv 파일 번호 형식에 따라 로 또는 그 반대로 할 수 있습니다.
여러 형식이 호환되지 않는 최악의 시나리오에서는 텍스트로 가져온 다음 성가신 텍스트 기능을 사용하여 직접 날짜로 변환해야 할 수도 있습니다.
=DATE(RIGHT(A1,4), MID(A1, FIND("/", A1)+1, 2), LEFT(A1,2))
답변2
당신은 특별한 사람입니다! 이 문제가 있는 거의 모든 사람들은 CSV를 열어 작동시킬 수 없다는 사실에 완전히 당황합니다. 그들은 다른 어떤 방법으로도 그렇게 하지 않을 것입니다. CSV에서 복사하여 붙여넣을 수 있을 뿐만 아니라 텍스트 형식을 지정하는 데 어려움을 겪은 열로 CSV를 가져올 수도 있습니다. 축하해요!
따라서 텍스트 열에 텍스트로 가져올 수 있습니다. 한 단계 더 수행하려는 경우 원하는 형식(mm/dd/yyyy)의 날짜일 수 있습니다. 두 단계만 더 거치면 됩니다.
먼저, 이제 열 데이터의 형식을 변경합니다. 숫자 형식 탭에서 날짜 컬렉션을 선택한 다음 탭 중앙에 있는 형식 예를 살펴보세요. 중앙의 예시 선택 항목 아래에 "Locale (location):"이라고 표시된 드롭다운이 있습니다... "English (Australia)"로 스크롤하여 선택하세요.
위의 날짜 형식 예는 변경되며 원하는 정확한 버전을 선택할 수 있습니다.
물론 텍스트 표현은 변경되지 않습니다. 이것이 실제 단계입니다. 강제("강제")를 해야 합니다. 이를 수행하는 방법은 많지만 가장 적합한 방법은 리본 메뉴, 데이터 탭으로 이동하는 것입니다. 찾아 Text to Columns클릭해 보세요.
구분 너비 또는 고정 너비는 실제로 중요하지 않습니다. 구분 경로로 이동하면 아무 것도 선택하지 않았는지 확인하여 "구분 기호 없음"을 선택하고 고정 너비가 수행되는 위치에 도달하게 되기 때문입니다. 3단계 중 3단계에서 다음을 수행할 수 있습니다. 변환 후 열의 형식 유형을 설정합니다.
"날짜" 라디오 버튼을 선택하고 오른쪽 드롭다운에서 을 선택합니다 DMY. 입력의 형식이 아니라 출력할 내용을 선택합니다.
"마침"을 클릭하면 작업이 완료됩니다.
앞으로는 파워 쿼리를 살펴보는 것이 좋습니다. 데이터에 대한 좋은 CSV가 있으므로 PQ가 사용하기 가장 간단한 방법입니다. 수식이나 프로그래밍이 필요 없으며 파일에서 데이터를 가져오고 필요하지 않은 모든 열을 버린 다음 날짜 열을 사용자가 지정한 형식으로 날짜로 변환합니다.
모두 저장하고 가져올 새 데이터가 있을 때마다 스프레드시트의 테이블을 업데이트하거나 도우미 스프레드시트를 사용하여 이를 수행하고 매번 결과를 생산 시트로 가져올 수 있습니다. 프로덕션 스프레드시트에 도우미 페이지가 있는 경우(이 경로로 이동하면 결과를 처음 "로드"할 때 자동으로 페이지가 생성됨) 기본 시트에 복사하여 붙여넣거나 수식을 사용하여 정보를 복사하세요.
매크로를 사용할 수 있거나 허용된 경우 쿼리를 업데이트하기 위해 매크로가 있는 버튼을 설정할 수 있습니다.
다른 것이 없다면 변환된 날짜를 로드한 다음 필요할 때 복사하여 붙여넣는 "도우미 스프레드시트"가 좋을 수 있습니다. 하지만 위의 방법은 매번 매우 쉬울 것입니다. 따라서 아마도 6개 중 6개, 나머지 6개일 것입니다.