Связывание двух значений ячеек в Google Sheets
Привет спасибо за помощь,
Я ищу способ связать 2 ячейки вместе в Google Sheets, где вывод одной из них будет зависеть от ввода другой.
Позвольте мне привести пример, у меня есть база данных столбцов с кодами и рядом с ними соответствующих имен, простой пример ниже.
DATABASE 1
CODE | NAME
-----------
146 | Aba
234 | Bac
365 | Cge
При заполнении заказа я хочу иметь возможность либо ввести код, и лист автоматически выполнит поиск имени и заполнит его, либо введет имя, и лист автоматически заполнит код.
пример:
Order 1
my input finished
CODE | NAME CODE | NAME
----------- -> PROCESS -> -----------
146 | 146 | Aba
| Cge 365 | Cge
Я хочу выяснить, каким может быть "процесс", чтобы получить желаемое решение. Я не очень хорош в кодировании и не знаю, с чего начать, но я подумал, что одним из решений может быть использование функции VLOOKUP для перехода вниз по каждой строке, проверки наличия значения в столбце CODE, затем VLOOOKUP соответствующее значение NAME из базы данных, если нет значения CODE, тогда посмотрите, есть ли значение NAME и VLOOKUP значение CODE. Затем перейдите к следующему ряду.
Спасибо, что нашли время, чтобы прочитать это, я ценю любую помощь.
1 ответ
Преобразование букв столбцов в цифры или цифры в буквы
Вы можете выполнять то, что вы описываете с этими функциями. Я имею ascii к символу и столбцу буквы к номерам столбцов примеров. Скопируйте все эти функции в Code.gs, а затем вам нужно перейти на пустую страницу и переименовать ее в "Контакты", а затем запустить функцию setupOnEdit(). Я решил использовать столбцы A и B. Способ настройки теперь, если вы печатаете столбцы электронной таблицы буквами, тогда номера столбцов появятся в соседнем столбце. Или, если вы введете цифры в столбцах, буквы появятся в другом столбце. Я думаю, вы просто хотели, чтобы каждая колонка шла в одну сторону. И это возможно. Я оставлю это как упражнение для читателя.
function getASCIIObject(){
var aObj={};
for(var i=32;i<127;i++){
var s=Utilities.formatString('%s',String.fromCharCode(i));
if(s!='='){
aObj[i]=s;
aObj[s]=i;
}
}
return aObj;
}
function getColumnsObject(){
var cObj={};
var chA=[];
var colA=[];
for(var i=65;i<=90;i++){chA.push(String.fromCharCode(i));}
for(var i=0;i<10;i++){
for(var j=0;j<26;j++){
if(i==0){
colA.push(chA[j]);
}
if(i>0){
colA.push(chA[i-1] + chA[j]);
}
}
}
for(var i=0;i<colA.length;i++){
cObj[i+1]=colA[i];
cObj[colA[i]]=i+1;
}
return cObj;
}
function dCode(key,mode){
if(key){
var mode=mode || 'ASCII';
if(mode=='ASCII'){
var dObj=getASCIIObject();
return dObj[key];
}
if(mode=='COLUMNS'){
var dObj=getColumnsObject();
return dObj[isNaN(key)?key.toUpperCase():key];
}
if(mode=='DICT'){
var dObj=getMyDictionary();
return dObj[key];
}
}
throw('Error; Invalid params in dCode()');
}
function setupEditTrigger(funcName) {
if(ScriptApp.getProjectTriggers().indexOf(funcName)==-1){
ScriptApp.newTrigger(funcName).forSpreadsheet(getGlobal('SSId')).onEdit().create();
}
}
function setupOnEdit(){
setupEditTrigger('decodeColumns');
}
function decodeColumns(e){
var rg=e.range;
var sh=rg.getSheet();
var col=rg.getColumn();
var row=rg.getRow();
var value=rg.getValue();
Logger.log('rg: %s sh: %s col: %s row: %s value: %s',rg.getA1Notation(),sh.getName(),col,row,value);
if(sh.getName()=='Contacts' && col==1){
rg.offset(0,+1).setValue(dCode(value,'DICT'));
}
if(sh.getName()=='Contacts' && col==2){
rg.offset(0,-1).setValue(dCode(value,'DICT'));
}
}
function getMyDictionary(sheetname,keycolumn){
var sheetname=sheetname ||'Dictionary';
var keycolumn=keycolumn || 1;
var valuecolumn=keycolumn + 1;
var dObj={};
if(sheetname && keycolumn){
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName(sheetname);
var rg=sh.getRange(1,keycolumn,sh.getLastRow(),2);
var codeA=rg.getValues();
for(var i=0;i<codeA.length;i++){
if(codeA[i][0]){
dObj[codeA[i][0]]=codeA[i][1];
}else{
break;
}
}
return dObj;
}else{
throw('Error: Invalid params in getMyDictionary()');
}
}
Вот вторая версия, которая дает вам две вкладки словаря для работы. Одним из них является словарь, а другой словарь2. Но, конечно, вы можете изменить их на что угодно.
function getASCIIObject(){
var aObj={};
for(var i=32;i<127;i++){
var s=Utilities.formatString('%s',String.fromCharCode(i));
if(s!='='){
aObj[i]=s;
aObj[s]=i;
}
}
return aObj;
}
function getColumnsObject(){
var cObj={};
var chA=[];
var colA=[];
for(var i=65;i<=90;i++){chA.push(String.fromCharCode(i));}
for(var i=0;i<10;i++){
for(var j=0;j<26;j++){
if(i==0){
colA.push(chA[j]);
}
if(i>0){
colA.push(chA[i-1] + chA[j]);
}
}
}
for(var i=0;i<colA.length;i++){
cObj[i+1]=colA[i];
cObj[colA[i]]=i+1;
}
return cObj;
}
function dCode(key,mode){
if(key){
var mode=mode || 'ASCII';
if(mode=='ASCII'){
var dObj=getASCIIObject();
if(dObj.hasOwnProperty(key)){
return dObj[key];
}
}
if(mode=='COLUMNS'){
var dObj=getColumnsObject();
key=isNaN(key)?key.toUpperCase():key;
if(dObj.hasOwnProperty(key)){
return dObj[key];
}
}
if(mode=='DICT1'){
var dObj=getMyDictionary();
if(dObj.hasOwnProperty(key)){
return dObj[key];
}
}
if(mode=='DICT2'){
var dObj=getMyDictionary('Dictionary2');
if(dObj.hasOwnProperty(key)){
return dObj[key];
}
}
}else{
throw('Error; Invalid params in dCode()');
}
return nill;
}
function setupEditTrigger(funcName) {
if(ScriptApp.getProjectTriggers().indexOf(funcName)==-1){
ScriptApp.newTrigger(funcName).forSpreadsheet(getGlobal('SSId')).onEdit().create();
}
}
function setupOnEdit(){
setupEditTrigger('decodeColumns');
}
function decodeColumns(e){
var rg=e.range;
var sh=rg.getSheet();
var col=rg.getColumn();
var row=rg.getRow();
var value=rg.getValue();
Logger.log('rg: %s sh: %s col: %s row: %s value: %s',rg.getA1Notation(),sh.getName(),col,row,value);
if(sh.getName()=='Contacts' && col==1){
rg.offset(0,+1).setValue(dCode(value,'DICT1'));
}
if(sh.getName()=='Contacts' && col==2){
rg.offset(0,-1).setValue(dCode(value,'DICT2'));
}
}
function getMyDictionary(sheetname,keycolumn){
var sheetname=sheetname ||'Dictionary';
var keycolumn=keycolumn || 1;
var valuecolumn=keycolumn + 1;
var dObj={};
if(sheetname && keycolumn){
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName(sheetname);
var rg=sh.getRange(1,keycolumn,sh.getLastRow(),2);
var codeA=rg.getValues();
for(var i=0;i<codeA.length;i++){
if(codeA[i][0]){
dObj[codeA[i][0]]=codeA[i][1];
}else{
break;
}
}
return dObj;
}else{
throw('Error: Invalid params in getMyDictionary()');
}
}