MayBeMayBe

MayBeMayBe

Posts Tagged ‘Excel

tips – How to use the INDEX and MATCH worksheet functions with multiple criteria in Excel…,,

leave a comment »

 

..

MS – How to use the INDEX and MATCH worksheet functions with multiple criteria in Excel

https://support.microsoft.com/en-us/kb/214142

..

StackOverflow – Lookup using INDEX and MATCH with two criteria

http://stackoverflow.com/questions/18767439/lookup-using-index-and-match-with-two-criteria

..

How to VLOOKUP with Multiple Criteria Using INDEX and MATCH

http://www.exceltactics.com/vlookup-multiple-criteria-using-index-match/

..

**

Superuser – When to use Ctrl+Shift+Enter and when to use Enter in Excel?

http://superuser.com/questions/674566/when-to-use-ctrlshiftenter-and-when-to-use-enter-in-excel

..

..

Select multiple criteria in MS EXCEL

..

MS Excel Sheet에 위와 같은 표가 있다고 해 보자.

단순한 Vlookup 함수와는 다르게, Index match 함수를 사용해서 값을 골라야 할 때가 있다.

위에서 보인 예제를 본다면, A열, B열, C열, D열, E열 조건을 만족하는 값을 F열에서 고를 때에는 단순하게 Vlookup 함수를 사용할 수는 없고 Index, Match 함수를 사용해야 하는데 여기에는 약간 문제가 있다.

Index, Match 조합을 할려면 조건을 하나 밖에 사용할 수 없는데, 위에 든 링크 예제를 보면 여러 조건일 때도 응용해서 사용할 수가 있다.

..

 

=INDEX(Data!$A$1:$F$1000000,MATCH(1,(Data!$A:$A=$A3)*(Data!$B:$B=C$1)*(Data!$C:$C=$D$304)*(Data!$E:$E=$D$305),0),6)

 

참고로, 위와 같은 식을 사용해서는 꼭 Ctrl + Shift + Enter키늘 눌러서 식을 마무리 해야 한다. 그냥 Enter 키를 누르고 식을 마무리 하면 작동을 하지 않는다.

..

..

여러조건을 만족하는 Cell을 고를 때, 처음에는 SumIFs() 함수를 사용하였었다. Sumifs() 함수를 보면 여러가지 조건들을 만족하는 Column Cells 값에 대해 Sum을 하라는 것인데 이 함수를 사용하면 조금문제가 있다.

..

..

..

여하튼, 약 500,000개에 약간 미치지 못하는 F열에 대해서 작업을 하니 컴퓨터가 멈춘 듯이 돌아간다.

..

MS – SUMIFS function

https://support.office.com/en-us/article/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b

..

Written by MayBeMayBe

March 6, 2016 at 01:57

Posted in Programming

Tagged with

tips : Spreadsheet, moving sheet or create clone sheet…,,

leave a comment »

..

..

엑셀 파일에서 작업을 할 때, 다른 엑셀 파일에 있는 Sheet 내용들과 함께 작업해야 할 경우가 있다. 가능하다.

..

복사해서 붙여놓기를 하는 것도 한가지 방법이다.

..

..

다른 방법도 생각해 보자.

..

Ex 001

..

위와 같은 엑셀 파일이 있다고 가정해 보자. 내용은 아무것이나 상관없다.

엑셀 Sheet 1번에서 마우스 오른 쪽을 누르면..

새로운 팝업 창이 뜨는데, 그 중에서 “이동/복사”를 살펴보면…

아래와 비슷한 상태로 바뀐다.

..

Ex 002

바로 위 그림에서 1번은 현재 열려 있는 엑셀 파일들 목록을 선택하기 위함이고, 2번은 선택한 엑셀 파일 안에 있는 Sheet를..

마지막 3번은, 다른 엑셀 파일이 아니라 현재 자신이 작업하고 있는 엑셀 파일에서 사본을 만들겠다는 것이다.

..

보통 나는 3번과 같은 복사본 만들기 선택사항은, 그림을 포함한 기나긴 시계열 값들이 있는 Working Sheet에 대하여 YoY, ttm 값들을 편하게 구할 때 자주 쓰고 있다.

말인즉슨, 현재 다달이 발표하는 값들을 가진 Sheet에서 값들과 그림을 한번만 그리고 나면 그것에 해당하는 ttm 값은 쉽게 구할 수 있다.

다달이 있는 값과 그에 맞는 그림들을 모두 “복사본”을 만든다음에 새로이 생긴 Sheet에서 값들만 ttm에 맞게 조절하면 다달이 있는 값에 해당하는 ttm 값을 바로 구할 수 있다. YoY도 마찬가지다.

..

..

현재 MS Excel에서는 사본이나 Sheet를 이동 복사를 할 때 Ctrl 키를 누른 상태에서 여러 Sheet를 선택하면 많은 Sheet에 대해서 바로 Sheet 이동/복사 및 사본만들기를 할 수 있다.

..

..

..

여기서 구글 SpreadSheet도 같은 원리로 작동한다. 그러나 현재 Google Spread Sheet는 Multi Sheet를 선택할 수 없고 단 하나의 Sheet에 대해서만 Sheet 이동/복사를 할 수 있다. 시간이 지나면 언젠가는 여러 Sheet에 대해서도 작업할 수 있게 될 것이다.

..

..

YoY, ttm 값들에 대해 작업을 편하게 하는 면도 있지만, 여러 문서 파일에 훝어져 있는 값들을 하나로 합치는 기능도 물론 괜찮다.

..

..

어쩌면 많이들 알고 있고 이미 많이들 사용하고 있을지도 모를..

..

..

Written by MayBeMayBe

September 2, 2015 at 00:57

Posted in Writing

Tagged with

[TIPS] MS Excel – Find and Replace special key code…,,

leave a comment »

..

MS Excel 특정 Cell 하나에 값들을 입력할 때, 강제로 행을 내리는 방법이 있다. 익히 알려진대로 Alt + Enter 키를 누르면 된다.

무슨 말인지 모르면 직접 특정 Cell에서 실행 해 보면 알테고..

..

..

여기서, 이렇게 입력한 특수 키들(누군가는 WhiteSpace라고 부르기도 하지만…)을 검색하고 특정 값을 바꾸년 경우가 있다.

이럴 때 많이 쓰는데 단축키로 Ctrl + H, Ctrl + F일텐데 이것은 찾기 및 바꾸기 기능이라고 하자.

..

Ctrl + H로 이렇게 눈에 보이지 않는 특수키들을 검색 및 바꾸고자 할 때는..

Ctrl + H를 누르면 뜨는 찾기 및 바꾸기 대화상자 창에서….

Alt 키를 누르고 특수키에 해당하는 ASCII(혹은 Unicode) 키 코드 값을 입력하면 된다.

Alt 키를 누른 상태에서 ASCII key code 값을 입력하면 입력하는 키 값이 대화상자 입력창에서 보이지는 않는다. 그렇나 눈에 보이지는 않지만 입력은 된다.

..

가령, 제일 위에서 설명한 예제를 다시 끄집어 낸다면 ASCII 코드 값은 “10”번이고 뜻은 LF(Line Feed)이다. 이러한 강제 행 바꿈 값들을 검색하고자 할 때는 Alt + 10 번을 입력하면 되는데…

..

무슨 말인지 헷갈리는 사람들 많겠지만.

이것은 내 기록 목적이니….

..

..

..

아래는 아스키 코드 테이블.

http://www.theasciicode.com.ar/ascii-control-characters/line-feed-ascii-code-10.html

..

..

가끔은 문서에 있는 특수 키 값들을 모르는 경우가 있는데, 입력한 값들을 Unicode 값으로 변환해 주는 사이트.

https://www.branah.com/unicode-converter

..

..

아스키 키 코드 값이나 Unicode 변환 사이트는 위에 말고도 많고도 많지만..

..

==============================================================================

이렇게 하는 이유는 문서 전체의 일관성을 위해서 특수 WhiteSpace 값들을 일괄적으로 그리고 편하게 바꾸기 위해서이다.

..

..

참고로, Alt + Ascii 코드 값으로 특수 키를 검색하는 방법은 오래 전에 StackOverflow에서 검색했던 것인데 출처는 따로 표시할 수가 없다.

..

..

..

지난 주에 했던 일들을 다시 해야하는데, 사이트를 잊어 버려서 다시 찾느라 고생 좀 하였다. 확실하게 기록을 남겨 두는게 좋겠다.

..

..

..

가끔, 인터넷 웹페이지에 올라온 테이블에서 분명 공백 문자인데 찾기 및 바꾸기로 ” ” ==> “” 처리를 하면 원하는데로 되지 않는 경우가 많다.

이유야 여러가지이지만 이럴 때도 대부분 WhiteSpace 값들 때문인데 Ascii 코드0X20 값 대신 다른 값을 공백문자로 써서 그런 것이다.

가끔은 Ascii code 0XFF, 십진수 값으로는 255를 써서 그런 경우가 많다.

..

Unicode로 확장한 문서에서는 이것 말고도 더 많다.

..

..

일관성 없이 여러사람들이 눈에 보이는데로 아무 생각없이 써서 그렇다.

..

Written by MayBeMayBe

September 1, 2015 at 16:52

Posted in Writing

Tagged with

[Tips] MS Excel – Option changed…,,,

leave a comment »

..

..

MS Excel을 사용할 때, 문서를 열 때마다 반복하는 일이 있었다.

폰트 크기 조절.

기본 값은 보통 11정도인데 나는 대부분 크기 9로 조절해서 쓰고 있다. 대부분의 문서 모두와 대부분의 모든 Sheet…에서..

..

..

..

MS Excel을 실행하고 File이라는 메뉴 단추를 누르면 아래와 비슷한 그림이 뜬다. 버전마다 약간 차이가 있을 수는 있다.

..

MS Excel Ex 001

위에서 “새 통합 문서 만들기”에 보면 글꼴 크기와 새로운 문서 만들 때 기본으로 제공하는 Sheet 수를 정할 수 있다.

..

..

..

..

..

MS Excel Ex 003

..

무릇, 컴퓨터의 꽃 중의 하나는 Ctrl + C와 Ctrl + V, 그리고 거기에 거기에 덧붙인다면 Ctrl + Z, Ctrl + Y가 될 것이다.

위에서는 특정 Cell 영역을 복사한 다음에 다른 Cell에 붙여놓기를 한 것인데, 붙여놓기를 하면 붙여놓기 옵션 창이 자그마하게 뜬다.

대수롭지 않을 수도 있는데, 나는 저게 상당히 불편하고 다른 작업을 계속 진행하는데 걸림돌이 되는 경우가 많다.

시계열 값들을 많은 Column 값들에 대해 연속으로 잡업할 때면 저 옵션 창이 마우스 커서 클릭을 방해하는 경우가 조금은 되는 것 같다.

사실, 나는 저 옵션이 없어도 알아서 붙여놓기 할 때 부터 붙여 놓기 옵션을 사용하고 있으니 번거로울 뿐이다.

..

MS Excel Ex 002

옵션창에 있는 “고급” 칸에 보면 “콘텐츠를 붙여넣을 때 붙여넣기 옵션 닺추 표시(S)라는 선택 단추가 있다. 거기서 조절하면 된다.

..

..

..

나머지 선택사항들을 살펴보지 않았음.

..

==========================================================

그런데, “Advance(d)”라고 표시된 것을 한글로 번역할 때 “고급”이라고 하는 것 같다. 그냥 생각으로는 깔끔하지 못한 번역 같다.

고급 (선택)옵션, 저급 선택(옵션)이라는 뜻이 물씬 풍겨 나온다.

“더 나아간 선택사항 들”이 맞을 텐데……

..

..

..

..

Written by MayBeMayBe

August 30, 2015 at 10:40

Posted in Writing

Tagged with

MS Excel – AverageIf, SumIf, whatever…,,

leave a comment »

..

MS Excel – SumIf 함수

https://support.office.com/en-ca/article/SUMIF-function-169b8c99-c05c-4483-a712-1697a653039b

..

..

..

..

다음과 같은 MS Excel 표가 있다고 가정해 보자.

Data
2013-01-03 1
2013-01-07 3
2013-01-18 2
2013-01-23 4
2013-02-04 7
2013-02-28 8
2013-03-09 9
2013-04-11 12
2013-04-22 13
2013-04-25 14
2013-05-02 15

..

위에 있는 표 처럼 연-원-일로 되어 있는 각각의 Cell 값을 연-월처럼 각 달마다 값으로 하나로 합치고 싶다.

..

Sum Data
2013/01/01 10
2013/02/01 15
2013/03/01 9
2013/04/01 39
2013/05/01 15

바로 위에 있는 표는 제일 위에 있는 각 날짜 마다 있는 값을 각 월별로 합친 값이다. 가령 2013년 1월(1일)에 있는 값 10은 2013년 01월 3일, 7일, 18일, 23일에 있는 각 값 1, 3, 2, 4를 더하면 나오는 값이다.

..

위에서 예를 든 설명은 사실 복잡하게 MS Excel에 있는 함수 값을 복잡하게 사용하지 않아도 바로 계산은 할 수 있지만, 위에 있는 것은 설명할려고 간단하게 만든 것이고 상당히 많은 날짜 별로 있는 값을 정리해야 할 때가 있는 법이다.

..

..

MS Excel Sumif

바로 위에 다시 예를 들었는데, 위에서 2번 부분은 A열에 있는 각 날짜 값에서 년도와 월 값만 취하고 날짜는 강제로 첫 시작일로 맞춘 것이다.

MS Excel 식은 다음과 같다.

=DATE(YEAR(A2), MONTH(A2), 1)

..

위와 같이 D2 Cell에서 하면 빨간색으로 칠해진 2번과 같은 값이 나온다. 이렇게 하는 이유는 MS Excel에 있는 SumIf 함수를 사용하기 위해서이다.

..

위에 있는 그림에서 빨강 3번이 있는 테이블이 최종적으로 작업할 곳인데, 보면 F열은 월별로 정리되어 있다. 반면에 A열은 날짜별로 정리되어 있고.

..

빨강 2번 월별 값을 미리 구했으니, 이것을 이용하여 빨강 3번에 있는 G열 값을 채울 수 있다.

Cell G2 값은 아래와 같이 수식을 사용하면 된다.

=SUMIF(D:D, F2,B:B )

..

위에 있는 함수에서 D:D는 D열 전체를 가르키는 값이다. D열은 빨강 2번이 있는 곳으로 A열에 있는 날짜별로 있는 값을 월 값으로 강제로 변환한 값이다. 이 변환한 월 값을 가지고 F2와 같은 값이면 B열에 있는 같은 연/월값 전체를 더하라는 뜻이 된다.

그러면 2013년 01월 03일, 07일, 18일, 23일 값이 2013년 01월에 해당하는 모든 값이므로 그에 해당하는 값들 1 + 3 + 2 + 4 = 10이라는 값이 G2 Cell에 입력이 된다. G3 Cell도 마찬가지…

..

AverageIf 함수도 아마 마찬가지일 것이다.

..

Written by MayBeMayBe

July 13, 2015 at 23:48

Posted in Writing

Tagged with

MS Excel – Date function…,,

leave a comment »

..

..

Ex 003

..

MS Excel에서 날짜에 대해 무엇인가를 할 때, 월말에 해당하는 끝 날짜 때문에 불편했던 적이 있다.

특히 매해 2월에 해당하는 끝날이 28일인가, 29일인가를 가늠해야 할 때가 많은데, 이럴 때는 다음과 같이 해도 된다.

..

아래 식은 MS Excel Cell E2에 대한 식이다.

=DATE(YEAR(A2), MONTH(A2)+1, DAY(A2)-1)

..

위에 있는 그림에서 1번 부분은 입력할 때 연월(Year, Month) 부분만 입력하면 MS Excel 내부에서 연월일에 해당하는 “일” 부분은 시작일 “01”을 인식하게 되어 있다.

그림에서 2번 부분…

..

그런데 이런 “일(day)” 부분을 각 해(Year)와 월(Month)에 해당하는 마지막 날(day)로 바꾸고 싶다면, 위와 같은 식을 사용하면 된다. 다른 열에다가 입력을 하고, 변환된 값을 복사한 후 “값”으로 붙여놓기..

1992년 2월은 마지막 날이 29일이고 1993년 2월은 마지막 날이 28일인가 보다.

..

사용한 식은 MS Excel에서 기본으로 제공하는 Date() 함수이다.

Year(), Month(), Day() 함수는 말 그대로 임.

참고로,

=DATE(YEAR(A2), MONTH(A2)+1, 0)

..

과 같이 입력해도 된다.

..

Written by MayBeMayBe

January 15, 2015 at 22:32

Posted in Programming

Tagged with

MS Excel – Index, match functions…,,

leave a comment »

..

MS – Index functin

http://office.microsoft.com/en-001/excel-help/index-function-HP010069831.aspx

..

MS – Match Function

http://office.microsoft.com/en-001/excel-help/match-function-HP010062414.aspx

..

Ex 001

..

위와 같은 표(Table?)가 있다고 해 보자.

표를 보면 각 행(row)마다 연도(year)로 되어 있고, 각 열(column)마다 학 행에 대한 월(month)로 되어 있다.

이렇게 연월표로 작성된 테이블을 하나의 열(column)에 대하여 각 연도와 월로 결합된 테이블로 다시 만들 수 있다.

사용할 함수는 위에 링크를 단 Index, Function MS Excel 함수를 사용하자.

..

Ex 002

..

위에 있는 그림을 보면, 크게 숫자가 있는 세 부분으로 나눌 수 있다.

1번 부분은 연월표로 작성된 표가 있는 부분, 2번은 이렇게 행과 각 열들로 분산하여 연월표로 있는 부분을 나머지 3번 부분은 2번에 대하여 1번에 있는 표 값을 하나의 열(column)으로 재 작성한 결과를 보여주는 것이다.

..

2번 부분은 1번 부분에 있는 연도와 각 월에 대하여 하나의 열(A열)에 대하여 직접 입력하면 된다.

위에 있는 부분은 1992년 01월 ~ 2014년 12월까지 A열을 확장하였다.

..

이렇게 2번에서, 하나의 열에 대하여 길게 쓰여진 값에 대하여 1번에 있는 연월표를 변환하면 되는데….

사용한 식은 다음과 같다.

=INDEX($Y$2:$AK$25, MATCH(YEAR($A3), $Y$2:$Y$25, 0), MATCH(MONTH($A3), $Y$2:$AK$2, 0))

바로 위에 있는 식은 3번 부분에 있는 3행 W열에 대한 식이다.

Index 함수에서 첫번째 매개변수 부분은 $Y$2:$AK$25인데, 이것은 1번 부분에 있는 테이블을 가르키는 값이다. MS Excel에서 사용하는 절대주소와 상대주소 체계에 대한 설명은 생략한다.($에 대한 설명임.)

..

위에서 사용한 Index 함수에서 첫번째 매개변수는 원본이 있는 테이블을 가르키고, 두번째 매겨변수는 첫번 째 매개변수에 있는 테이블에서 행(row)을 세번 째 매개변수는 열(Column)을 사용하게 되어 있다.

Index 함수에서 사용하는 두번 째 매개변수 Match(Year($A3), $Y$2:$Y$25, 0) 부분에서….

Year() 함수는 Year 함수에 있는 매겨변수 A3가 위 그림에서 가르키는 값 “1992/01″에서 연도(Year)를 가져올려고 쓴 함수이다. 이렇게 A열에 있는 각 값(==> Year)은 1번 표에 있는 “Y2:AK25″에서 행(row)을 선택하기 위해서 쓰이고 있다.

Match(Year($A3), $Y$2:$Y$25, 0) 부분에서 가르키는 값은 위에 있는 그림대로 한다면, 다시 다음과 같이 변환될 것이다.

Match(1992, $Y$2:$Y$25, 0)

결과적으로, 위에 있는 함수는 “Y2:Y25″에 있는 테이블에서 “1992”가 있는 부분을 찾으라는 뜻이다.

..

마찬가지로, 위에 있는 Index 함수에서 MATCH(MONTH($A3),$Y$2:$AK$2,0) 부분은 A3가 가르키는 “1992/01″에서 월 부분을 가져오고(month(A3) 함수에서), 그 월에 해당하는 값을 테이블 “Y2:AK2“에서 찾으라는 뜻이다.

MATCH(MONTH($A3), $Y$2:$AK$2, 0)

===> MATCH(1, $Y$2:$AK$2,  0)

..

이렇게 하면, 위에 있는 그림에서 W3 Cell에 있는

=INDEX($Y$2:$AK$25, MATCH(YEAR($A3), $Y$2:$Y$25, 0), MATCH(MONTH($A3), $Y$2:$AK$2, 0))

함수는 다음과 같이 중간 값으로 변환이 될 것이다.

=INDEX($Y$2:$AK$25, MATCH(YEAR($A3), $Y$2:$Y$25, 0), MATCH(MONTH($A3), $Y$2:$AK$2, 0))

==>

=INDEX($Y$2:$AK$25, MATCH(1992, $Y$2:$Y$25, 0), MATCH(1, $Y$2:$AK$2, 0))

==>

=INDEX($Y$2:$AK$25,  1, 1)

..

마찬가지로 W4 Cell에 있는 값도 A4 Cell에 있는 “1992/02″에 해당하는 값을 1번에 있는 테이블에서 가져올 수 있다.

=INDEX($Y$2:$AK$25, MATCH(YEAR($A4), $Y$2:$Y$25, 0), MATCH(MONTH($A4), $Y$2:$AK$2, 0))

==>

=INDEX($Y$2:$AK$25, MATCH(1992, $Y$2:$Y$25, 0), MATCH(2, $Y$2:$AK$2, 0))

==>

=INDEX($Y$2:$AK$25, 1, 2)

..

이런 방법으로 반복을 하면 A열에 대한 값들을 W열에 구할 수 있다.(그림에서 3번 부분.)

..

사용한 함수는 크게,

Index와 Match 함수이다.

Year()와 Month() 함수는 생략하자.

..

..

..

Written by MayBeMayBe

January 15, 2015 at 21:29

Posted in Programming

Tagged with