根据Google表格中的选项列表替换单元格的内容 [英] Replace Content of a cell based on a list of options in Google Sheets

问题描述

多年来，我一直在使用很长的公式来帮助我实现我在这里要问的问题.

For years I have been using a very long formula to help me achieve what I am going to ask here.

基本上，每个月我都会下载我的信用卡对帐单，并使用大量公式将其汇总，以更好的方式将其导入我的会计软件中.我的公式所做的是读取每一行的描述，然后根据该行的内容重写该描述.

Basically, every month I download my Credit Card statement and use a bunch of formulas to sum it up in a better way to import into my accounting software. What my formulas do is that it reads the Description in each row and based on the content of that row it rewrites that description.

例如:在我的信用卡中，它可能显示"Starbucks Main St."，因此它将在列表中查找该信息，如果找到"Starbucks"，它将进行重写(在公式所在的单元格中)到咖啡". 所以，我有:

For example: In my credit card it may say "Starbucks Main St.", so it looks for that information in a list and if it finds "Starbucks", it will rewrite (in the cell where the formula is located) to "Coffee". So, what I have is:

Column B > Original Description from Credit Card Statement
Column F > Keyword to be found
Column G > New description

问题是我使用的 IF搜索公式必须与该列表的大小一样长.它可以工作，但是我想知道是否不能简化它.另一个问题"是我不能使用arrayformula，因此将公式放在电子表格的顶部，它将扩展为整个语句.

The problem is that I use an IF Search formula that has to be as long as the size of that list. It works, but I wonder if it can't be simplified. Another "issue" is that I can't use an arrayformula, so putting the formula on top of the spreadsheet and it would expand to the whole statement.

这是一个例子: https://docs.google.com/spreadsheets/d/14255Sz28ItSMZ32EebIFiAHQFxtyCJakN5q3gYUB-N0/edit#gid = 0

有什么想法吗?

推荐答案

尝试:

=ARRAYFORMULA(PROPER(IFNA(VLOOKUP(REGEXEXTRACT(PROPER(B2:B); 
 SORT(TEXTJOIN("|"; 1; PROPER(G2:G)); 1; 0)); G2:H; 2; 0); B2:B)))

这篇关于根据Google表格中的选项列表替换单元格的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！