엑셀왕초보의 생존엑셀-Vlookup을 이용한 AE listing
꾸k
2024.01.16
182
0
안녕하세요. 임실모 집필진으로 첫 글을 작성하게 되어 무척이나 기쁩니다.
사실 저는 엑셀, 워드 등을 아주 잘 못합니다. 임상시험실무자 분들 중에서는 아마도 저보다 엑셀을 못하시는 분보다는 잘하시는 분들이 훨씬 많을 것으로 생각이 됩니다.
그럼에도 불구하고 제가 엑셀에 대한 내용을 쓰는 이유는..
임상시험을 진행하다보면 eCRF 에서 이미 확인이 되는 정보임에도 별도의 listing 을 요청 받는 경우가 많습니다. SDV note 를 정리하는데도 도움이 되구요.
그 때마다 하나하나 수기로 기재하다보니 단순 오기도 많고 눈이 빠질 것 같기에..
저는 살기 위해 사비와 시간을 들여 탈X, 당X마켓 같은 곳에서 엑셀 원데이클래스 등으로 배운 것이기도 하고, 제가 이해하고 글을 쓸 수 있는 정도의 수준이라면 대부분의 분들이 이해하실수 있을 것이라 생각이 되어 부끄럽지만 첫 글로 써보게 되었습니다.
재야의 숨은 엑셀 고수분들께서는 제 글에서 틀린 부분이나, 보다 쉽고 편리한 방법에 대해 제 e-mail(minjeong331@gmail.com)으로 추가 의견을 주시면 추후 계속하여 임실모 여러분들께 소개해드리겠습니다. 🙂
첨부파일의 Data set 을 참고해 주세요.(Data set 은 제가 임의로 만든 가상의 데이터이므로 논리적으로 맞지 않을 수 있습니다.)
AE listing 을 위해 eCRF (CRScube)에서 Dataset 을 받았습니다.
이 경우 아래와 같이 1,2,3,4.. 숫자로 기재되어 있는 부분에 대해 중증도 경증, 중증, 관련성 있음, 없음 등으로 정리하여 보고드려야 합니다.
이것을 AE 수집항목인 중증도, 결과, IP인과관계, 교정치료, 의약품 조치, 예측여부, SAE 여부 및 구분으로 변경하고자 합니다.
다운 받은 CRF Data set 내에 다음과 같이 AE 항목에 대해 정의해 놓은 sheet 가 있습니다.
중증도의 경우 1:경증^2:중등증^3:중증^4:생명 위협과 같이 ^ 표시가 있는데, 먼저 이 부분을 먼저 나눠줍니다.
5번째 열을 드래그 한 후 데이터-텍스트 나누기를 클릭한 후 구분 기호로 나누기-기타 ^ -일반- 마침 순으로 클릭합니다.
그럼 이렇게 ^ 로 표시되었던 부분이 별도의 열로 나눠집니다.
현재는 가로로 되어 있는데 저희는 숫자(1,2,3)을 기준으로 해당하는 열(Vlookup)의 값을 찾을 것이므로 가로로는 진행이 어렵습니다.
전체표를 드래그 한 다음 control +C (복사) 후 붙여넣기를 원하는 셀에서 Alt->e->s(선택하여 붙여넣기)를 실행하면 팝업창 하단부에 행/열 바꿈이 있습니다. 여기서 행/열바꿈을 클릭 한 후 확인을 누릅니다.
그럼 이렇게 행/열이 바뀐 세로형의 테이블이 만들어 지는데
제일 앞열에 숫자 1,2,3,4,5 등을 추가해주면 준비표를 만드는 작업은 끝납니다.
앞서 보았던 AE 표에 열을 하나씩 더 추가해 줍니다.
이제 S1001 대상자의 AESEV 를 찾아보겠습니다.
=VLOOKUP(G2,LAYOUT!D34:L39,2)
G2(G2에 있는 숫자 1을 찾겠습니다), LAYOUT!D34:L39 (layout sheet 의 D34~L39까지의 범위에서 찾겠습니다), 2(AESEV는 D34~L39까지의 범위에서 2번째 열에 있습니다)
그럼 이대로 드래그..를 하면 안됩니다. 설정해 놓은 범위가 다 바뀝니다. 고정하고 싶은 부분에는 $ 를 붙여줍니다.
아래로 드래그를 할것이므로 G2, G3, G4.. 로 숫자(행)는 바뀌어도 되지만 G(열)는 바뀌면 안됩니다. G앞에만 $가 붙을때까지 열심히 F4를 눌러줍니다.
준비표 만들어 놓은 범위도 바뀌면 안됩니다. sheet 부터 행열까지 전부다 고정! (모두 $를 붙입니다.)
만들어 놓은 준비표 범위 내에서 AESEV 가 2열에 있는 것은 변함이 없으므로 2는 둡니다.
그 다음 아래로 쭉 드래그를 하면 원하는 정보가 뜹니다.
마찬가지로 AE out 도 찾아봅니다.
=VLOOKUP($I2,LAYOUT!$D$34:$L$39,3)
$I2 (I2에 있는 값: 숫자 1을 찾겠습니다. I열 고정하겠고 행은 바뀔수 있습니다.), LAYOUT!$D$34:$L$39(준비표는 행열모두 고정),3(LAYOUT!$D$34:$L$39 준비표에서 AEout 은 3열에 있습니다)
그리고 마지막, 이상태로 숫자가 있는 G열을 없앤다면..? 기껏 수식을 통해 찾아놓은 값들이 다 없어질테니...
H열을 그대로 복사하여, 그 위치에 그대로 붙여 넣기를 다시 해줍니다. 그러나 이 때 반드시 '서식'이 아닌 '값'으로 변경!
그럼 나중에 AESEV 열이 없어져도 값으로 바뀐 '2: 중증등' 들은 그대로 있습니다.
마찬가지로 같은 방법으로 스크리닝 번호 옆에 열을 생성하여 RN sheet 에서 대상자의 Random 번호를 찾아 붙여넣기도 가능합니다.
저는 개인적으로 2:중증등 처럼 숫자와 해당 값을 함께 보고 싶어서 이대로 사용하기는 합니다만, 외부 보고용으로 편집하려면 '2:중증등' 이 아니라 '중증등' 인 것이 편리하겠지요.
그럼 준비표를 만들 때 앞에 숫자 부분은 그대로 두고 '2: 중증등' 인 부분을 '중증등'으로 수정해주시면 됩니다.
참고로 Vlookup에서는 찾는데 기준이 되는 값(예시 파일에서는 에서는 숫자1,2,3,4,5...)이 가장 1번째 열에 있어야 해서 찾으려는 값(AESEV, AEOUT 등..) 앞에 숫자(1,2,3,4,5)열을 추가해 주었습니다.
Xlookup 같은 함수들도 자유롭게 구사하면 좀 더 편리할 것 같은데, 아직 제 수준에서는 Vlookup 만 이해하여 진행하고 있습니다.
귀한 시간 내어 읽어주신 실무자분들에게 조금이라도 도움이 되었기를 진심으로 바라며, 더 좋은 의견이나 방법들은 언제든지 공유 부탁드립니다 🙂
★ 엑셀파일 다운로드 ⇒ 임실모 참고용 엑셀
사실 저는 엑셀, 워드 등을 아주 잘 못합니다. 임상시험실무자 분들 중에서는 아마도 저보다 엑셀을 못하시는 분보다는 잘하시는 분들이 훨씬 많을 것으로 생각이 됩니다.
그럼에도 불구하고 제가 엑셀에 대한 내용을 쓰는 이유는..
임상시험을 진행하다보면 eCRF 에서 이미 확인이 되는 정보임에도 별도의 listing 을 요청 받는 경우가 많습니다. SDV note 를 정리하는데도 도움이 되구요.
그 때마다 하나하나 수기로 기재하다보니 단순 오기도 많고 눈이 빠질 것 같기에..
저는 살기 위해 사비와 시간을 들여 탈X, 당X마켓 같은 곳에서 엑셀 원데이클래스 등으로 배운 것이기도 하고, 제가 이해하고 글을 쓸 수 있는 정도의 수준이라면 대부분의 분들이 이해하실수 있을 것이라 생각이 되어 부끄럽지만 첫 글로 써보게 되었습니다.
재야의 숨은 엑셀 고수분들께서는 제 글에서 틀린 부분이나, 보다 쉽고 편리한 방법에 대해 제 e-mail(minjeong331@gmail.com)으로 추가 의견을 주시면 추후 계속하여 임실모 여러분들께 소개해드리겠습니다. 🙂
첨부파일의 Data set 을 참고해 주세요.(Data set 은 제가 임의로 만든 가상의 데이터이므로 논리적으로 맞지 않을 수 있습니다.)
AE listing 을 위해 eCRF (CRScube)에서 Dataset 을 받았습니다.
이 경우 아래와 같이 1,2,3,4.. 숫자로 기재되어 있는 부분에 대해 중증도 경증, 중증, 관련성 있음, 없음 등으로 정리하여 보고드려야 합니다.
이것을 AE 수집항목인 중증도, 결과, IP인과관계, 교정치료, 의약품 조치, 예측여부, SAE 여부 및 구분으로 변경하고자 합니다.
다운 받은 CRF Data set 내에 다음과 같이 AE 항목에 대해 정의해 놓은 sheet 가 있습니다.
중증도의 경우 1:경증^2:중등증^3:중증^4:생명 위협과 같이 ^ 표시가 있는데, 먼저 이 부분을 먼저 나눠줍니다.
5번째 열을 드래그 한 후 데이터-텍스트 나누기를 클릭한 후 구분 기호로 나누기-기타 ^ -일반- 마침 순으로 클릭합니다.
그럼 이렇게 ^ 로 표시되었던 부분이 별도의 열로 나눠집니다.
현재는 가로로 되어 있는데 저희는 숫자(1,2,3)을 기준으로 해당하는 열(Vlookup)의 값을 찾을 것이므로 가로로는 진행이 어렵습니다.
전체표를 드래그 한 다음 control +C (복사) 후 붙여넣기를 원하는 셀에서 Alt->e->s(선택하여 붙여넣기)를 실행하면 팝업창 하단부에 행/열 바꿈이 있습니다. 여기서 행/열바꿈을 클릭 한 후 확인을 누릅니다.
그럼 이렇게 행/열이 바뀐 세로형의 테이블이 만들어 지는데
제일 앞열에 숫자 1,2,3,4,5 등을 추가해주면 준비표를 만드는 작업은 끝납니다.
앞서 보았던 AE 표에 열을 하나씩 더 추가해 줍니다.
이제 S1001 대상자의 AESEV 를 찾아보겠습니다.
=VLOOKUP(G2,LAYOUT!D34:L39,2)
G2(G2에 있는 숫자 1을 찾겠습니다), LAYOUT!D34:L39 (layout sheet 의 D34~L39까지의 범위에서 찾겠습니다), 2(AESEV는 D34~L39까지의 범위에서 2번째 열에 있습니다)
그럼 이대로 드래그..를 하면 안됩니다. 설정해 놓은 범위가 다 바뀝니다. 고정하고 싶은 부분에는 $ 를 붙여줍니다.
아래로 드래그를 할것이므로 G2, G3, G4.. 로 숫자(행)는 바뀌어도 되지만 G(열)는 바뀌면 안됩니다. G앞에만 $가 붙을때까지 열심히 F4를 눌러줍니다.
준비표 만들어 놓은 범위도 바뀌면 안됩니다. sheet 부터 행열까지 전부다 고정! (모두 $를 붙입니다.)
만들어 놓은 준비표 범위 내에서 AESEV 가 2열에 있는 것은 변함이 없으므로 2는 둡니다.
그 다음 아래로 쭉 드래그를 하면 원하는 정보가 뜹니다.
마찬가지로 AE out 도 찾아봅니다.
=VLOOKUP($I2,LAYOUT!$D$34:$L$39,3)
$I2 (I2에 있는 값: 숫자 1을 찾겠습니다. I열 고정하겠고 행은 바뀔수 있습니다.), LAYOUT!$D$34:$L$39(준비표는 행열모두 고정),3(LAYOUT!$D$34:$L$39 준비표에서 AEout 은 3열에 있습니다)
그리고 마지막, 이상태로 숫자가 있는 G열을 없앤다면..? 기껏 수식을 통해 찾아놓은 값들이 다 없어질테니...
H열을 그대로 복사하여, 그 위치에 그대로 붙여 넣기를 다시 해줍니다. 그러나 이 때 반드시 '서식'이 아닌 '값'으로 변경!
그럼 나중에 AESEV 열이 없어져도 값으로 바뀐 '2: 중증등' 들은 그대로 있습니다.
마찬가지로 같은 방법으로 스크리닝 번호 옆에 열을 생성하여 RN sheet 에서 대상자의 Random 번호를 찾아 붙여넣기도 가능합니다.
저는 개인적으로 2:중증등 처럼 숫자와 해당 값을 함께 보고 싶어서 이대로 사용하기는 합니다만, 외부 보고용으로 편집하려면 '2:중증등' 이 아니라 '중증등' 인 것이 편리하겠지요.
그럼 준비표를 만들 때 앞에 숫자 부분은 그대로 두고 '2: 중증등' 인 부분을 '중증등'으로 수정해주시면 됩니다.
참고로 Vlookup에서는 찾는데 기준이 되는 값(예시 파일에서는 에서는 숫자1,2,3,4,5...)이 가장 1번째 열에 있어야 해서 찾으려는 값(AESEV, AEOUT 등..) 앞에 숫자(1,2,3,4,5)열을 추가해 주었습니다.
Xlookup 같은 함수들도 자유롭게 구사하면 좀 더 편리할 것 같은데, 아직 제 수준에서는 Vlookup 만 이해하여 진행하고 있습니다.
귀한 시간 내어 읽어주신 실무자분들에게 조금이라도 도움이 되었기를 진심으로 바라며, 더 좋은 의견이나 방법들은 언제든지 공유 부탁드립니다 🙂
★ 엑셀파일 다운로드 ⇒ 임실모 참고용 엑셀
목록보기
