[엑셀팁] 많은 셀 채우기

비교적 많은 양의 셀을 동일한 값으로 채우거나 연속된 번호로 채우고 싶은 경우에
아래 방법을 이용하시면 좋습니다.

아래 그림처럼 B3000 을 입력

B3000 셀이 나왔으니 마킹을 위해서 1로 입력해 둠

이제 틀고정 기능을 이용하여 셀을 한꺼번에 채움

아래 그림처럼 셀고정 상태가 되었으면 두가지 방법 중 한가지로 셀을 채울 수 있습니다.

좌우에 셀이 채워져 있으면 위아래 두셀을 선택하고 자동 채우기를 할 수가 있는데 좌우에
아무것도 없는 경우에는 이런 방법을 활용하시면 쉽게 원하는 값을 채울 수 있습니다.

도움이 되셨다면 공감 꾹~~ 부탁드립니다.



[엑셀] 유용한 단축키

엑셀을 사용하다보면 단축키를 알아두면 편리한게 있습니다.
대부분은 마우스로 다 해결 가능하지만 알아두면 좀 더 편리한 걸 제 나름대로 정리해보고 있는 중입니다.

더 도움이 될 단축키는 추가 수정하여 올리겠습니다. 

[엑셀] SUMIFS 함수 쉽게 다뤄보기

이번에는 SUMIFS 함수에 대해서 알아보겠습니다.
SUMIF 함수는 조건식을 하나만 만족시키다보니 불편할 수도 있고 조건 하나만으로도 만족스러운 결과를 찾을 수도 있을 것입니다.
여러개의 조건을 만족하는 경우를 찾고자 한다면 SUMIFS 함수를 이용하면 됩니다.

결론은 함수마법사 이용하면 너무 쉽다 라고 말씀드립니다

SUMIF 함수와 SUMIFS 함수가 다른 점은 sum_range 위치가 달라진 다는 점입니다.
SUMIFS 함수는 다중조건을 만족시켜야 하므로 sum_range를 앞으로 둔 거 같네요.
SUMIF 함수도 그냥 sum_range 를 앞에다 두어도 되는데 말이죠.

아래 그림처럼 구하고자 하는 품목이 독서대, 입고된 날짜가 5월 13일 이라고 가정합시다.
이 2가지 조건을 만족하는 가격의 합을 구하라 라고 한다면??

복잡하게 생각하지 마시고 함수마법사의 힘을 빌어서 해주면 아주 쉽습니다.

아래 그림처럼 보시고 하시면 됩니다.
먼저 sum_range지정, 조건1의 범위지정과 조건1의 조건값 지정, 조건2의 범위지정과 조건2의 조건값 지정

위 그림에서 제가 독서대 라는 글자를 직접 입력하는 대신에 셀을 지정했는데
아래 그림에서는 독서대라는 글자를 직접 입력해서 적어도 된다는 의미로 바꿔서 적은 겁니다.

이번에는 구하고자 하는 조건이 독서대가 아니라 '끝자리가 대로 끝나는 품목을 모두 다 찾아라' 라고 한다면??
어떻게 하시겠습니까?
아래 그림처럼 조건 지정하는 곳에다가 '*대' 라고 입력하기만 하면 됩니다.

이번에는 수량이 15개 이상인 독서대를 찾아서 가격 합계를 구하라 라고 나왔네요

조건식 넣은 필드에다가 아래 그림처럼 '>=15' 라는 조건식을 넣어주면 됩니다.

결과값을 굳이 표시하지 않아도 다 아실 겁니다.
조건을 어떻게 주느냐에 따라 원하는 결과를 다양하게 얻을 수 있습니다.
결과값을 얻은 함수식을 다른 것으로 변경하여 넣어줘도 됩니다.
여기서 영역을 절대값으로 모두 변경한 이유는 구하고자 하는 값이 하나가 아니라 다른 것도 있을 수 있어서 조건범위가 변경되지 않도록 하기 위함입니다.
직접 해보시는게 가장 좋은 방법입니다. 첨부된 파일 받아서 한번 해보세요.

그리고 피벗테이블 만들어서 하는 것은 각자 알아서 한번 해보시기 바랍니다.
피벗테이블을 이용하는 방법도 알고 싶다는 분이 계시다면 작성해서 올려보겠습니다. 

유용한 엑셀 함수

엑셀을 다루다보면 요긴하게 사용할 함수들을 알아둘 필요가 있다.
앞으로 유용하게 사용할 함수들을 공부하면 알기 쉽게 정리하고 추가할 생각이다.

셀에 나온 내용중에서 특정구간을 유용하게 이용하고자 하는 경우에는
concatenate 함수, mid 함수를 사용하여 원하는 내용을 발췌 하면 좋다.

셀의 내용중에 불필요하게 들어간 공백을 제거하고 싶다면
trim(A1) 함수를 이용하면 된다.

선택하여 붙여넣기 - 값 을 편하게 입력하려면....
복사하기 : Ctrl + C
붙여넣기 : Alt + HVV  (값 붙여넣기)

중복검사
내용 : 특정구간에 데이터가 1000개가 있고, 다른 셀에 특정값이 있을 때 다른 셀의 값이 특정구간에 존재하는지 여부 검사
함수 : IF(COUNTIF(조건구간,비교하고자하는 셀),"중복","신규")

자체셀에서 중복 값, 문장 찾아내기
IF(COUNTIF($A$3:$A$8,A4)>1,"중복","")
이건 일단 오름차순 정렬을 먼저하고 하는게 중복값 비교도 되고 용이하다.

VLOOKUP(찾을 값,배열,반환할 값의 열이 몇번째인가, FALSE)
첫번째 열에서 일치하는 값을 찾은 다음, 오른쪽 몇번째 값을 가져와라
다시 말하면, 배열에서 찾을 값과 일치하는 행을 반환한다. (행을 찾는다)
** 배열은 첫번째 열
    배열보다 왼쪽에 있는 값은 반환이 불가능

찾는 값보다 왼쪽에 있는 배열도 찾기를 원한다면 INDEX/MATCH 함수를 사용하라!!!
INDEX(원하는 값 조건범위 문자열, MATCH(찾는값,찾을 조건범위 문자열,0))
조건을 만족하는 하나의 값만 출력 가능

INDEX(array,행,열)
열의 값을 입력하지 않으면 자동으로 1열이 반환된다. 즉 이 경우에는 INDEX(array,행)
행을 찾으려면 MATCH 함수를 이용한다.
MATCH 함수는 지정한 값의 첫번째 위치값을 반환받는 함수
MATCH함수는 지정한 값을 배열에서 찾아 상대 위치를 구해준다.

*형식: MATCH(lookup_value,lookup_array,match_type) - lookup_value: 데이터 테이블에서 찾고자 하는 값입니다. - lookup_array: 찾으려고 하는 값이 포함된 데이터 테이블 범위입니다. - match_type: 찾는 방법을 지정하는 옵션으로 숫자 -1, 0, 1이 있습니다. 1 lookup_value보다 작거나 같은 값 중에서 최대값 반환 0 lookup_value와 같은 첫째 값 반환 -1 lookup_value보다 크거나 같은 값 중 가장 작은 값을 반환

  * match(현재 SHHET의 찾고자 하는 값, 찾고자 하는 값과 동일 값이 들어간 (타 SHEET) 배열, 0) 처럼 입력해야 함
     index 에서 찾는 배열은 macth 함수에서 사용하는 배열보다 더 넓은 범위의 배열
Vlookup 과 Match 함수를 사용하여 다른 곳의 값을 참조하는 경우 꼭 알아둬야 할 것은 
해당 함수가 대/소문자를 구별하지못하기 때문에.. 
대/소문자를 구별해야 할 경우 이 두 함수를 사용하지 말아야 한다는 것

만약 N/A 라는 것이 나오는 것을 나오지 않게 하고 싶다면
IFERROR(INDEX($A$2:$B$41,MATCH(G3,$B$2:$B$41,0),1),"") 와 같이 IFERROR 함수를 사용하라 

FIND 함수
FIND(찾고자 하는 값, 찾는 값이 들어간 셀,1) = 찾는 값이 들어간 셀의 시작점 위치를 반환
원하는 게 있는 것지 여부라면 =COUNT(FIND({"지우개","연필","볼펜"},B3))
피벗 작업을 위한 거라면 IF(COUNT(FIND({"지우개","연필","볼펜"},B3))>0,1,0)
 * 찾는 값이 하나라도 들어가면 1로 표기하고, 안들어가 있으면 0으로 표기하라
만약 값을 기록하도록 하고 싶다면 MID함수랑 같이 사용한다.
LEFT(찾는 값이 들어간 셀, 시작점에서의 숫자길이) 
   LEFT(A1, VALUE(FIND("-",A1,1)-1)) : 구분자 "-"를 기준으로 왼쪽의 값을 표시하라 
MID(찾는 값이 들어간 셀,시작점,시작점부터의 숫자길이) 
   MID(찾는 값이 들어간 셀, FIND(찾고자하는 값, 찾는 값이 들어간 셀,1), 숫자길이)
RIGHT(찾는 값이 들어간 셀, 오른쪽에서부터의 숫자길이) 
   RIGHT(A1,LEN(A1)-FIND("-",A1,1)

SUBSTITUTE(찾는 값이 들어간 셀, old text, new text, instance num)
  * instance num : 몇번째 old text 를 new text 로 바꿀 것인지 지정하는 숫자, 생략도 가능 
예제 : =MID(A2,FIND("ㅜ",SUBSTITUTE(A2," ","ㅜ",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),1),100)
  의미 해석 : 공백으로 떨어져 있는 값중에서 마지막 공백의 오른쪽 값을 반환하라
  FIND 함수를 통해 시작점 위치를 찾는다. 시작점으로부터 길이는 잘 모르지만 최대한 길게 잡아 100으로 설정했다.
  찾고자 하는 값이 공백이 몇번째 공백인지 여부를 찾기 위해서
  특정 값으로 대체하고 LEN 함수를 이용하여 intance num 값의 위치를 찾는다. 

특정기호로 된 것을 구분하는 것은
 데이터 -> 텍스트나누기 함수를 이용하면 편하다
 가령 구분자가 > 로 된 경우가 여러개 존재하는 경우 나누고자 하는 수만큼 열을 생성한 다음에
 이 함수를 사용하면 쉽게 나누어진다.

자리수를 4자리로 표현하면서 0001, 0002 등로 나오도록 하려면....
text(셀값,"0000") 로 지정하면 된다.

다중조건으로 값이 일치하는 것을 찾고자 할 경우에는 해결이 쉽지 않으면 두개의 셀을 하나의 셀처럼 인식시켜 비교하는 것도 방법이다.
C2&D2

길이가 일정하지 않는 것은 오름차순 정렬을 하면 제대로 정렬이 안된다.
A1
A10
A100
뭐 이런식으로 정렬이 되니 불편하다.
이건 텍스트 분리하기를 해서 하나로 다시 합치는 방법을 사용해보자
RIGHT(B2,LEN(B2)-1)  : 왼쪽 1개를 제외하고 나머지를 모두 기입하라
이것을 가지고 TEXT 셀값 맞추기를 하면 된다. TEXT(RIGHT(B2,LEN(B2)-1,"00000")

특정 셀이 숫자인지, 문자인지 여부 판별하는 식으로 자막 공백제거
=IF(ISNUMBER(I15)=TRUE,"A",IF(ISTEXT(I15)=TRUE,"B","C"))

색기준 정렬
 정렬필터에서 색기준으로 정렬하면 된다.
 특정단어가 들어간 필드만 색깔을 넣고자 할 경우에는 조건부서식을 활용하면 된다.

공백 없애기
  홍길동, 홍 길동 등으로 입력값이 서로 다를 때 비교하기가 모호해질 때 공백을 없애는 함수는
  =substitute(a1, " ", "") 이렇게 쓰면 문자열내의 모든 공백이 제거됨
  문자열 앞뒤 공백 제거는 =trim(a1) 함수를 사용

 셀에 보면 숫자인데 텍스트로 되어 있어서 실제로는 숫자로 인식 안되는 경우가 있다.
이럴 경우 숫자로 인식시키는 방법은
=VALUE(C1) 처럼 텍스트를 숫자로 인식시키는 함수를 사용한 다음에 다른 셀에 값만 붙여넣기를 하면 된다. 

[엑셀] 한글 영문 혼용 셀에서 한글, 영문 추출

출처 : 엑셀 하루에 하나씩 카페


1.해당 엑셀 시트에서 Alt+F11을 누르거나, 도구-매크로-'Visual Basic Editor"를 실행합니다.
2. VBA편집기가 나오면, 메뉴바에서 "삽입-모듈"을 실행합니다.
3. 하얀 백지화면이 나오면 아래 코드를 그대로 복사해다가 붙여넣습니다.
4. Alt+F11을 눌러 다시 원래의 워크시트로 돌아오십니다.
5. 일반 워크시트 함수와 똑같이 사용하시면 됩니다.

** 단점은 띄어쓰기가 된 걸 인식하지 못한다는 것이다.
    그래서 편법으로 " " 공백문자를 인식하도록 하는" "를 추가했다.
    두개의 조건문에 모두 넣으니 인식이 안되길래 한번씩 사용하는 걸로 하고 두번에 걸쳐서 자료를 추출했더니
    원하는 결과값이 얻어졌다. 심봤다!!!!!!!!!!

아래 함수를 직접 만들어주신 분께 정말 감사드립니다

Function CutText(sText As String, Optional LanguageType As Integer = 1) As Variant

' ----------------------------------------------------------------------------------------

' 설명 : 인수로 전달한 sText 에서 LanguageType 의 값에 따라 지정한

'        형식의 텍스트만 분리해서 전달합니다.

'         LanguageType 의 사용값

'         1 : 숫자

'         2 : 영어 : 띠어쓰기 인식하도록 " " 추가하고, ' 인식하도록 추가

'         3 : 한글

'         4 : 한자

' 작성일 : 2005 / 9 / 20

' ----------------------------------------------------------------------------------------

    Dim sCut As String

    Dim sTMP As String

    Dim i As Integer

   

    Application.Volatile

    If LanguageType > 4 Then

        CutText = CVErr(xlErrNA)    '#N/A 오류를 반환

        Exit Function

    End If

   

    For i = 1 To Len(sText)

   

        sCut = Mid(sText, i, 1)

   

        Select Case sCut

            Case 0 To 9

                If LanguageType = 1 Then sTMP = sTMP & sCut

            Case "a" To "z", "A" To "Z", " ", "'"

                If LanguageType = 2 Then sTMP = sTMP & sCut

            Case "가" To "힣", "ㄱ" To "ㅎ", "ㅏ" To "ㅣ"

                If LanguageType = 3 Then sTMP = sTMP & sCut

            Case Else

                If LanguageType = 4 Then

                    If Asc(sCut) >= -13663 And Asc(sCut) < 0 Then sTMP = sTMP & sCut

                End If

        End Select

   

    Next

   

    CutText = sTMP

   

End Function

 

엑셀창 2개 띄우는 방법

엑셀을 작업하다보면 창을 두개 뛰우고 작업을 해야 편리한 경우가 많다.
요즈음 엑셀 가지고 작업을 밥먹듯이 해야 할 상황이라서 찾아봤고
이해하기 쉽게 정리를 하고자 한다.

아래 그림에서 /e 로 된 것을 /en "%1" 으로 수정하고 DDE 체크된 것을 uncheck 하면 된다.

*.xls 와 *.xlsx 두개 모두 이렇게 하면 된다. 

[엑셀] FIND 함수 응용 활용으로 업무를 편하게

FIND 함수를 활용하는 법을 설명을 했는데 업무에 편리하게 활용하기에는 좀 부족한 면이 있는 거 같아서 좀 더 정리해서 올립니다.

그냥 간단하게 값을 입력하여 찾는 방법은 다 아는 것이니 굳이 설명드리지 않겠습니다.


오늘 설명드릴 사항은 배열을 이용하여 찾기를 해보겠습니다.
무슨 말이냐면

관광명소 중에서 '조선', '고려'와 연관된 말이 들어간 셀을 찾아서 표시하라
라는 내용이 있다고 칩시다.
그런데 다음에는 '신라', '백제' 라는 말이 들어간 셀을 찾아서 표시하라
라고 한다면 어떻게 할까요?

이런 식으로 찾아야 할 값을 나열식으로 적어주는 방법도 있지만
이 경우에는 값을 추가하거나 변경해야 할 경우에는 엄청 불편합니다.

이걸 간단하게 해결하는 방법은
배열식을 이용하는 방법입니다.

배열서식으로 만들어서 작업을 하면 배열값을 늘리거나 줄이면서 작업하면 편리하게 작업이 가능하므로 매우 편리합니다.
배열 참조식은 별도로 다른 sheet 에다가 놓고 작업을 하는 편이 보기도 좋고 여러모로 좋습니다.

FIND 함수, IF 함수, VLOOKUP함수, 피벗테이블을 적절하게 잘 활용하면 원하는 작업을 편하게, 빠르게 하실 수 있답니다.

자료 정리 하는 것중에 어려운 건 샘플 만들어서 하는 거네요 ㅠㅠㅠ
허접한 샘플이지만 파일 첨부했습니다.
 

중복값 찾기

중복값을 찾는 걸 어떻게 찾아낼까요?
가장 손쉬운 방법이 조건부 서식을 활용한 방법입니다.
조건부 서식을 활용하면 여러모로 편리합니다.
어제 중복자료를 체크해야 할 사항이 있어서 조건부서식을 활용하여 요긴하게 써먹었습니다.

품목에서 중복된 값을 찾아보는 방법을 그림을 죽 설명했으니 보시면 이해가 금방되실 겁니다.


중복체크할 영역을 블럭설정을 합니다.


이제 조건부서식을 이용하여 중복값을 선택합니다.




중복을 설정하고 나면 아래처럼 표시가 됩니다.


같은 것끼리 정렬을 이용하여 정리를 해보면...

이렇게 나옵니다.

중복값 제거를 바로 하면 옆에 있는 셀과의 관련성이 잘못된 것이 있을 수도 있는 걸 모를 수가 있으니 중복값 검사를 한 다음에
중복값 제거를 하는게 좋습니다.

엑셀 공백제거 및 두칸띄기를 한칸띄기로 변경

엑셀을 하다보면 원하지 않게 공백이 있어서 정렬을 해서 비교를 해도 중복자료가 있는지 없는지 파악하기 힘들때가 있습니다.




이럴 때 유용하게 사용하는 함수가 공백제거 함수인 trim 함수 입니다.
trim 함수는 문장의 앞뒤의 공백만 제거합니다. 즉 문장 사이의 띄어쓰기는 처리 하지 않습니다.
B열 8행 보시면 앞의 공백이 제거된 거 보이시죠?


문장을 입력하다보면 한칸띄기를 해야 하는데 두칸띄기가 되어 있는 경우가 생기기도 하는데요
이럴 때는 아래처럼 Ctrl + H를 눌러서 스페이스바를 두번 눌러주고, 변경할 내용에는 스페이스바를 한번만 하고 나서
찾기를 한 다음 변경하기를 해주시면 됩니다.


아주 간단한 내용인데 경우에 따라서는 잘 생각나지 않아서
필요할 때 도움이 되실수도 있어서 적어봤습니다.

[간단팁] 엑셀 한영 자동전환시 대처법

엑셀을 사용하다보면 영문으로 넣었는데 자동으로 특정글자가 한글로 변경되어 할 때마다 짜증스러울 때가 있죠..
그렇다고 한영변환을 아예 막아버리자니 그렇고~~
이런경우에는 간단하게 설정해서 사용하시면 됩니다.

이런 경우에는 아래 순서대로 따라서 하시면 됩니다.

설명한 그림은 엑셀 2010 이지만 2007 등 다른 것도 찾으시면 메뉴는 거의 비슷한 곳에서 찾으실 수 있을 겁니다.

자동고침 옵션에서 한/영 자동고침을 완전히 해제를 해버리면 잘못하여 한글로 입력하고 있다고 생각하고 입력중일때 영문입력을 하고 있을때 완전히 지우고 새로 입력을 해야 하겠죠..

그래서 자동으로 변환되는 단어가 나오면 그 단어만 추가를 해주는 것이 좋습니다.

자동변환이 되지 않도록 입력값과 결과값을 동일하게 넣어줍니다.

확인을 다 하셨으면 이제 확인을 눌러주세요..

아래는 수식 자동고침을 할 값을 하나 추가를 해봤습니다.

MS워드에서는 --> 를 입력하면 자동으로 화살표 방향키로 바뀝니다.

동일(유사)하게 하려고 값을 넣어본 것입니다.

엑셀을 편하게 사용하기 위해서 좀 더 편리하고 간단한 팁을 추가해봤습니다.

[엑셀] 조건부 서식을 활용한 값 치환

위 그림처럼 테이블(자료)에서 원하는 걸 찾아서 값을 변경하고 싶다면 어떻게 해야할까요?

방법은 여러가지가 있을 수 있습니다.

오늘은 조건부 서식을 이용해서 한번 해보겠습니다.

블럭설정은 첫번째 지정하고 싶은 셀에서 Shift 누른상태에서 마우스로 마지막 셀을 선택해서 누르면 됩니다.

그 상태에서 Ctrl + D를 눌러주면 값이 변경되는 걸 확인하실 수 있습니다.




조건부 서식 텍스트포함은 어떤 특정한 글자가 들어간 것만 찾아서 색깔별 정렬을 해서 원하는 결과를 찾을 때도 매우 유용합니다.




다른 방법으로 해본 다면

값 대체하는 substitute 함수를 이용하는 방법입니다.

이렇게 한 다음에 값붙여넣기를 하고 나서 원래 수량 열은 지우고 값대체 열에 '수량'으로 변경만 해도 됩니다.

방법은 다양하게 해볼 수 있겠지요..




자주 사용하는 IF함수 사용법이랑 다른 함수들을 적절히 조합하면 됩니다.




직접 해보실 분을 위해 첨부파일 첨부합니다.