旻天之心 's Home

*. 여러 열의 텍스트를 연결 또는 조인하는 방법

& 연산자 또는 CONCATENATE 함수를 사용하여 여러 열의 텍스트를 연결 또는 조인할 수 있습니다.

예를 들어, 다음과 같이 셀 A1:C4에 이름의 데이터가 입력된 경우에 D1:D4에 합치는 경우

A1: 홍 B1: 길  C1: 동   D1:
A2: 이 B2: 천  C2: 사   D2:
A3: 박 B3: 민  C3: 천   D3:
A4: 이 B4: 태  C4: 백   D4:

전체 이름을 만들려면 셀 D1에 다음 수식 중 하나를 입력합니다.

방법1)
$D$1: =CONCATENATE(A1," ",B1," ",C1)

방법2)
$D$1: =A1&" "&B1&" "&C1

*. 텍스트 사이에 공백을 삽입하기 위해 셀 사이에 공백(" ")을 사용랍니다.

*. 엑셀에서 일반 TEXT 숫자로된 20071015 를 2007-10-15 형태로 바꾸는 방법 

일반 TEXT 숫자로된 20071015

이숫자를 2007-10-15  이런식으로 바꿀려고 하니

셀서식 날짜로 지정을 하니까 ############# 이렇게 나오는 경우


방법1)

=TEXT(DATE(MID(B7,1,4),MID(B7,5,2),MID(B7,7,2)),"YYYY-MM-DD") 로 하시면 해결 됩니다.

이후에 해당 셀을 복사를 하신 다음 편집-선택하여 붙여넣기-값으로 하시면 됩니다.

◆ 엑셀 하이퍼링크 한번에 다 지우기(1)

한번에 전체 링크를 지울 수 있는 간단한 방법을 소개합니다.

1. ALT+F11 을 눌러서 VBE 창으로 가신 후에..

2. CTRL+G 를 눌러서 직접실행창으로 가셔서

3. 거기에 CELLS.Hyperlinks.Delete 를 입력한다.

4. Enter Key 를 누른다.

5. VBA 편집창을 닫는다.

6. 엑셀 파일을 보시면 하이퍼링크가 모두 삭제되어 있음을 확인할수 있다.

◆ 엑셀 특정범위의 하이퍼링크 지우기(2)

1. 지우려는 Sheet 의 빈셀에 임시로 숫자 1을 입력한다.  

2. 숫자 1이 입력된 셀을 선택하고 복사(CTRL+C) 한다.

3. 지우고자 하는 하이퍼링크가 걸려 있는 범위를 선택하고,
   오른쪽 마우스 버튼을 누르고 "선택하여 붙여넣기" 를 선택하여 해당 메뉴가 표시되면
   "연산" 의 "곱하기"를 선택하고 "확인" 버튼을 누른다.

4. 하이퍼링크가 삭제되어 있음을 확인할수 있다.

5. 1단계에서 빈셀에 임시로 입력한 숫자 1을 삭제한다.

*. VLOOKUP 사용법

    설명 :  테이블의 첫 행에 있는 값 또는 값의 배열을 검색한 다음 테이블 또는 배열에 지정한 행으로 부터 같은 행에 있는 값을 반환합니다.

    형식 : VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

      Lookup_value : table_array의 첫 번째 열에 기록된 내용 중 사용자가 찾고자 하는 검색 조건. 즉, 사원 명부에서 비교하여 찾을 값으로 사번으로 찾고자 할 때는 사번, 이름으로 찾고자 할 때는 이름이 있는 셀을 선택한다.  숫자, 참조영역, 또는 문자열로 지정할 수 있다.
      Table_array  : 검색하려는 데이터들이 포함된 셀을 시작으로, 비교하여 가져올 데이터가 있는 열까지의 영역. 즉  부서 데이터를 가져올려고 할 때는 부서 영역까지 선택. 영역을 많이 선택하는 것은 허용됨
       * table_array의 첫 열에는 lookup_value와 비교 하는 값
       Col_index_num : table_array의 첫 번째 열에서 lookup_value가 기록된 행을 찾은 후 동일 행에서 얻고자하는 데이터가 포함되어 있는 열 번호를 말한다. 따라서, col_index_num로 지정한 숫자가 1보다 작거나 table_array의 전체 열수보다 크면 각각 #VALUE! 오류, #REF!오류가 나타난다
      Range_lookup : 정확하게 일치하는 값을 찾을 때는 false 입력하고 , 근사값을 찾고자 할 때는 true나 생략을 한다. 단 근사값을 정확히 찾을려면 table_array 영역의 첫 열 기준으로 반드시 올림차순(1,2,3..., abc..., 가나다...순)으로 정렬을 되어야 정확한 근사값을 얻을 수 있다.

예)

Vlookup 의 마지막 옵션을 false 로 놓으면 정확히 일치된 값만을 가져 옵니다.

=VLOOKUP(A2,[파일명.xls]sheet1!$A$2:$H$62,1,FALSE)

=VLOOKUP(A2,$F$2:$G$153,2,FALSE)

일치된 값이 없을때 공백으로 처리하는 경우에 사용한다.

=IF(ISERROR(VLOOKUP(A2,$F$2:$G$153,2,0)),"",VLOOKUP(A2,$F$2:$G$153,2,0))

엑셀에서 셀분리하기

A1셀에 데이터가 들어있고 이를 B1셀과 C1셀에 분리하여 싶다면,

가.  데이터가 일정한 간격으로 되어 있는 경우
      예) 4자리-4자리'의 형태로 되어 있다면,
           B1에는 =left(A1,4)
           C1에는 =right(A1,4)

나. 자리수는 불규칙하나 중간에 구분자로 이를 앞뒤로 나누면
      예) 구분자가 "/" 인 경우
           B1에는 =left(A1,find("/",A1)-1)
           C1에는 =right(A1,len(A1)-find("/",A1))

를 사용하면 된다.

변경하고자 하는 셀의 위치(A1)에서 다음과 같이 처리하면 된다.

=upper(A1) : 모두 대문자로 표시

=lower(A1) : 모두 소문자로 표시

=proper(A1) : 첫글자만 대문자이고 나머지는 소문자 표시

여러개로 작성된 엑셀자료를 하나의 엑셀에 합치는 마크로 입니다.

첨부된 파일을 실행하면 "마크로"를 실행하는 아이콘이 나오는데 그것을 편집하여 원하는 만큼의 엑셀파일의 경로와 이름을 지정해주면 된다.

   

엑셀에서 문서 작성 도중 여러 개의 셀값의 숫자들을 반올림 해야 하는 경우가 있다 .

round는 반올림 즉, 4이하는 버리고 5이상은 반올림하는 함수
rounddown는 무조건 버리는 함수입니다.
roundup는 무조건 올림하는 함수입니다.

말하자면..

=ROUND(P9*G9/1000,2)이것이 의미하는 것이 무엇입니까?
P9 G9에는 중량과 길이 입니다!

"중량과 길이를 곱하여 1000으로 나눈값을 반올림하여 소수둘째자리(소수셋째자리에서 반올림하여)까지 표시한다"..라는뜻입니다.

예를 들어  34*69=2346 이며, 이를 1000으로 나누면 2.346   이 됩니다
이를 (소수셋째자리에서 반올림하여) 소수둘째자리까지 표시하려면 2.35가 됩니다..

따라서 여기서 2는 "소수이하 두자리까지 표시하시오..셋째자리에서 반올림하여"  이런뜻입니다.

정수단위를 반올림하거나 올림, 버림할때는 반드시 -(마이너스)를 붙여합니다. 또한 반올림되는 자릿수를 표현한다.

천단위에서 반올림하여 만단위까지 표시 =round(1545678,-4)
백단위에서 반올림하여 천단위까지 표시 =round(1545678,-3)
십단위에서 반올림하여 백단위까지 표시 =round(1545678,-2)

반대로 소수점을 반올림할 때는 -(마이너스)를 붙이지 않는다 또한 이때는 표시하는 자리수를 쓴다

소수셋째자리에서 반올림하여 소수둘째짜리까지 표시  
       =round(1545678,2)

소수둘째자리에서 반올림하여 소수첫째짜리까지 표시
       =round(1545678,1)

소수첫째자리에서 반올림하여 정수로자리까지 표시  
       =round(1545678,0)

Sheet의 C1의 값에서 B1의 값의 차이를 00년 00월 00일로 표시한다
C1과 B1의 날짜 형태는 YYYY/MM/DD의 형태를 기준으로 한다
( C1 의 값 2007/03/05, B1 의 값 2005/02/01 )

방법1)

=YEAR(c1)-YEAR(b1)-IF(OR(MONTH(c1)<MONTH(b1),AND(MONTH(c1)=MONTH(b1),DAY(c1)<DAY(b1))),1,0) & "년 "&MONTH(c1)-MONTH(b1)+IF(AND(MONTH(c1)<=MONTH(b1),DAY(c1)<DAY(b1)),11,IF(AND(MONTH(c1)<MONTH(b1),DAY(c1)>=DAY(b1)),12,IF(AND(MONTH(c1)>MONTH(b1),DAY(c1)<DAY(b1)),-1))) & "개월 "&c1-DATE(YEAR(c1),MONTH(c1)-IF(DAY(c1)<DAY(b1),1,0),DAY(b1)) & "일"

방법2)

=DATEDIF(B1,C1,"y")&"년"&DATEDIF(B1,C1,"ym")&"개월"&DATEDIF(B1,C1,"md")&"일"

  두 날짜간의 일수 차이    =DATEDIF(B1,C1,"d")
  두 날짜간의 월수 차이    =DATEDIF(B1,C1,"m")
  두 날짜간의 년수 차이    =DATEDIF(B1,C1,"y")
  두 날짜간의 일수계산     =DATEDIF(B1,C1,"yd") & "일 경과"
  두 날짜간의 개월수 계산 =DATEDIF(B1,C1,"ym") & "개월 경과"

* datedif()함수 사용시 주의할 점! 날짜1과 날짜2가 있을 경우, 날짜1이 날짜2보다 반드시 작아야 하며 그렇지 않으면 에러가 발생한다.
 

예를들어,

         시트1                               시트2

       A          B                      C             D    

      가                                  나             1

      나                                  가             2

      다                                  다             3

      바                                  아             4

위와 같이 시트1과 시트2가 있습니다.

여기서 제가 원하는 것은 시트 1의 A필드에 있는 '가','나','다','바','아'를 차례대로 시트 2의 C필드와 비교해서 존재하면 C의 값을 필드 B에 가져오고 싶습니다.

하나만 예를 들면,  필드 A의 '가'에 해당하는 값이 C에 존재하고 그 때 D는 2이므로 2를 B에 적는것입니다.

[답글]

INDEX  와 MATCH 를 활용하면 됩니다.

=INDEX(Sheet2!$D$2:$D$6,MATCH(Sheet1!$A2,Sheet2!$C$2:$C$6,0))