如何使用下拉菜单中的数据和其他两个条件填充表格? [英] How to use data from drop down menus and two other criteria to populate a table?

问题描述

我正在尝试建立一个电子表格，以总结我在四个银行帐户之间转账的金额.在随附的电子表格中，我将手动在A，B，C和D列中的不同帐户(即，Everyday，Holiday，Emergency，Investments)之间进行转帐.在A列中，双击一个单元格添加日期，而B和C列使用下拉框选择帐户.

I am trying to set up a spreadsheet to summarise the amounts of money I transfer between four bank accounts. In the attached spreadsheet, I will manually put in the transfers I make between the different accounts (i.e., Everyday, Holiday, Emergency, Investments) in columns A, B, C, and D. In column A, you double-click a cell to add in a date, while columns B and C use a drop down box to choose accounts.

我想做的是在右侧的表格中填充这四列中的信息.

What I would like to do is have the information in these four columns populate the table on the right.

我将B列和C列分别命名为"OUT"和"IN".例如，在我的工作表中，第二行表示在4月8日，我从日常"帐户中取出$ 500，并将其放入假日"帐户中.

I've named columns B and C as "OUT" and "IN", respectively. For example, in my sheet, the second row means that on the 8th April, I'd taken $500 OUT of the Everyday account and put it IN the Holiday account.

因此，对于右边的表格，我希望它为我提供我每个特定月份在每个特定帐户中转入和转出的所有资金的总计.您知道如果可能的话我可以使用哪些公式?

So for the table on the right, I'd like for it to give me the totals for all the money I've transferred IN and OUT for each specific account for each specific month. Do you know what formulae I could use to do this if it's possible?

我的电子表格示例如下: https://docs.google.com/spreadsheets/d/1azNw6m7f3x7WhwEP9WXolCzfTE9dccj9wjs0qPwD-FY/edit#gid=2056599600

An example of my spreadsheet is here: https://docs.google.com/spreadsheets/d/1azNw6m7f3x7WhwEP9WXolCzfTE9dccj9wjs0qPwD-FY/edit#gid=2056599600

我尝试使用VLOOKUP和SUMIFS.

推荐答案

将此粘贴​​到 H3 单元格中，然后向右拖动-然后向下拖动:

paste this into H3 cell and drag to the right - then drag down:

=IFERROR(SUM(QUERY({$A:$D}, 
 "select Col4 
  where Col"&IFS(H$2="OUT", 2, H$2="IN", 3)&"='"&IF(H$1="", G$1, H$1)&"' 
    and month(Col1)+1="&MONTH($G3&1), 0)))

，然后将总计行粘贴到 H15 单元格中，然后向右拖动:

and for totals row paste this in H15 cell and drag to the right:

=SUM(H3:H14)

演示电子表格

demo spreadsheet

这篇关于如何使用下拉菜单中的数据和其他两个条件填充表格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！