[엑셀 함수 2024]

학습목표

• UNIQUE, SORT 함수 : 실시간 갱신 + 정렬되는 드롭다운 목록 만들기

• FILTER + SEQUENCE 함수 : 입력한 년도와 월에 따라 변하는 자동 달력 만들기

• TOCOL + WRAPROWS 함수 : 불규칙한 데이터를 올바른 구조로 가공하는 방법

• VSTACK + FILTER 함수 : 여러 시트 데이터를 실시간으로 취합하는 자동화 서식 만들기

동적배열 

---

=VLOOKUP( 찾을값, 검색범위, 열번호, [일치옵션])

 

가격 합계: =SUM(E8:E15)
가격 평균: =AVERAGE(E8:E15)
H8: =VLOOKUP(G8,B8:E15,2,0) 
	or =INDEX(C8:C15, MATCH(G8, B8:B15, 0)) - 열 순서 바꿔도 안정적

 

cf) 구버전은 배열함수 개념 사용: ctrl + shift + enter

동적배열: =VLOOKUP(G8,B8:E15,{2,3,4},0)

(주의) 배열의 범위에 값이 들어가면 #분산! 오류 발생한다.

 

단, 스프레드시트에서는  위의 배열 기능이 동작되지 않았으며 ARRAYFORMULA함수로 다양한 함수와 조합할 때 배열기능을 지원합니다. but 셀 하나에 값이 다 들어가기에 비슷한 동작은 아니였습니다. 

=ARRAYFORMULA(IFERROR(VLOOKUP(G8, B8:E15, 2, FALSE)) & " / " &
              IFERROR(VLOOKUP(G8, B8:E15, 3, FALSE)) & " / " &
              IFERROR(VLOOKUP(G8, B8:E15, 4, FALSE)))

 

해결 방법: INDEX + MATCH + SEQUENCE

아주 비슷하게 동작하는 동적 함수 조합을 쓸 수 있었는데 아래와 같았습니다. (자동채우기는 안 되는 ㅜ.)

=INDEX(C8:E15, MATCH(G8, B8:B15, 0), SEQUENCE(1, 3))

---

 

고유값

= UNIQUE ( 범위, [가로방향조회], [단독발생] )

---

부서: =UNIQUE(D10:D19)
풀네입: =UNIQUE(ARRAYFORMULA(B10:B19 & C10:C19))

 

 

참고: 고유값만 2열로 다시 나누고 싶은경우

=SPLIT(UNIQUE(ARRAYFORMULA(B10:B19 & "|" & C10:C19)), "|")

 

---

정렬

---

= SORT ( 범위, [기준열], [정렬방향], [가로방향정렬] )

부서별: =SORT(B10:D21,1,1)
부서별&매출액: =SORT(B10:D21,{1,3},{1,-1})
부서목록: =SORT(UNIQUE(B10:B21))
드롭다운: 데이터 > 유효성검사 > 제한 대상: 목록 > =$J$10#

 

스프레드시트:

1) 여러 범위 정렬

=SORT(B10:D21, 1, TRUE, 3, FALSE)

 

2) 참조 범위 묶기

=ARRAYFORMULA(J10:J) or =SORT(FILTER(J10:J100, J10:J100 <> ""))

단, 데이터 > 데이터 확인 > 드롭다운(범위)에서는 =SORT!J10:J (배열이 지원이 안 됨)

---

선택 열 반환

---

= CHOOSECOLS ( 범위, 열번호1, [열번호2], … )

 

머리글 따라 열 선택: =CHOOSECOLS(B10:F16,MATCH(H9:J9,B9:F9,0))

---

범위에서 검색

---

= XLOOKUP ( 찾을값, 찾을범위, 반환범위, [N/A값], [일치옵션], [검색방향] )

게임이름: =XLOOKUP(H10,D10:D18,B10:C18)
게임이름: =CHOOSECOLS(XLOOKUP(H10,D10:D18,B10:F18),1,4)
변경일시: =XLOOKUP(L10&M10,B10:B18&C10:C18)

 

스프레드 시트에서 변경일시: =INDEX(FILTER(E10:E18, B10:B18=L10, C10:C18=M10), 1)

 

---

연속된 숫자

---

1~1000까지: =SEQUENCE(1000)
순번: ="No. "&SEQUENCE(COUNTA(E10:E18))
달력만들기(G10): =SEQUENCE(6,7,P11-WEEKDAY(P11,1)+1)
	- 요일번호(R11): =weekday(P11,1)
    	- 조건부서식: =MONTH(G10)<>$P$10

 

스프레드 시트에서 순번: =ARRAYFORMULA("No. "&SEQUENCE(COUNTA(E10:E18)))

---

조건에 해당하는 데이터 필터링

---

 

부서목록: =SORT((UNIQUE(B8:B18))
목록 : =$F$8#
부서: =SORT(FILTER(B8:D18,B8:B18=I7),3,-1)

스프레드시트에서 목록: =FILTER!F8:F

스프레트시트에서 필터: =SORT(FILTER(B8:D18,B8:B18=I7),3,0)

CF) 응용: https://www.oppadu.com/엑셀-실시간-검색/

---

범위를 세로로 결합

---

= VSTACK ( 범위1, [범위2], … )

날짜: =VSTACK(B8:C9,B12:C13,B16:C17)
응용: =FILTER(VSTACK(서초:용산!A2:E16),VSTACK(서초:용산!A2:A16)<>"")
시작, 끝 시트 추가: =FILTER(VSTACK(시작:끝!A2:E16),VSTACK(시작:끝!A2:A16)<>"")

 

스프레드에서 여러 개 시트 취합

=VSTACK(
  FILTER('서초'!A2:E16, '서초'!A2:A16 <> ""),
  FILTER('용산'!A2:E16, '용산'!A2:A16 <> ""),FILTER('여의도'!A2:E16, '여의도'!A2:A16 <> "")
)

스프레드 시트에서 다중 시트 범위 참조가 안되는 듯 싶다.

 

해결: 아래 함수를 추가해서 해결 =FILTER_VSTACK_BETWEEN("시작", "끝", "A2:E16", 0)

앱 스크립트 > code.gs

function FILTER_VSTACK_BETWEEN(startSheet, endSheet, rangeStr, checkColIndex) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const sheetNames = sheets.map(s => s.getName());

  const startIndex = sheetNames.indexOf(startSheet);
  const endIndex = sheetNames.indexOf(endSheet);
  if (startIndex === -1 || endIndex === -1 || startIndex >= endIndex) {
    throw new Error("시작 시트와 끝 시트 이름을 정확히 지정하고, 시작이 끝보다 앞서야 합니다.");
  }

  let output = [];

  for (let i = startIndex + 1; i < endIndex; i++) {
    const sheet = sheets[i];
    const range = sheet.getRange(rangeStr);
    const values = range.getValues();

    const filtered = values.filter(row => row[checkColIndex] !== "");
    output = output.concat(filtered);
  }

  return output;
}

 

---

데이터전처리

---

= TOCOL ( 범위, [제외옵션], [읽기방향] )

 

=TOCOL((G10:H11,G14:H15,G18:H19),1)

스프레드시트: =TOCOL(VSTACK(G10:H11, G14:H15, G18:H19), 1)

 

= WRAPROWS ( 범위,나눌개수,[채울값] )

한 줄 배열: =TOCOL(G10:K27,1)
한 줄을 개수 만큼 여러 줄 자름: =WRAPROWS(TOCOL(G10:K27,1),7)

 

잘못된 데이터 를 올바른 데이터 구조로 바꾸는 초기화 과정에서 중간으로 한 줄 바꾸는 tocol이 중요하다.

 

이미지

종목번호: =XLOOKUP(E11,H:H,I:I)
url: ="https://ssl.pstatic.net/imgfinance/chart/mobile/mini/"&F10&"_end_up_tablet.png"
이미지: =IMAGE(C10)

 

스프레드 시트에서

url: ="https://ssl.pstatic.net/imgfinance/chart/mobile/mini/" & TEXT(F11, "000000") & ".png"
이미지: =IMAGE(C10)

이미지 차단된 경우 링크 첨부: =HYPERLINK("https://ssl.pstatic.net/imgfinance/chart/mobile/mini/" & TEXT(F11, "000000") & ".png", "차트 보기")

 

WINDOW에서 가능: 이름관리자 > stockinfo 이름으로 아래 수식 등록

 

LAMBDA(종목코드,
  LET(
    코드, TEXT(종목코드, "000000"),
    a, TEXTAFTER(TEXTBEFORE(WEBSERVICE("https://m.stock.naver.com/api/stock/" & 코드 & "/basic"), """,""compareToPrevious"), "closePrice"":""") * 1,
    b, IMAGE("https://ssl.pstatic.net/imgfinance/chart/mobile/mini/" & TEXT(IMAGE!$F$11, "000000") & ".png"),
    HSTACK(a, b)
  )
)

 

앱스크립트

function STOCKINFO(code) {
  code = code.toString().padStart(6, '0');
  const url = `https://m.stock.naver.com/api/stock/${code}/basic`;

  try {
    const response = UrlFetchApp.fetch(url);
    const data = JSON.parse(response.getContentText());

    const closePrice = data.closePrice;
    const imgUrl = `https://ssl.pstatic.net/imgfinance/chart/mobile/mini/${code}.png`;

    // IMAGE 함수 형태로 문자열 생성
    const imgFormula = `=IMAGE("${imgUrl}")`;

    // 2개의 값(주가, 이미지 수식) 배열로 반환
    return [[closePrice, imgFormula]];
  } catch (e) {
    return [["오류", ""]];
  }
}

Apps Script의 return이 항상 값만 전달하기 때문에 구글 스프레드시트에서 수식으로 바로 해석되지 않기 때문에 이미지는 수동으로 건드려야 나오는 한계...