그래서 다음과 같은 요구 사항이 있습니다.
시트 1
xxxx
YYYY
ZZZZ
시트 2
1994 xxx
1995 xxx
1996 xxx
1994 YYY
1995 YYY
1996 YYY
1994 ZZZ
1995 ZZZ
1996 ZZZ
본질적으로 제가 원하는 것은 시트 1에서 회사 이름을 복사하여 각 연도에 붙여넣고 거의 600개 회사에 대해 반복하는 것입니다.
이 작업을 수행하는 수식이나 VBA 코드가 있습니까? 어떤 도움이라도 정말 감사하겠습니다
답변1
회사 목록을 선택하고 데이터>데이터 가져오기 및 변환>테이블/범위에서를 사용합니다.
그러면 파워 쿼리 편집기가 열립니다. 다음과 같은 내용이 표시됩니다.
홈>닫기 및 로드 사용
이제 시작 연도와 종료 연도 사이의 동적 연도 목록을 만드는 시간을 갖도록 하겠습니다. 나중에 시작과 끝을 변경하려는 경우 유용합니다.
통합 문서의 별도 셀에 시작 연도와 종료 연도를 입력합니다.
시작 연도를 선택한 다음 이름 상자에 start_year와 같은 이름을 입력하여 셀 이름을 지정합니다.
셀 이름을 지정한 후 셀을 선택하고 데이터>데이터 가져오기 및 변환>테이블/범위에서를 사용합니다.
열 머리글이 Column1인 단일 셀로 파워 쿼리 편집기가 열리면 해당 셀을 마우스 오른쪽 버튼으로 클릭하고 드릴다운을 선택합니다.
왼쪽의 쿼리 창을 확장하면 다음이 표시됩니다.
홈>닫기 및 다음으로 로드를 사용하고 연결만 생성을 선택합니다.
이를 연말에 반복하여 다음을 얻습니다.
다시 한번 닫고 로드하고 연결만 생성합니다.
이제 Excel에서 데이터>쿼리 및 연결을 클릭합니다. 당신은 이것을보아야합니다 :
내 것은 50개의 회사 이름으로 더미 데이터를 생성했기 때문에 '50행 로드됨'이라고 표시됩니다. 귀하의 경우는 다를 수 있습니다.
Table1 쿼리를 마우스 오른쪽 버튼으로 클릭하고 '편집'을 선택합니다. 그러면 파워 쿼리 편집기가 다시 열립니다.
이제 홈>고급 편집기로 이동합니다. 다음과 같아야 합니다.
연도 목록을 생성하기 위해 몇 가지 코드를 추가하겠습니다.
두 번째 줄에 다음 줄을 추가합니다.
years = List.Numbers(start_year,end_year),
이와 같이:
이제 완료를 클릭하세요. 아무것도 변하지 않은 것처럼 보이지만 걱정하지 마세요.
열 추가>사용자 정의 열을 사용하고 다음과 같이 구성합니다.
확인을 클릭하면 다음과 같이 표시됩니다.
연도 열 상단의 이중 화살표를 클릭하고 '새 행으로 확장'을 클릭하세요.
이제 회사와 연도의 모든 조합에 대한 행이 표시됩니다.
홈>닫기 및 로드를 사용하여 통합 문서에 다시 넣으세요.
회사 목록이나 시작 및/또는 종료 연도가 변경되면 원본 데이터를 변경하고 이 쿼리를 새로 고치면 됩니다.
답변2
그것은 매우 쉬운 일입니다. 방법은 다양하지만 방금 설명한 내용에 가장 적합한 방법은 다음과 같습니다.
회사 이름이 몇 개인지 모두 합해 보세요. ( Ctrl-Down Arrow그들의 열을 누르고 마지막 행이 무엇인지 확인하십시오. 그 중 하나와 첫 번째 행을 사용하여 존재하는 수를 확인하십시오. 이 답변을 위해 587이라고 가정하십시오.)
다른 시트에서는 열을 선택하고 첫 번째 연도를 입력한 다음 복사하여 총 587개의 셀을 채웁니다. A1에서 시작하여 A587에서 끝난다고 가정해 보겠습니다. A588 셀에 수식을 입력합니다 =A1+1. 이를 복사하여 다른 587개 셀을 채웁니다. 셀을 복사한 다음 해당 셀과 나머지 587개 셀(A588:A1174)을 강조 표시하고 붙여넣으면 됩니다. 이제 587개의 셀이 모두 강조 표시되고 수식이 채워졌습니다. 해당 블록을 복사하고 을 누른 다음 Ctrl-Down Arrow다시 Down Arrow다음 빈 셀(A1175)로 이동합니다.
이제 다시 붙여넣고 다음 빈 셀로 이동하세요. 나열하려는 모든 연도의 587 셀 블록이 될 때까지 계속하십시오. 항목의 전체 열을 강조 표시합니다(아마도 30년이었으므로 A1:A17610 셀을 강조 표시할 것입니다). 전체 묶음을 복사하고 Paste|Special|Values모두 영구 텍스트로 변경합니다.
587개의 회사 이름을 복사하여 B1:B587의 다음 열에 붙여넣습니다. 그런 다음 다음 빈 셀 B588로 이동하여 수식을 입력하고 =B1셀을 복사합니다. A 열로 이동하여 마지막으로 채워진 셀로 이동하거나 를 사용하여 F5 GoTo셀 B17610으로 이동합니다(연도가 있는 마지막 셀은 A17610이며 어쨌든 B 열에 있기를 원합니다). 어떻게 하든지 B17610으로 가십시오. 모든 빈 셀을 덮으려면 를 누르십시오 Shift-Up Arrow. (그 자체와 A588까지 이동하게 됩니다... 동일한 공식이기 때문에 A588 위에 붙여넣어도 괜찮습니다.)
강조표시한 후 붙여넣습니다. 그런 다음 A열에서 했던 것처럼 모든 B열 셀을 강조 표시한 다음 복사하여 Paste|Special|Values모두 영구 텍스트로 만듭니다.
그것은 단지 한 시간과 많은 번거로움처럼 보입니다. 사실, 약간의 실수를 하더라도 최대 2분 정도가 소요될 것으로 예상됩니다. 그렇지 않으면... 읽는 것처럼 보이지만 실제 생활에서는 빠릅니다.
완료.