엑셀 Become awesome in EXCEL
엑셀 LOOKUP 함수 완벽 가이드: 마지막 값 찾기와 조건 활용법
2024. 11. 19.엑셀에서 특정 열의 마지막 값은 다음과 같이 찾을 수 있다. 여기서는 LOOKUP 함수를 사용하여 마지막 값을 찾아보자. 위 예제에서 카운트 열(B열)에 마지막 값이 존재하는 과일의 이름은 다음과 같이 찾을 수 있다.=LOOKUP(2,1/(B2:B10""),A2:A10) 여기서 A2:A10은 값이 있는 열 범위로, 즉, 마지막 값 존재 여부를 따지는 열로 필요한 열로 변경해서 사용하면 된다. 그리고 B2:B10"" 조건으로 여기서는 값이 비어있지않은 셀을 찾는데 사용된다.마지막으로 LOOKUP(2,1/조건,범위)은 조건에 맞는 가장 마지막 값을 찾는 수식이다. 그럼 여기서 1/조건에 대해서 더 살펴보자. 1/조건은 조건을 만족하는 셀에 대해 1 값을 반환하고, 조건을 만족하지 않는 셀에는 #DIV/0! ..
엑셀 한 셀에서 단일 문자의 개수를 세는 수식
2024. 11. 1.한 셀에서 문자의 개수 세기 아래 예제에서 A3에서 문자 "a"의 개수를 셀려면 다음 수식을 입력하면 된다. A3 셀의 전체 길이에서 A3 셀의 "a" 문자를 공백으로 바꾼 길이를 빼면 공백으로 바뀐 문자의 수, 즉 "a" 의 개수를 셀 수 있다.=LEN(A3)-LEN(SUBSTITUTE(A3,"a","")) 범위에서 단일 문자의 개수를 세는 수식마찬가지 예에서 범위에서 특정 문자의 개수를 세는 것도 마찬가지 방법으로 하면 된다.셀 범위에 속한 각각의 셀의 길이를 합한 다음에 문자 "a" 를 공백으로 바꾼 뒤 각 셀의 길이의 합을 구한 뒤 빼주면 된다. 다만 이 경우 배열 수식으로 입력해야 하므로, 수식 입력 후 CTRL+SHIFT+ENTER를 눌러야 한다. =SUM(LEN(A2:A8)-LEN(SUBS..
엑셀 콤마를 탭으로 바꾸기 (엑셀 텍스트 나누기 마법사로 텍스트를 여러 열로 나누기)
2021. 1. 10.아래는 손으로 쓴 숫자들로 이루어진 MNIST (Modified National Institute of Standards and Technology database) 이미지 데이터의 하나로 숫자 9를 나타낸다. 이를 엑셀 시트에 붙여서 조건부 서식 > 색조 기능을 통해 대략 정말 숫자 9처럼 보이는지 확인해보자. 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,..
여러 개의 조건에 부합하는 셀 갯수 카운트하기 How To Countif With Multiple Criteria In Excel
2020. 7. 17.텍스트 값에 기반한 여러개의 조건에 맞는 셀 카운트하기 Count Cells With Multiple Criteria Based On Text Value 다음의 데이터를 가지고 동일한 컬럼에 분포된 Clothes 와 Cosmetics의 수를 카운트해보자. 이를 위해서는 단순히 각각의 countif 함수를 합치면 된다. =COUNTIF($A$2:$A$30,"Clothes")+COUNTIF($A$2:$A$30,"Cosmetics") 위의 식에서 A2:A30 은 데이터 범위이고, Cosmetics 와 Clothes 는 카운트하고자하는 검색조건이다. 하나의 컬럼에서 2개 이상의 조건을 사용하는 경우, 다음과 같이 각각의 값을 구하는 수식을 합치면 된다. =COUNTIF(range1, criteria1) + CO..
텍스트가 포함한 셀 갯수 카운트하기 How To Count If Cell Contains Text Or Part Of Text In Excel
2020. 7. 17.아래와 같은 데이터를 가지고 있고, 여기서 "Central"이라는 텍스트가 포함된 셀의 갯수를 카운트하기 원한다고 해보자. COUNTIF 함수를 사용하여, 텍스트 또는 특정 텍스트 일부가 포함된 셀의 갯수를 카운트할 수 있다. COUNTIF 함수의 구문은 다음과 같다. =COUNTIF (range, criteria) 아래 2개의 인수가 사용되는데 Range, Criteria 모두 필수이다. Range 카운트하고자하는 셀의 범위 Criteria 수, 표현식, 셀참조, 또는 텍스트문자열 등 카운트할 대상 위의 예에서는 B4:B33 범위의 셀에서 Central 이라는 텍스트를 포함하고 있는 셀의 갯수를 카운트하기 위해 D4 셀에 다음과 같은 함수를 입력하였다. =COUNTIF(B4:B33,"*Central*"..
엑셀 비밀번호 찾기 Advanced Office Password Recovery
2020. 5. 7.엑셀 파일 비밀번호 복구 소프트웨어를 검색해보면 대부분 유료로, 트라이얼 버전으로 테스트조차 못하는 경우가 많다. 여기서 소개하는 Advanced Office Password Recovery 는 제약은 있지만 트라이얼 버전으로 사용이 가능한 소프트웨어이다. 간단한 비밀번호 찾기에는 사용이 가능하며, 아래에서 다운로드할 수 있다. https://www.elcomsoft.com/aopr.html Advanced Office Password Recovery | Elcomsoft Co.Ltd. Recover, remove or circumvent passwords protecting documents created with a variety of office suites. Break passwords to Mi..
엑셀 특정 문자 앞 또는 뒤의 값 출력하기 Extract Substring After Or Before A Specified Character
2020. 3. 25.예제에 사용할 데이터는 Roguefitness 카테고리별 새롭게 등록된 상품수 데이터로, 여기에서 숫자만 추출하는 방법을 알아보자. 다음과 같이 카테고리명 다음 ( ) 안에 상품수가 들어가 있다. 먼저 FIND 함수로 ( 가 시작되는 위치부터 파악해보자. 아래를 통해 FIND 함수에 대해 더 알아보자. 기본 구문은 다음과 같다. FIND 함수는 대/소문자 구분하며, 와일드카드 문자는 사용할 수 없다. FIND(find_text, within_text, [start_num]) find_text 찾으려는 텍스트로 여기서는 ( 입니다. within_text 찾으려는 텍스트를 포함하는 검색 대상 텍스트로 여기서는 A 열이다. start_num 검색을 시작할 문자를 지정하는 것으로, within_text, 즉 A..
화폐, 퍼센트, 소수점 포맷 유지하면서 concatenate 함수 사용하기 Concatenate With Keeping Number (Currency/Percentage/Decimal Places) Format In Excel
2020. 3. 13.이번 포스팅에서는 통화, 백분율, 소수점 등의 수와 관련된 포맷을 그대로 유지하면서 CONCATENATE 함수를 사용하는 방법을 알아봅니다. 아래의 예를 통해 살펴보겠습니다. 아무런 가공을 하지 않으니 가독성이 현저히 떨어집니다. 먼저, 합치고자하는 대상의 포맷부터 확인해야할 필요가 있습니다. 해당 셀에서 마우스 오른쪽 버튼을 눌러 셀 서식으로 이동합니다. 표시 형식 탭으로 이동하여 범주에서 사용자 지정을 고릅니다. 여기서 형식에 나오는 내용을 복사합니다. 여기서는 _-[$$-409]* #,##0.00_ ;_-[$$-409]* -#,##0.00 ;_-[$$-409]* 이 됩니다. 복사할 내용을 다음과 같이 TEXT 함수를 이용하여 포맷을 지정해줍니다. 기존의 F2 를 다음과 같이 변경하는 것입니다. 그리..
엑셀 CONCATENATE 함수 사용시 날짜 포맷 유지하면서 합치기Concatenate With Keeping Date Format In Excel
2020. 3. 13.엑셀에서 CONCATENATE 함수 사용시 어떻게 하면 날짜 포맷을 그대로 유지하면서 가져올 수 있는지 알아보자. 날짜를 인수로 가져와 CONCATENATE 함수를 사용하는 경우, 별다른 조치를 하지 않으면 아래와 같이 2020-03-01 이라는 날짜가 43891 로 표시된다. 여기서 43891 은 1990년 1월 1일을 1로 시작하여 매겨나간 경우 2020년 3월 1일에 대항되는 값이다. =CONCATENATE(A2," : ",B2,"명") 43891 : 3736명 기존 날짜 포맷을 그대로 가져오기 위해서는 다음과 같이 TEXT 함수를 통해 읽기 좋은 포맷으로 지정할 수 있다. =CONCATENATE(TEXT(A5,"yyyy-mm-dd")," : ",B5,"명") 2020-03-04 : 5328명 적용..
엑셀 특정 비율만큼 난수 생성하기 Random number weighted probability
2020. 3. 8.Random number weighted probability 각 항목별 정해진 비율로 난수를 생성하는 방법을 알아보자. 다음과 같이 랜덤하게 A에서 H까지 8개의 값을 생성하고자 한다. 다만, 각 값이 균등하게 생성되는 것이 아니라, 아래에서 값별로 정해놓은 비율만큼만 생성되도록 해야한다. 어떻게 구현할 수 있는지 살펴보자. 이를 다루기 전에 텍스트가 아닌 숫자의 경우 구현하는 방법부터 알아보자. 아래의 예이다. 기본 수식은 다음과 같다. RAND 함수와 MATCH 함수에 기반을 둔 헬퍼 테이블을 사용하여 주어진 비율만큼 랜덤한 수를 생성할 수 있다. =MATCH(RAND(),누적확률) 이 수식은 C3:E10 의 범위에서 볼 수 있는 헬퍼 테이블에 의존한다. C열에는 실행결과로 생성하고자 하는 8개의 ..
엑셀 셀에서 텍스트 줄 바꿈
2020. 2. 15.아래와 같이 셀의 내용이 셀 너비를 초과하여 나오는 경우, 셀의 너비에 맞게 텍스트줄을 바꾸고 싶다면 다음과 같이 하면 된다. 해당 셀을 선택하고 아래와 같이 홈 탭의 맞춤 그룹에서 텍스트 줄 바꿈 을 클릭합니다. 셀 안의 데이터가 자동으로 줄바꿈이 된 것을 확인할 수 있다. 여기서 열 너비를 변경하면 데이터 줄 바꿈이 자동으로 조정된다.
엑셀 최우측값 찾기 How to get right-most value of row in Excel?
2020. 2. 15.Photo by Lukas from Pexels 위의 표에서 보듯이 각 행의 가장 우측값(색깔이 들어간 셀의 값)을 가져오는 함수를 만들어보자. 아래 그림과 같이 E2 란에 다음의 함수를 입력하고 아래로 드래그한다. =LOOKUP(2,1/(A2:C2""),A2:C2) 이 함수가 어떻게 작동하는지 하나씩 살펴보자. 먼저 A2:C2"" 는 다음과 같이 True, False 값으로 구성된 Array 를 반환한다. 1 을 앞서 반환된 배열로 나눈 결과로 새로운 배열을 만든다. 그러면 아래와 같이 divide by zero errors (#DIV/0!) 로 구성된 배열을 만들 수 있다. 이 배열이 바로 2번째 인수인 Lookup Vector 이다. 만약 Lookup Vector 에서 Lookup Value 를 찾..
엑셀에서 값으로 차트 그래프 정렬하기
2018. 8. 24.원본데이터의 정렬 순서에 따라 차트상의 막대그래프의 정렬도 달라진다. 필터를 이용하여 원하는 열을 정렬할 수도 있고, 아래에서와 같이 데이터 탭에서 원하는 열 선택 후 오름차순이나 내림차순으로 정렬해도 된다.아래는 응시생 기준으로 정렬해본 경우다. 정렬이 되었음을 알 수 있다.국가명으로 정렬하고 싶으면 국가열을 선택 후 데이터 탭에서 정렬순서를 선택하면 된다.
엑셀 여러 셀의 날짜와 텍스트를 하나의 셀에 결합하기
2016. 10. 15.concatenate 또는 & 연사자를 이용하여 셀의 날짜과 텍스트를 결합하면 아래에서 보는 바와 같이 숫자로 변환되어 결합이 된다. 이렇게 연결 연산자를 사용하여 날짜를 텍스트 문자열에 연결할 때는 TEXT 함수를 사용하여 숫자가 표시되는 방식을 제어할 수 있다. = CONCATENATE(E491, F491, TEXT(G491, "yyyy-mm-dd")) TEXT 함수에는 value 및 format_text의 두 개 인자가 필요하며, format_text 는 "m/d/yyyy" 또는 "#,##0.00" 와 같이 따옴표로 묶인 텍스트 문자열인 날짜 또는 숫자 형식이다. [참고] https://support.office.com/ko-kr/article/%ED%85%8D%EC%8A%A4%ED%8A%B8%EC..
다중조건 IF 문 (IF formulas with multiple conditions)
2015. 8. 25.IF문의 조건절이 하나가 아닌 2개 이상의 조건을 만족하는 경우를 따져야하는 경우를 살펴보자. 간단하게 AND 와 OR 이상 2가지의 경우를 생각해볼 수 있다. 1. IF & AND function AND 의 경우 모든 조건이 만족해야 TRUE 를 반환하고 하나라도 만족하지 못하면 FALSE 를 반환한다. 함수는 다음과 같다. =IF((AND(C2>=100, D2>=200)), "A", "B") 위에서 보듯이 C2의 값이 100이상이고 D2의 값이 200 이상인 경우에 A 값을, 그리고 위의 2가지 조건 중 하나라도 만족을 못 시키면 B 를 반환한다. 2. IF & OR function AND와 달리 OR은 다중 조건 중 하나만 만족해도 TRUE 를 반환한다. 식은 다음과 같다. =IF((OR(C2>=1..
엑셀 복소수 연산 함수 (엑셀을 이용한 허수 계산)
2015. 4. 17.복소수 연산을 위해서는 아래와 같이 추가기능에 분석도구를 선택해서 사용가능하게 해두어야 한다. 복소수 관련 주요 함수는 다음과 같다. 함수 함수의 기능 COMPLEX(a,b) a + i b 형태의 복소수 생성 IMABS(z) 복소수 z의 절대값 계산 IMSUM(a, b) 두 복소수의 합 계산 IMSUB(a,b) 두 복소수의 차 계산 IMPRODUCT(a,b) 두 복소수의 곱 계산 (최대 255개까지) IMDIV(a,b)두 복소수이 나눗셈의 몫 반환 IMREAL(z) 복소수 z의 실수부 반환 IMAGINARY(z) 복소수 z의 허수부 반환 예제. 3+4i, 2-i 에 대해 위의 함수를 적용해보면 다음과 같다 imsum 두 복소수의 합 반환 =IMSUM(A6,B6)=5+3i imsub 두 복소수의 차 반환 ..
구글 스프레드시트를 이용한 [성적관리시스템] 만들기 (1) - 입력 데이터 목록 만들기, 학생명단 만들기
2015. 3. 14.구글 스프레드시트를 이용하여 간단한 성적관리 프로그램을 만들어보자. 먼저 필요한 화면들을 생각해보자. 1) 데이터 입력 : 학생명단 입력, 시험 생성, 성적입력 등 2) 목록관리 : 데이터의 일관성을 확보하기 위해 별도 시트에서 입력되는 데이터의 목록을 관리한다. 3) 결과 분석 : 반별 성적표, 개인별 성적표, 반별 비교 등 그럼 본격적인 작업을 위해 구글 문서편집기로 이동한다. https://docs.google.com/spreadsheets/u/0/ 위의 링크로 들어가 하단의 + 아이콘을 클릭하면 아래와 같이 새로운 스프레드 시트가 열린다. 목록을 만들자 먼저 만들 것은 다음과 같이 목록의 내용이다. 이는 일관된 데이터의 사용을 위함이다. 예를 들면, 어떤 학생은 자신의 출신학교를 '한국대'라..
사인, 코사인 함수 그리기
2014. 7. 2.각도를 α 라고 했을 때, 코사인 및 사인값을 구하는 함수는 다음과 같다. (α 값의 셀 위치가 A2 라고 했을 때) cos(α) = cos(radians(A2) sin(α) = sin(radians(A2) 아래 테이블에서 결과값을 선택하고 삽입 - 분산형 - 곡선이 있는 분산형 을 선택하면 사인, 코사인 함수그래프를 그릴 수 있다. α cos(α) sin(α) 0 1.0000000000 0.0000000000 5 0.9961946981 0.0871557427 10 0.9848077530 0.1736481777 15 0.9659258263 0.2588190451 20 0.9396926208 0.3420201433 25 0.9063077870 0.4226182617 30 0.8660254038 0.5000..
[날짜] YEARFRAC 함수
2014. 2. 11.YEARFRAC 함수의 수식 구문과 사용법에 대하여 [정의]YEARFRAC 함수는 두 날짜 사이의 날짜 수가 일년 중 차지하는 비율을 반환하는 함수입니다. YEARFRAC 함수를 사용하면 특정 기간에 대한 연간 이익 또는 채무의 비율을 구할 수 있습니다. [구문]YEARFRAC(start_date, end_date, [basis]) [인수]start_date : 시작날짜(필수요소)end_date : 종료날짜(필수요소)[basis] : 날짜계산기준(선택요소)
OFFSET 함수와 양식컨트롤 이용하여 엑셀 챠트에 스크롤바 만들기 (1)
2013. 2. 4.장기간에 걸친 데이터를 챠트로 표현할 경우 챠트에 슬라이더바가 없으면 데이터의 간격이 너무나 조밀해져 트렌드를 보겠다는 목적이 아니라면 활용에 불편한 점이 많다. 그리고 데이터가 추가될수록 그 조밀함은 더해지게 되어 데이터 정보를 보기에 불편하다는 점, 그리고 데이터 간격이 일정치 않다는 단점이 있다. 예를 들면 아래와 같다. 아래는 2011년~2012년에 걸친 730개의 런닝 데이터를 챠트로 표현한 것이다. 주황색선은 누적 거리를 표현한 것이고 각각의 바는 해당일에 달린 거리를 유형별(필드, 트랙, 트레드밀)로 합산하여 나타낸 것이다. 그러면 위의 챠트에 1. 체크박스에 체크된 항목만 모아서 일자별 바를 만들고 2. 누적거리는 보조축에 꺽은 선 형으로 표현하고 3. 슬라이더 바를 달아 원하는 기간만큼 ..