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

问题描述

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

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

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

B列>信用卡对账单的原始描述F栏＞要查找的关键字G列>新描述

问题是我使用了一个 IF 搜索 公式，该公式必须与该列表的大小一样长.它有效，但我想知道它是否不能简化.另一个问题"是我不能使用数组公式，所以将公式放在电子表格的顶部，它会扩展到整个语句.

这是一个例子:

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.

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

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.

Here's an example: https://docs.google.com/spreadsheets/d/14255Sz28ItSMZ32EebIFiAHQFxtyCJakN5q3gYUB-N0/edit#gid=0

ANY ideas?

解决方案

try:

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

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