필터, 중괄호, 쿼리로 불러온 데이터의 합을 구하고 싶습니다 외

구글시트
작성자
조성원
작성일
2023-05-27 19:18
조회
897
엑셀버전 : 구글시트

운영체제 : 윈도우10

질문 요약 : 중괄호, FILTER, QUERY, ROW, SUBTOTAL이 복합된 질문입니다.

안녕하세요! 회사에서 자동으로 지출결의서를 작성해주는 통합문서를 만들 소요가 생겨서 작업을 하는 중입니다.

일전에도 질문을 올려서 쿼리를 활용하라는 조언을 받았는데, 작업중에 다시 어려움이 생겨 도움을 구하게 됐습니다.

* 설명이 복잡해서 시트지를 첨부했습니다.

  1. 아래는 지출과 관련된 모든 데이터가 담긴 공통지출 데이터 시트입니다.(민감한 부분은 부득이하게 삭제했습니다)

2. 아래는 위의 공통지출 데이터 시트로부터 FILTER기능을 활용해 5월분만 추출한 FILTER 시트입니다.

*사용함수 : =FILTER('공통지출 Data'!B2:R,MONTH('공통지출 Data'!R2:R)=5)

3. 다음은 최종적으로 작성될 지출결의서 양식을 위해(이게 너무 답답합니다...뒤에 설명드릴게요) 어쩔수 없이 작성한 총계양식 시트입니다.

4. 위의 SUB 1,2 시트를 통합하면서 필터가 바뀔때마다 행간의 침범이 일어나지 않도록 중괄호를 사용해서 두 시트의 내용을 아래와 같이

합쳤습니다. (이미지에는 잘 표현이 안됐지만 두 데이터 사이에는 약 1000행정도의 공란이 존재합니다)(시트명:공통지출SUB3(SUB1+2))

5. 두 시트 내용사이의 공란도 없애고, 회사에서 요구하는 지출결의서 양식에 맞추기 위해서 아래와 같이 준비된 양식지 내에 QUERY 함수를

활용해서 두 시트의 내용을 다시 불러왔습니다. (시트이름은 공통지출결의서(QUERY))

여기서부터 막혔습니다.... 질문의 본론은 아래와 같습니다...

 

6. ① SUB1의 데이터는 잘 불러와 진 것 같습니다.

② 번은 SUB2의 데이터입니다. 지출양식이 아래와 같이 통제되어있다보니 "공란"이라는 단어가 입력되어있을시에는 회색을 셀을

칠하게끔 조건부 서식을 걸었습니다.

그런데, 이상하게 SUB2의 데이터는 최초 중괄호로 SUB1과 합칠때에는 정상적으로 불러와졌는데, 최종적으로 QUERY로 불러왔더니

군데군데 회색칠이 안되는 셀이 있습니다. 땜빵난곳중에 첫번째 '분류코드' 열과 '지출(예정)일' 열이 그렇습니다. 왜 "공란"이라고 옆의

셀들과 동일하게 입력을 해줬고, 수식도 별도로 건게 없는데 유독 얘들만 저렇게 나오는 걸까요?

③ 땜빵난 곳들중에 이곳은 일부러 "공란"이라는 단어를 입력하지 않은 곳입니다. 여기서 그때그때 필터마다 변화하는 금액들의 합계를

구하고싶은데, 필터마다 행 수도 달라 어느 셀에 어떤 함수를 걸어야 할지 막막합니다(SUBTOTAL을 활용해야 할것 같다는 막연한

생각만 있습니다...

④ '구분'열은 현재 비워져 있습니다. 행이 있을경우에만 ROW 함수를 이용해서 행번호를 매기고 싶은데, 문제는 이 행들이 모두 필터와

쿼리를 이용해 불러오는 행들이다보니 위 3번과 마찬가지로 어느셀에 어떤 함수를 걸어야 할지 막막하네요...

※일전에 총계 행을 위로 올리는것이 최선이라는 답변을 들었는데 회사 통제양식이 변화될 기미가 보이지 않네요....

실력이 부족하다보니 이렇게 장황하게 질문을 드렸습니다.. 고수님들 답변 부탁드립니다 ㅜㅜ

신고
스크랩
공유
전체 2

  • 2023-05-29 07:56
    채택된 답변

    @조성원 님 민감한 부분이 있어 해당 파일을 못 올리는건 이해를 합니다.

    하지만 정확한 답변을 위해서는 간단하게나마 비슷하게 만들어서 파일을 올리는게 답변에 도움을 드릴 수 있습니다

    해당 질문을 답변 못 해드리는 이유에는

    해당 파일의 수식이 어떻게 해서 저 값이 나오게 되는지를 눈으로 파악할 수 없기때문입니다.

    해당 사진에 무슨열이 A, B 열인지 잘 모르겠는것도 큽니다.

    만약 조건부 서식이 잘 작동하지 않는다면

    해당 수식을 직접 셀에다가 기입해보고

    ALT + M + V를 통해 왜 수식이 이상한 값이 나오는지 파악해보시는게 좋을 것 같습니다.

    또한 수식이 왜 제대로 작동 안하는지 모르겠을때는 해당시트를 복붙해서 예제시트를 만든 후 수식범위를 7개 정도로 좁히고 테스트 케이스를 여러개 넣어보며 ALT + M + V로 해당수식의 작동 방식을 파악해 고쳐나가는것도 중요합니다.


  • 2023-05-27 23:32

    @조성원 님 이건 답변아니구요.  제가 살면서 이렇게 엄청나게 긴 장문의 질문은 첨 보내요


전체 16,942
번호 카테고리 제목 작성자 작성일 추천 조회
알림
[📚진짜쓰는 실무엑셀] 2024년 엑셀 공부, 이 책 한권으로 마스터하세요! (291)
오빠두엑셀 | 2022.02.03 | 추천 554 | 조회 846838
오빠두엑셀 2022.02.03 554 846838
공지사항 문서서식
⭐ [더 나은 커뮤니티 문화를 위한 Q&A 글 작성 규칙] ⭐ (197)
오빠두엑셀 | 2021.10.28 | 추천 304 | 조회 31528
오빠두엑셀 2021.10.28 304 31528
74639 함수/공식
New 데이터유효성 검사를 통해 40배수의 수만 입력 (8)
쿠노이 | 00:21 | 추천 0 | 조회 31
쿠노이 00:21 - 31
74638 피벗테이블
New 피벗테이블 날짜 그룹화 원치 않는 범위가 뜹니다. (1)
단팥빵 | 2024.10.31 | 추천 0 | 조회 28
단팥빵 2024.10.31 - 28
74633 문서서식
New 안녕하세요 게임 시스템 테이블 작성 배우고 있는데 기초적인 부분을 잘 모르겠어서 질문 드립니다! 첨부파일 (1) 답변완료
코니 | 2024.10.31 | 추천 0 | 조회 48
코니 2024.10.31 - 48
74632 기능/도구
New 챗gpt 연동해서 이미지로 된 표의 데이터를 가져오려고 합니다.
김수현 | 2024.10.31 | 추천 0 | 조회 49
김수현 2024.10.31 - 49
74628 구글시트
New IMPORTRANGE 함수에서 칸을 늘리면 사라지는데 고정하는 방법은 없을까요? (3)
야망이 | 2024.10.31 | 추천 0 | 조회 55
야망이 2024.10.31 - 55
74627 문서서식
New 조건부 서식 문의 엑셀파일첨부파일 (1)
배고픈짐승 | 2024.10.31 | 추천 0 | 조회 74
배고픈짐승 2024.10.31 - 74
74626 파워쿼리/피벗
New [파워쿼리] 여러 파일 한번에 불러올 때 데이터 유실 첨부파일 (3)
naa**** | 2024.10.31 | 추천 0 | 조회 64
naa**** 2024.10.31 - 64
74625 VBA
New 시트이동시 유저폼 실행방법 문의 (2) 답변완료
땡이 | 2024.10.31 | 추천 0 | 조회 63
땡이 2024.10.31 - 63
74621 기능/도구
New 4개의 열 에서 같은 이름 가로로 정렬 (3)
Bukonge | 2024.10.31 | 추천 0 | 조회 62
Bukonge 2024.10.31 - 62
74618 VBA
New 유저폼에 바코드스캔시 입력속도 개선방법? (5) 답변완료
땡이 | 2024.10.31 | 추천 0 | 조회 58
땡이 2024.10.31 - 58
74616 함수/공식
New Rawdata 첨부파일 (4)
HIHUGO | 2024.10.31 | 추천 0 | 조회 68
HIHUGO 2024.10.31 - 68
74604 문서서식
New 간트차트 조건부서식 이용 문의 엑셀파일첨부파일 (3)
배고픈짐승 | 2024.10.30 | 추천 0 | 조회 81
배고픈짐승 2024.10.30 - 81
74599 함수/공식
New 이런 경우에는 무슨 함수를 써야할까요..? (2)
iwnqn | 2024.10.30 | 추천 0 | 조회 101
iwnqn 2024.10.30 - 101
74598 문서서식
New 엑셀 유효성 검사 뛰어 쓰기 문제 엑셀파일 (2) 답변완료
퉁퉁이 | 2024.10.30 | 추천 0 | 조회 75
퉁퉁이 2024.10.30 - 75
74592 함수/공식
New 여러 값들 중에서 원하는 값을 찾고 싶어요 도와주세요! 첨부파일 (9) 답변완료
저도해볼게요 | 2024.10.30 | 추천 0 | 조회 100
저도해볼게요 2024.10.30 - 100
74589 구글시트
New 한셀에 줄바꿈으로 있는 여러데이터를 한셀에 한데이터 들어가게끔 일괄로 하고 싶습니다 첨부파일 (4) 답변완료
스톤스톤 | 2024.10.30 | 추천 0 | 조회 60
스톤스톤 2024.10.30 - 60
74585 VBA
New UserForm 크기질문 첨부파일 (5)
땡이 | 2024.10.30 | 추천 0 | 조회 76
땡이 2024.10.30 - 76
74583 함수/공식
New XLOOKUP 함수의 다중조건을 잘 모르겠습니다 첨부파일 (6)
qlcc**** | 2024.10.30 | 추천 0 | 조회 92
qlcc**** 2024.10.30 - 92
74582 함수/공식
New filter 함수에 대해 문의드립니다 (5)
김은상 | 2024.10.30 | 추천 0 | 조회 73
김은상 2024.10.30 - 73
74581 구글시트
New 구글폼 견적서 자동화 관련, 폼에서 입력된 이미지 파일을 견적서에 넣는 방법 좀 알려주시면 감사하겠습니다.
| 2024.10.30 | 추천 0 | 조회 59
2024.10.30 - 59
74572 차트/그래프
New 꺽은선 그래프의 레이블이 원복이 됩니다. 첨부파일 (3)
구리구리이 | 2024.10.30 | 추천 0 | 조회 65
구리구리이 2024.10.30 - 65
74570 함수/공식
New 배수 금액으로 순번 정하기 알려 주세요...ㅠ 엑셀파일첨부파일 (3) 답변완료
서기 | 2024.10.30 | 추천 0 | 조회 62
서기 2024.10.30 - 62
74566 함수/공식
New 두개의 열에 입력된 데이터를 순서 바꿔서 입력으로 중복일 때 골라내는 법 엑셀파일첨부파일 (1) 답변완료
문과적남자 | 2024.10.30 | 추천 0 | 조회 63
문과적남자 2024.10.30 - 63
74564 함수/공식
New 각 열별로 합 구하는 방법 첨부파일 (2) 답변완료
라핼 | 2024.10.30 | 추천 0 | 조회 80
라핼 2024.10.30 - 80
74563 함수/공식
New 필터 적용시 중복값 제외 카운트 되는 법 좀 알려주세요 ㅠㅠ 첨부파일 (1)
정재욱 | 2024.10.30 | 추천 0 | 조회 78
정재욱 2024.10.30 - 78
74558 함수/공식
New 문자와 숫자가 섞여있는 열의 합계와 평균을 구하는 방법을 알려주세요!! 첨부파일 (9)
침착해 | 2024.10.30 | 추천 0 | 조회 83
침착해 2024.10.30 - 83
74556 함수/공식
New 세트상품 마진율 구하기 (2)
ek02 | 2024.10.30 | 추천 0 | 조회 70
ek02 2024.10.30 - 70
74543 기능/도구
New 문자와 숫자 혼합된 셀에서 숫자만 따로 따올수 있는 기능이 있을까요? 첨부파일 (4)
야만용사 | 2024.10.29 | 추천 0 | 조회 105
야만용사 2024.10.29 - 105
74542 기능/도구
New 엑셀 백분율 숫자로변경 (3)
rarara | 2024.10.29 | 추천 0 | 조회 96
rarara 2024.10.29 - 96
74540 함수/공식
New ★데이터 가공★ 잘못된 데이터를 올바른 데이터로 변환하는 과정을 도와주세요 ㅠㅠ 엑셀파일첨부파일 (11)
| 2024.10.29 | 추천 0 | 조회 137
2024.10.29 - 137