조건부 서식에 들어갈건데, 거기서 입력할 함수가 요전이랑 좀 다르다. 범위를 지정하고 조건부서식->새 규칙->수식을 사용하여 셀 입력으로 가서 =AND(SEARCH($G$2,B3),$G$2<>"")를 입력하고 확인을 누르면 된다. SEARCH함수는 문자열에서 특정 텍스트의 시작 위치를 찾는 함수인데, 찾고자 하는 문자가 없으면 VALUE!를 띄우고 있으면 문자열의 위치를 나타낸다. 그러니까 SEARCH함수로 검색 됐으면서 검색란이 공백이 아닐 때 조건부서식이 발동되는 셈.
이거 해 볼거다. 얘도 뭐 거창한건 없고 Filter함수 쓸 거라 그렇게 어렵진 않다. 근데 Filter함수를 쓰려면 버전이 몇이어야 된다? 그죠 365여야 합니다...
여기 가상의 명단이 있다. 그리고 검색 바로 뭘 할거냐면 지역으로 회원을 검색할거다.
이게 된 게 맞는데 설명하기 복잡쓰... 그래도 해보자.
일단 영상과 달리 검색 바가 없는 이유는 간단하다. 내가 지금 웹버전으로 돌리고 있는데, 여기서는 개발 도구가 없어서 체크박스 외에는 다른게 추가가 안된다.
엑셀 프로그램으로 돌리는 분들은 개발도구-삽입-텍스트 상자 넣고 속성 들어가서 Linkedcell 설정하면 된다.
함수에는 =FILTER(F3:H19,LEFT(G3:G19,LEN(C2))=C2,"none")가 들어가 있는데... 엥? LEN이 왜 들어갔어요? 이거 문자열 길이 구하는 함수다. 영상에서 보면 차종이 토요타인데 TOYOTA 다 안 쓰고 TOYO까지만 써도 검색 되죠? 아마 저게 len함수를 써서 그게 가능한듯 하다.
여기 검색란에 경만 친 거 보시면 경기, 경남, 경북이 다 같이 검색되는 것을 볼 수 있다.
일단 이게 결과물이다. 아니 이걸 어떻게 하셨죠? 일단 기본적인 방법은 위에 적혀있는거랑 같은데, 조건부서식에 들어가는 함수가 다르다. 행만 강조할때는 =CELL("ROW")=ROW()였지만 여기서는 =OR(CELL("ROW")=ROW(),CELL("COL")=COLUMN())를 쓰면 된다. 물론 코드 들어가서 워크시트 누르고 Activecell.Calculate 써야 하는 건 동일하다.
처음에 로우 컬럼 같이 해야하나 했는데 저거였나...
본인이 Office 365를 쓴다면 이런 복잡한 노가다 필요 없이 보기-포커스 셀 가서 누르면
코딩 좀 해보신 분들은 들어봤을 정규식을 활용해서 뭔가 하는 함수이다. 정규식이 뭔지 설명할때 아 그빵 뭐였지 **빵 이런 식으로 와일드카드 써서 검색하는? 뭐 특정 패턴을 인식하는? 뭐 그런 거라고 설명을 하는데, 실제로 코딩할때 정규식 패턴을 이용해서 유효성 검사를 한다. 전화번호는 000-0000-0000 형식이니까 숫자 세개, 다음에 네개, 또 다음에 네개로 패턴을짜면 되고 이메일은 아이디 골뱅이 도메인(뭐시기닷컴, 뭐시기오알지) 구별할 수 있게 패턴을 짠다.
여기 전화번호가 있다. 여기서 가운데 숫자를 REGEXREPLACE 함수를 이용해 ****로 바꿔볼거다.
=REGEXREPLACE(C2,"-[0-9]+-","-****-")를 입력하면 된다. 롸? 이게 왜 이렇게 되나요? 정규식 패턴을 짤 때는 토큰들을 조합해서 쓰게 되는데, [0-9]는 0부터 9까지(숫자)를 의미한다. 그 옆에 +가 붙어있으니까 하나 [0-9]+는 하나 이상의 숫자가 된다. 양 옆의 -는 가운데 네 자리만 *로 대체하기 위해서 넣은거다. 저걸 뒤에만 넣으면 010까지 같이 *로 변환되고, -를 다 빼면 전화번호 자체가 가려진다.
파일이 개발살나서 이름에 특수기호가 섞여버린 상황이다. 사실 파일이 개발살나면 열리는것만 해도 기적이고 폰트 지원도 안되는 뭐 뷁 이런거 나오지만 넘어가자. 여기서 특수문자를 어떻게 지우나요? 하나하나 지워요?
한글에 대응하는 정규식 토큰을 쓰면 된다. =REGEXREPLACE(B2,"[^ㄱ-ㅎ|ㅏ-ㅣ|가-힣| ]","")에서 [ㄱ-ㅎ|ㅏ-ㅣ|가-힣]이 한글에 대응하는 토큰이고 ㄱ 앞에 붙어있는 ^는 얘네 빼고라는 의미이다. 저 작대기는 쉬프트+\ 하면 입력할 수 있는 막대기인데, 의미는 OR이다. 그니까 대괄호 안에 있는 건 이거 혹은 이거 혹은 이거 빼고라는 얘기. 근데 뒤에 공백은 왜 들어갔냐면 로버트 한 이름이 공백이 있어서...
데이터에 영문이 섞여있을 때 토큰을 저렇게 짜면 영문도 공백처리 된다. 그니까 저게 내가 예시만 쓴 게 아니라 함수까지 자동완성한 게 맞음... 그럼 영어는 어떻게 하냐고?
뭘 어째요 토큰 두개 추가해야지... 대괄호 안에 a-z랑 A-Z를 추가해서 =REGEXREPLACE(B2,"[^ㄱ-ㅎ|ㅏ-ㅣ|가-힣|a-z|A-Z| ]","")로 써 주면 된다. [a-z]는 영어 소문자, [A-Z]는 영어 대문자.
이건 일단 이메일이다. 예시니까 저기다 뭐 보낼 시도는 하지 말자. 이 이메일로 뭐 할거냐면, REGEXREPLACE 함수를 이용해서 이메일의 아이디와 도메인을 분리할거다. 아이디에는 골뱅이 앞에 있는 것, 도메인에는 골뱅이 뒤에 있는 게 들어갈 예정이다.
아이디는 =REGEXREPLACE(B2,"@[a-z|A-Z|.]+","")으로 뒷부분을 떼버렸다. 골뱅이랑 영어+닷까지 대체된거 맞다. 혹시 도메인에 숫자가 포함된 경우가 있다면 토큰을 [0-9|a-z|A-Z]로 하자.
도메인은 아이디만 뗴버리면 된다. =REGEXREPLACE(B2,"[0-9|a-z|A-Z]+@","")로 떼버리면 아이디에 숫자가 포함될때도 알아서 대체될 것이다. 도메인이라면 몰라도 보통 아이디에는 숫자 들어가는 경우가 꽤 있으므로...
파이썬이나 다른 프로그래밍 언어에서는 정규식을 쓸 때 \d(숫자) \s(공백) 이런 식으로 백슬래시를 붙여서 쓰는 경우가 있다. 그리고 글자 수를 {3} {4} 이런 식으로 중괄호 안에 쓰는데, 내가 그것도 해봤더니 된다. 그러니까 맨 처음에 예시로 들었던 전화번호 가운데만 ****로 바꾸는 패턴이 =REGEXREPLACE(C2,"-[0-9]+-","-****-")도 되고 =REGEXREPLACE(C2,"\d{4}-","****-")도 된다는 얘기. 뒤에 있는 건 숫자 네자리로 지정한건데, 뒤에 하이픈이 없으면 전화번호가 010 빼고 다 ****로 바뀐다.
차트를 그릴 때 데이터가 꽤 많이 있는 경우가 있는데, 이 때 이거빼고 그려줘 저거빼고 그려줘 하면 어떡합니까? 일일이 선택해서 그립니다... 차트에 삽입되는 데이터 또 한번에 선택되는것도 아니잖음... 이걸 365에서는 체크박스를 쓰면 딸깍으로 보여줄 수 있다 이거다.
여기서 국영수만 보고싶다 하면 사회랑 과학을 하나씩 선택해서 지우는게 보통인데... 이런게 한두개가 아니면 캐귀찮아요...
표 위에 체크박스를 넣었다. 근데 이것만으로 바로 딸깍이 되지는 않는다.
일단 원본 표를 다른데다 치웠다. 내용을 삭제한 게 아니다. 삭제하고 나서 C3셀에 커서를 올리고 =IF(C$1,L23,"")를 입력한 다음 쭉 드래그하면 공란이죠? 정상이다.
국어 영어 수학만 보기 위해 위 체크박스를 국영수에만 뒀더니 국영수만 그래프로 나타났다.
근데 막대가 쏠려서 안이쁜게 함정... 위 사진에서 국영수만 그렸던 그래프도 보면 오른쪽이 비어있는데, 사회/과학 자리다.
가끔 어떤 일을 할 때 사수 혹은 상사가 "잘 돼가?"라고 물어볼 때도 있고, "얼마나 했어?"라고 물어볼때도 있다. 이럴 때 두루뭉술하게 말하면 나도 그렇지만 질문하는 사수나 상사도 그래서 어디까지 됐다는거임? 할텐데, 이럴 때 정리해두고 한 n%정도 됐습니다 하면 아 이정도 됐구나 할 거 아뉴.
구버전(365 이전)
뭐 대충 이렇게 있다 치자...
영상에서는 Alt+N+C+B로 삽입하던데 나는 개발 도구까지 가서 삽입했음... 영상 속 리본메뉴에는 삽입란에 체크박스가 있었다. 아무튼 그래서 내껀 구닥다리임... ㅡㅡ
그 다음에 H2셀로 가서 =REPT("|",COUNTIF(C3:G3,TRUE))를 치면... 안되는게 맞다. 저 체크박스를 하나하나 셀에 연결해야 하거든... 그러니까 저 다섯개를 일일이 저 셀에 연결해야 한다. 정말 귀찮다.
그리고 글자색을 흰색으로 만들어서 대충 구색을 갖춰야 하기 때문에 구버전에서는 이 방법을 추천하지 않는다. 그럼 365 사용자는요? 따라오십시오.
Office 365 사용자
일단 365 사용자들은 일일이 셀이랑 연결하고 자시고 할 필요가 없다.
여기 확인란 보여요? 이걸 누르면
이런식으로 다이렉트로 삽입이 된다. 일일이 연결이요? 아이 하지마 엑셀이 다 해줘.
실험실에서 대장균을 도말한다 치면 대충 이런 단계로 진행이 된다. 실험의 경우 절대 막히면 안되기 때문에 진행하기 전에 필요한 시약이나 배지가 다 있나 확인해보고 없으면 본인이 만들어야 한다. 배지가 만들어져 있으면 뭐... LB정도는 뭐 걍 써도 될것이다. LB배지도 없으면 오토클레이브부터 돌려야겠지만. 아무튼 이걸 얼마나 진행했는지 막대그래프로 나타내보자.
처음에 rept 썼더니 에러뜨더라... 아무튼 이렇게 하면 된다. 저 안에 있는거? ▒ <<이거다.
=ROUND(COUNTIF(D3:D8,TRUE)/COUNTA(D3:D8) * 100,2) & "%"를 쓰면 백분율로 표현할 수도 있다. 원리가 뭐냐고? 일단 ROUND함수는 나중에 설명하고 저 안에 있는걸 보자. COUNTIF(D3:D8,TRUE)는 체크박스 중에 체크된 것만 세라는 얘기이고, COUNTA(D3:D8)는 저 전체 개수가 된다. 그러니까 위 사진은 2/6이 되는데, 그럼 라운드 함수가 왜 있냐고?
1/3은 정확히 나누어 떨어지지 않고 0.33333...이다. 근데 소수점이 너무 많으면 보기 싫잖아요... 그래서 ROUND함수를 써서 소수점 아래 둘째자리까지 줄인 것이다.
프로젝트별로 이렇게 해도 되는데... 이게 모든 단계 총합을 어떻게 내야 할 지 모르겠음...ㅋㅋㅋㅋㅋㅋ
예를 들어서 행정구역이 있다, 그러면 본인은 시 구 동 이런 단위별로 나눠서 행에 저장한다. 근데 안 그럴 때도 있잖아요? 그러니까 예를 들어서
이런 식으로 있는데 서울시 매출 합쳐달라고 하면 아 이거... SUMIF 쓰면 되나...? 그럼 저거 다 서울시만 가져와야겠네? 아니 그니까 그럴 필요가 없다 이거다.
아니 어떻게 하셨음? 설마 일일이 옮겨놓고 거기만 안 찍으신거?
그건 아니고, SUMIFS 함수의 조건 부분에 와일드카드를 썼다. 그 왜 구글 검색할 때 아 그거 무슨 빵이었지? 하고 **빵 이런 식으로 검색할 때가 있다. 아니면 뭐 그런거 있잖음. 노래 제목을 일부만 알 때 **은 아무나 하나 노래 이런 식으로 검색하는데, 여기서 애스터리스크(*)가 와일드카드다. 조건에 와일드카드를 *서울*로 친 건 서울이 들어가는 모든 걸 찾으라는 얘기.
이 예제는 시가 앞에 와 있기 때문에 =SUMIFS(C3:C17,B3:B17,"서울*")로 해도 된다. 근데 조건을 "서울?"로 설정하면 서울 뒤에 한 글자만 찾아주니까 반드시 별을 붙이십시오.
번외편: FILTER()함수에서도 와일드카드를 쓸 수 있을까?
뭐 접미어로 필터 돌려봅시다... 예... 영어가 먼저 나와서 글치 저거 다 많이 접해본것들입니다. 예.
찾아보니 필터함수 에러나면 저렇게 뜬단다. 구글링 했는데 필터함수에 와일드카드는 걍 안된다 생각하는 게 맞나... 그럼 방법이 없나요?
E열은 =SEARCH("*ose",B2:B14)이고, F열은 =ISNUMBER(SEARCH("*ose",B2:B14))이다. 저기서 에러가 뜬 건 -ose가 없기 때문인거고, 에러가 떴으니 당연히 FALSE가 뜬 것. 그럼 저걸 FILTER함수랑 조합하면 어떻게 되나요?
=FILTER(B2:C14,ISNUMBER(SEARCH("*ose",B2:B14)))를 쓰면 된다.
검색어 입력하는 셀을 만들면 되겠는데?
근데 얘는 왜 에탄올에 메탄올이 따라오는걸까...
SEARCH함수를 FIND함수로 바꿨더니 정확하게 에탄올 메탄올만 찾아준다. 단, FIND함수는 와일드카드를 지원하지 않기 때문에 와일드카드 사용 시 오류가 뜬다.
