=QUERY({
QUERY('Лист6'!A:G;
"Select A, B, sum(F)
Where (C contains 'Фрукты' OR C contains 'Fruits')
Group by B, A
LABEL A'', B'', sum(F)''")
;
QUERY('Лист6'!A:G;
"Select F, '', ''
Where (B = '"&B2&"')
AND (C contains 'Яблоки' OR C contains 'Apple')
LABEL F''")
};"select Col1 where Col1<>''";0)#!/usr/bin/python
# -*- coding: utf-8 -*-
page_token = None
while True:
response = \
drive_service.files().list(q="'1R6eT6NE19_9LshSK3FsIHMVY6KOkhrG2' in parents"
, spaces='drive',
fields='nextPageToken, files(id, name)'
, pageToken=page_token).execute()
for file in response.get('files', []):
# Process change
print 'Found file: %s (%s)' % (file.get('name'), file.get('id'))
page_token = response.get('nextPageToken', None)
if page_token is None:
break
QUERY=QUERY(A1:B;"select A, count(B) where B='Аноним' group by A label count(B)'Анонимы'";1)
=INDEX(
QUERY(
{
'Заказы'!H3:I;
{'Заказы'!M3:M\-1*'Заказы'!N3:N}
};
"select Col1, sum(Col2)
where Col1<>'' and Col1<>'1899-12'
group by Col1
label Col1'Дата',sum(Col2)'Прибыль'"
)
)
-1*'Заказы'!N3:N.
range.function runTamotsuSample() {
Tamotsu.initialize();
const agent = Tamotsu.Table.define({
sheetName: 'Sheet1',
idColumn: 'id'
});
agent.create({
date: new Date(),
number: 100,
string: 'Hi there'
})
}
=FILTER(A:A;ISNA(MATCH(A:A;B:B;0)))
=ARRAYFORMULA(
REGEXREPLACE(
TRANSPOSE(QUERY(TRANSPOSE(D1:G&"|"),,COLUMNS(D1:G))),
"(\|\s*)+",
", " )
)
={"В строку";ARRAYFORMULA(REGEXREPLACE(
REGEXREPLACE(
TRIM(TRANSPOSE(QUERY(TRANSPOSE(D2:G&"|");;COLUMNS(D2:G))));
"(\|\s*)+";
", " );
"(^,\s*)|(,\s*$)";
""
))}
const goToWrapper_ = (address) => {
const range = getRangeByRefString_(SpreadsheetApp.getActiveSheet(), address);
range.getSheet().getRange('A1').activate();
range.activate();
}
function getRangeByRefString_(sheet, refString) {
return refString.indexOf('!') === -1 ?
sheet.getRange(refString) :
sheet.getParent().getRange(refString);
}
function goTo555() {
goToWrapper_('A555');
}
function goToSheet5A555() {
goToWrapper_('Sheet5!A555');
}
B1 формулу=INDEX('Ответы на форму!A:A')B2:B удалите!
VLOOKUPA3:A - первая таблица,C3:F - вторая таблица,IMPORTRANGE для переноса данных в общее место)=INDEX(IFERROR(VLOOKUP(
A3:A;
C3:F;
{1\2\3\4};
)))
=FILTER({C5:C20\A5:B20};C5:C20=1;B5:B20<>"")
/**
*
* @param {GoogleAppsScript.Events.SheetsOnEdit} e
*/
function onEdit(e) {
const allowRanges = [
{ sheetName: 'Заглавная буква', cells: ['B2', 'B3', 'B4'] },
{ sheetName: 'Стартовая страница', cells: ['B2', 'C2', 'D2'] },
]; // Список
const sheetNames = allowRanges.map(r => r.sheetName);
if (e && e.range) {
const sheet = e.range.getSheet();
if (
sheetNames.indexOf(sheet.getName()) > -1 &&
allowRanges.find(r => r.sheetName === sheet.getName()).cells.indexOf(e.range.getA1Notation()) > -1
) {
const value = '' + e.range.getValue();
e.range.setValue(value.slice(0, 1).toLocaleUpperCase() + value.slice(1).toLocaleLowerCase());
}
}
}
Не работает для группы ячеек
=REGEXEXTRACT("" & B8;"(\d\d)(\d\d)(\d\d)(\d\d)")
=IF(--C8>22;REGEXEXTRACT(C8;"(\d)(\d)"); C8)
=INDEX(SORTN(TRANSPOSE({
IF({B19:L19\B28:L28}="";"";--{B19:L19\B28:L28});
TRIM(QUERY(
IF("" & TRANSPOSE({B19:L19\B28:L28})="" & {B19:L19\B28:L28};IF({B19:L19\B28:L28}="";"";--{B19:L19\B28:L28}););
;
9^9))
});9^9;2;1;1);;2)