빅데이터 관련 프로그래밍/└ 엑셀로 데이터 다루기

날짜 데이터는 숫자로 되어있어서 더하거나 뺄 수있다.
default로는 숫자처럼, 셀 오른쪽에 붙는다.
그러나 다른문서에서 가져온 날자데이터가 문자열처럼 왼쪽정렬되어, 뺄셈이 안된다면?

  1. 날자데이터의 앞에 '를 입력하여 문자열-날자데이터를 임의로 만들었다.
    • 엑셀에서는 2017대신 17만 입력하여도 2017 자동인식


  1. 왼쪽 정렬된 문자열-날짜데이터는 더블클릭후 엔터만 치더라도 날짜로 바뀌어 오른쪽정렬이 된다.(엑셀이 자동인식)

    • 그러나 일일히 그렇게 할 순 없다.
    • 여기서는 강제로 '입력해준 상태기 때문에, 안바뀐다.
  2. =DATEVALUE()를 이용하여, 문자열-날자데이터를 한번에 변환시켜보자.

    • 정상변환 되었다면, 우측정렬된 날짜데이터가 생김
      (1) 변환시킬 필드를 세로 만든다.

      (2) 첫 셀만 선택하여, =DATEVALUE(입력후, 왼쪽데이터를 클릭 후 입력해준다.(만약 쌩둥맞은 숫자로 바뀌면, 셀서식>날짜)


      (3) 핸들바를 더블클릭하여, 나머지행들에 전체적용해주기

  3. 날짜뺄셈을 할때, 변환한 =DATEVALUE(A2)같은 서식으로는 엉뚱한 값이 나올 수 있다.

    이럴때는 복사-붙혀넣기 > 값 및 원본서식을 선택한다.

    • 수식만 제거하려면, 만 선택하면 됬지만, 날짜형식까지 유지하려면 값 및 원본서식을 선택해야한다.


      이후의 날짜 뺄셈은 잘된다.

아래와 같이 [합 행]에 SUM(B2:B3) 같이 +가 아닌 :로 계산된 수식이 적혀있다.

  1. 밑에 행을 선택한 상태에서 우클릭 > 행삽입이나 Ctrl + (+)를 통해서 행을 삽입하면, 삽입된 행도 합계 수식안에 적용된다.
    • 만약 SUM(B2+B3)의 형태로 :이 아니면, 안된다.



그러나 문제점이 발생한다.
[평균]열(C열)은 앞의 [수익]열(B열)을 이용해서 = B2/2 와 같이 만들어졌는데, 일반적인 행삽입으로는 해당 수식이 사라진다.

  • 즉, 삽입된 행은, 전체 수식에는 포함되지만, 행 자체의 수식은 삽입되지 않는 문제이다. 이럴 때는, 직접 수식을 만들어서 적는 수밖에 없다고 한다.

2. 수식을 가진 행 자체를 복사해서 행 삽입하기






  • 이 방법의 문제점은, 복사한 행의 값까지 복사가 되고 값을 바꾸면 되지만, 행의 값을 삭제하는 경우 함수식까지 삭제되어버린다.
  1. 가장 좋은 방법은 합산 행을 제외 표를 먼저 만들고, 행 삽입하기이다. 즉, 복사한 행 삽입을 하지 않아도, 단순 행삽입으로 행자체의 상대참조 수식까지 다 복사가 된다.

    • 합산행 제외한 셀을 선택한 뒤 삽입 > 표를 선택해준다.



    • 표를 만든상태에서는 단순 행삽입을 해도 자동으로 수식이 적용된다!

    • 여러 행을 삽입하고 싶다면, 그 밑으로 그 행수만큼 선택한 뒤 삽입해주면 된다.


    • 표 자체의 기본 디자인이 싫다면, 빈 디자인을 선택해주면 된다.

병합된 셀은 정렬이나 피벗테이블등의 기능을 활용할 수 없다.(보기만 이쁜?)

Database는 병합자체가 없기 때문에, 각 셀당 하나의 정보가 다 들어가야한다.

또한 PC방이라는 반복되는 명사를 고유명칭 + 한칸 띄고 + PC방 형태로 바꿔보자.

정원이라는 필드명에 xx명에서 도 제거해보자.

  1. 병합된 셀을 DB처럼 바꾸기

    • 병합된 셀을 전체선택한 뒤, 병합을 풀어보자.

    • 빠른실행메뉴의 2번째로 지정해준, 셀 병합 단축키를 선택하여 풀어준다.

    • 빈 셀만 선택하기 위해서, 병합을 푼 셀들을 전체 선택한상태에서, 홈>편집> 찾기 및 선택> *이동옵션* > *빈 셀*을 선택한다.
      my) [ Ctrl + G ] : 이동 -> [ 옵션 ] -> [빈 셀]



    • C5가 선택된 상태이다. 여기에 =를 입력한 뒤, 바로 위에 값이 들어갈 수 있게(상대참조) C4(윗셀 선택)을 해준다. 그리고나서 선택된 모든 빈셀에서 다 적용시키기 위해 Ctrl+Enter를 입력해준다.

    • 상대참조 값들이 잘 들어가 있다. C5는 =C4가, B5는 =B4가 (바로 윗셀의 값)이 잘 들어가 있는 것 같다. 하지만, 다른 필드를 기준으로 정렬시 참조값이 깨질 수 있기 때문에, 상대참조로 채워넣은 값은 전체 복사를 한 뒤, 붙혀넣기 > 값만(V) 선택해서 절대값으로 변환시켜주어야한다.





  1. 특정단어를 한칸 뛰고 특정단어형태로 바꾸기 or xx명에서 명 빼기

    • 제한된 범위에서 찾아바꾸기 이므로, 먼저 블록설정부터 한다.

    • PC방이라는 단어를 -> 한칸+PC방으로 바꿔보자.

    • 정원필드에서 -명 빼주기


각종 주의점들

  1. 필드는 오로지 1줄에 다 입력시키고, 많아지면 데이터 > 그룹을 활용하자
    • 칼럼1, 칼럼2, ..., 칼럼n, 빈 칼럼에서, 빈칼럼 앞까지 그룹을 해주면 빈 칼럼이 그룹명이 된다.





  1. 필드명은 영어로 하자. 통계 프로그램에서 인식 못할 수 도 있다.
    • 대쉬(-)보다는 언더바(_)가 좋다
  1. 범주 2개형의 경우, 성별이나 기저질환 유무 등 하나를 명시하고 다른하나는 0으로 나타내자. 그리고 데이터 유효성 검사를 활용하여, 각 셀에 입력될 값을 제한시키자.

    • 나이의 경우, 정수여야하며, 연령대 제한은 범위로 할 수 있다.







  • 성별(MALE)의 경우, 목록의 형태로 0,1만 오도록 제한할 수 있으며, 반드시 콤마로 나누어주어야한다. 그러면 드랍다운이 생기면서 선택하거나, 직접 0or1을 입력할 수 있다.




+ Recent posts