ReSQL.ru
     
powered by simpleCommunicator - 2.0.48     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Программирование [игнор отключен] [закрыт для гостей] / Вопрос по формулам Google Sheets
8 сообщений из 8, страница 1 из 1
Вопрос по формулам Google Sheets
    #40083748
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Есть онлайн-таблица в Google Sheets, с объединенными ячейками.
Пример таблицы в аттаче.
На другом листе я ввожу имя устройства и порт подключения и мне нужно получить описание данного порта.
Проблема в том, что для объединенных ячеек значение возвращается только для верхнего левого адреса, все остальные ячейки объединенного диапазона пустые. Например на скриншоте есть объединенная ячейка H144:H146 из трех ячеек. Но значение будет только для H144, в H155 будет пусто.

Частично задачу я решил, но не до конца.
Допустим, в ячейке O указано имя устройства (CORE-BACKBONE-X670), в ячейке P указан номер порта (18).

Вначале я нахожу устройство:
=match(O;'Схема'!$C:$C;0) = 127

Поскольку имя устройства — это объединенная ячейка, я нахожу ее "высоту" следующим образом:
=match("*";indirect("'Схема'!$C"&(match(O;'Схема'!$C:$C;0)+1)&":$C");0) = 48

Затем я определяю диапазон ячеек, в котором буду искать порт:
="G"&match(O;'Схема'!$C:$C;0)&":G"&(match(O;'Схема'!$C:$C;0)+match("*";indirect("'Схема'!$C"&(match(O;'Схема'!$C:$C;0)+1)&":$C");0)-1) = "G127:G175

Чтобы не повторять всю эту многоэтажную формулу, сохраняю ее в ячейке R, только в виде "r127:r175".

Затем определяю смещение в диапазоне, по которому находится нужный порт:
=row(indirect("'Схема'!"&substitute(R;"r";"G")))+match(P;indirect("'Схема'!"&substitute(P;"r";"G")))-1 = 145

Нужный порт находится в ячейке G145, а нужное описание находится в ячейке H145.
Но H145 это часть объединенной ячейки H144:H146 и она возвращает пустое значение.
Как можно найти значение объединенной ячейки?

Можно попробовать сделать аналогично тому, как я определял "высоту" устройства, то есть искать * в обратном направлении.
Но во-первых, это крайне громоздко; в Google Sheets нет функции reverse, нужно использовать что-то вроде =sort(A1:A3;row(A1:A3);0).
А во-вторых, нет никаких гарантий что в первой ячейке объединенного диапазона задано описание. Оно может отсутствовать, и тогда я найду описание с чужого порта.

Самым лучшим было бы сделать так, чтобы все ячейки объединенного диапазона имели одно и то же значение. Это бы очень упростило любые формулы, да и это более логично.
Но ни в Microsoft Excel, ни в Google Sheets, за много лет такое не сделали, значит видимо и не сделают.

Еще бы помогло, если была бы функция, которая бы возвращала высоту/ширину объединенного диапазона.
Но такой функции среди функций листа похоже тоже нет.
В Excel в подобных случаях можно было использовать функции VBA (range.CurrentRegion.Count).
В Google Sheets тоже можно сделать что-то подобное (https://stackoverflow.com/questions/41967357/google-spreadsheet-cell-reference-to-merged-cell), но этого бы крайне не хотелось.
Нет ли способа обойтись формулами листа?
...
Рейтинг: 0 / 0
14.07.2021, 12:31
| Ответить | Цитировать | Написать  
Вопрос по формулам Google Sheets
    #40083799
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Сделал библиотекой формул:
Код: javascript
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
/**
 * Get reference for top-left cell in merged area.
 *
 * @param {string|reference} target Individual cell (A1-notation string or reference).
 * @return Top-left cell reference.
 * @customfunction
 */
function MERGED_REF(target)
{
  if (typeof target == "string") {
    try {
      var tmp = SpreadsheetApp.getActiveSheet().getRange(target);
    } catch(err) {
      if (target == "") {
        throw "Empty reference in A1-notation.";
      } else {
        throw "Invalid reference in A1-notation.";
      }
    }
    target = tmp;
  } else {
    try {
      var tmp = target.getA1Notation();
    } catch(e) {
      throw "Invalid cell reference.";
    }
  }
  if ((target.getNumRows() != 1) || (target.getNumColumns() != 1)) {
    throw "Invalid reference, ranges not allowed.";
  }
  if (target.isPartOfMerge && target.getMergedRanges().length) {
    target = target.getMergedRanges()[0].getCell(1,1);
  }
  return target;
}

/**
 * Get value for top-left cell in merged area.
 *
 * @param {string|reference} target Individual cell (A1-notation string or reference).
 * @return Top-left cell value.
 * @customfunction
 */
function MERGED_VALUE(target)
{
  target = MERGED_REF(target);
  if (typeof target == null) return;
  return target.getValue();
}

/**
 * Get merged area size (width).
 *
 * @param {string|reference} target Cell in merged area (A1-notation string or reference).
 * @return Merged area width.
 * @customfunction
 */
function MERGED_WIDTH(target)
{ 
  target = MERGED_REF(target);
  if (typeof target == null) return;
  if (target.isPartOfMerge && target.getMergedRanges().length) {
    return target.getMergedRanges()[0].getNumColumns();
  } else {
    return 1;
  }
}

/**
 * Get merged area size (height).
 *
 * @param {string|reference} target Cell in merged area (A1-notation string or reference).
 * @return Merged area height.
 * @customfunction
 */
function MERGED_HEIGHT(target)
{
  target = MERGED_REF(target);
  if (typeof target == null) return;
  if (target.isPartOfMerge && target.getMergedRanges().length) {
    return target.getMergedRanges()[0].getNumRows();
  } else {
    return 1;
  }
}



Но как я и опасался, работает это не очень хорошо.
Во-первых медленно, во-вторых глючно.
Может быть какой-нибудь MVP подскажет, что можно сделать?
...
Рейтинг: 0 / 0
14.07.2021, 15:27
| Ответить | Цитировать | Написать  
Вопрос по формулам Google Sheets
    #40083810
exp98
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
exp98
Участник
Alibek B.,
варианты, как можно в Эксэле:
1) спросить/пошукать в разделе Офис, у них большой опыт в иксэле
2) Если все описания заполнены
а) вспомогательный столб рядом и формулы вида '= --(H5<>"") 'и т.д. там, где 1 - это 1-я яч в объединении, если=0, то остальные в ней.
б) второй столб реализует как бы цикл по строкам, где проверяет клетки в новом столбе из п.2а на ==1, и если нет, то заполняет значением из ячейки выше.

3а) Узнать любым способом (напр. из п.2б)) высоту об. яч., выделить столько по соседству и заполнить их формулой-массивом вида "{=H5}".
...
Рейтинг: 0 / 0
14.07.2021, 15:57
| Ответить | Цитировать | Написать  
Вопрос по формулам Google Sheets
    #40083817
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Промежуточные столбцы я стараюсь использовать как можно меньше, потому что таблица не фиксированная, в ней несколько сотен строк с группировкой и время от времени они добавляются. Столбцы с формулами неизбежны, но чем их меньше, тем лучше, поэтому у меня столь громоздкие формулы и всего один вспомогательный столбец.

По поводу предложенной идеи (2а, 2б) — в том то и дело, что описания могут отсутствовать.
Предложенная проверка '= --(H5<>"") ' будет работать ошибочно, потому что ячейка будет пустой как для объединенной ячейки, так и просто для незаполненной.

Вариант с "=match("*"; sort(A1:A3;row(A1:A3);0); 0)" рабочий, но крайне громоздкий (потому что вместо A1 и A3 будут многоэтажные формулы, либо потребуется много промежуточных столбцов) и не очень быстрый из-за множества сортировок. Когда на листе будет несколько сотен таких формул, лист ощутимо будет тормозить.

Вариант с самописной функцией (MERGED_VALUE или MERGED_HEIGHT) работает правильно, но почему-то он очень медленный. Вдобавок, по какой-то причине значение в вычисляемой ячейке не всегда обновляется автоматически и нужно обновлять весь лист.
...
Рейтинг: 0 / 0
14.07.2021, 16:19
| Ответить | Цитировать | Написать  
Вопрос по формулам Google Sheets
    #40083857
exp98
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
exp98
Участник
Я так и предполагал, хотя непонятно, почему несколько сотен строк - это много. Только потому, что долго работает?

А такой вариант из эксэла? Программно (макросом) имитировать смещение курсора вниз по "стрелка ВНИЗ"
При этом, если включена нумерация строк, то слева на шкале выделены будут только нужные номера, а курсор тоже выделит только нужные яч. Даже если подряд несколько пустых, и даже если среди них неск. об. пустых.
Вот только сам не знаю как подсчитать выделенное число строк.
Но это в эксэле.
...
Рейтинг: 0 / 0
14.07.2021, 17:48
| Ответить | Цитировать | Написать  
Вопрос по формулам Google Sheets
    #40083908
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Нужен удобный совместный доступ (на просмотр), поэтому и Google Sheets, а не MS Excel.
Имитировать управление курсором с помощью API конечно можно. Но функции листа не должны взаимодействовать с листом или ячейками, то есть это будет именно макрос (который нужно запускать вручную или по событию), а не функция.

Я надеялся, что кто-то знает и подскажет скрытую функцию Google Sheets, которая бы возвращала размер или содержание объединенной области. Скрытые функции в Google Sheets есть (они начинаются с символа подчеркивания), но они не документированы, по ним нет описания или подсказок.
...
Рейтинг: 0 / 0
14.07.2021, 21:21
| Ответить | Цитировать | Написать  
Вопрос по формулам Google Sheets
    #40083910
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Кстати, один в один моя проблема:
https://support.google.com/docs/thread/6668184/

Только закрыто без объяснений.
...
Рейтинг: 0 / 0
14.07.2021, 21:34
| Ответить | Цитировать | Написать  
Вопрос по формулам Google Sheets
    #40083915
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Alibek B.
Во-первых медленно, во-вторых глючно.

Кстати, с этим разобрался.
В Google Sheets есть два типа функций. Первый тип функций (функции листа) не взаимодействует с листом и ячейками, эти функции работают быстро. Второй тип функций взаимодействует с листом и ячейками, эти функции не предназначены использоваться на листе.
А поскольку в моей пользовательской функции MERGED_REF я вовсю обращаюсь к методам API (getActiveSheet, getNumRows и т.п.), то Google классифицирует мою функцию как функцию второго типа, поэтому низкая скорость и глюки с автообновлением.
В этом отношении Excel намного функциональнее.
...
Рейтинг: 0 / 0
14.07.2021, 22:01
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / Программирование [игнор отключен] [закрыт для гостей] / Вопрос по формулам Google Sheets
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=16&msg=40083817&tid=1339649]:
 0ms
get settings:
 7ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 41ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 45ms
get tp. blocked users:
 2ms
others: 14ms
total:  138ms
созд. / загр.: 138ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]