• Проверьте, нет ли условного форматирования на (излишне) большом диапазоне ячеек.
• Не ставьте фильтр на все столбцы.
• Очистите примечания, если их много и они не нужны.
• Посмотрите, нет ли проверки данных на большом диапазоне ячеек.
Ренат: У нас в МИФе есть сводный файл со списком всех книг и большим количеством данных по ним, которые грузятся из разных источников. В какой-то момент некоторые коллеги перестали им пользоваться – ноутбуки перегревались, а файл иногда и не открывался:)
После оптимизации по большинству описанных пунктов он стал «летать».
Это работало и со многими другими документами.
В Excel, кстати, с помощью этих же правил бывали случаи уменьшения размера файла в разы, а несколько раз – даже на порядок (последнее, правда, случается только при пересохранении файла из старого формата XLS в новые XLSX, XLSM, XLSB).
Евгений: Иногда в документах приходится использовать ресурсоемкие формулы, которые ничем не заменить, например может потребоваться собирать в один файл данные из 20 разных документов формулой IMPORTRANGE. Если ничего не предпринять, то работа с таким документом может стать мучительной, формулы будут постоянно обновляться и все начнет тормозить.
В таких случаях я предлагаю следующее решение: написать скрипт, который будет вставлять формулы в требуемые ячейки, а потом сразу же заменять их на значения. Такой скрипт можно запускать как вручную, так и по расписанию, скажем, каждые два часа, и в этом случае необязательно находиться в файле – скрипт отработает в офлайн-режиме.
Работа с формулами и диапазонами
НЕСКОЛЬКО БАЗОВЫХ ПРАВИЛ
• Любая формула, как и в Excel, вводится со знака «равно».
• Текст указывается в кавычках, после названий листов ставится восклицательный знак, названия листов берутся в апострофы, если в них есть пробелы (‘Название листа’!A1).
• Аргументы функций разделяются символом (каким именно – зависит от региональных настроек), для России это точка с запятой.
• Если у вас в настройках выбран английский язык, то вы все равно можете вводить привычные русские названия, но всплывающих подсказок в этом случае не будет. После ввода русские названия будут автоматически заменены на английские.
РАЗБИРАЕМ НА ПРИМЕРЕ СУММЕСЛИ (SUMIF), КАК ЗАДАТЬ (ВЫБРАТЬ) В ФОРМУЛЕ ДИАПАЗОНЫ И УСЛОВИЯ
Эта глава будет полезна тем, у кого совсем мало опыта в написании формул. В ней я по шагам расскажу, как начать формулу; как выбрать диапазон; что делать, если он на другом листе; что нажать, чтобы перейти от выбора диапазона к условию; как сделать ссылки абсолютными и не потратить на все это слишком много нервов.
Возьмем простую формулу СУММЕСЛИ (SUMIF).
По ссылке https://goo.gl/1dIZMI вы найдете Google Документ с примером, на котором можно потренироваться. Для редактирования выберите:
Файл → Создать копию.
Начнем.
Чтобы немного усложнить себе задачу, вводить формулу мы будем на одном листе, а диапазон суммирования и диапазон условия – на другом (оба листа должны находиться в одном документе – в отличие от Excel, где можно ссылаться и на другие книги).
Формулы всегда начинаются со знака «равно».
Итак, выделяем ячейку В2 и начинаем вводить формулу. Уже после нескольких символов =СУ появляются варианты формул с этим слогом в названии, выбираем мышкой СУММЕСЛИ и кликаем на нее:
Видим вот такое окно (формулу можно писать как в самой ячейке, так и в строке формул – это не принципиально):
Под формулой видим окно справки. В нем цветом подсвечивается тот элемент, который нужно ввести сейчас. У нас это диапазон условия (если справка не открылась, нажмите на? под формулой).
Выбираем лист «Диапазоны» и выделяем диапазон условия – для этого кликаем на его первой ячейке и «протягиваем» до последней (в данном примере это С1:C7). Выделять ячейки можно и в обратном порядке: начать с С7 и протянуть до С1; или можно кликнуть на названии столбца С, и он выберется целиком.
Если вам мешает справка формулы, то закройте ее, нажав на крестик. Если все равно что-то мешает и никак не получается выбрать нужный диапазон, как B1:B7 на скриншоте ниже, – его можно ввести с помощью клавиатуры, прямо в строке формул (не забывайте, что буквы в ссылках Таблиц латинские).
Диапазон выбран, но по умолчанию он будет относительным, то есть при копировании формулы сместится вслед за ней. Чтобы этого избежать, нажмите на клавиатуре F4. Теперь в адресе появились символы $, и он стал абсолютным – это означает, что и строки, и столбцы зафиксированы, ничего сдвигаться не будет.
(Если нажать F4 еще раз, то зафиксируются только строки, при повторном нажатии – только столбцы.)
Мы выбрали диапазон условия. Вводим точку с запятой (;), этот символ отделяет аргументы формулы друг от друга. Теперь нужно выбрать ячейку с условием.
Кликнем на вкладку Сводный отчет и на ячейку А2. Не будем делать условие абсолютным, так как планируем скопировать формулу на ячейку ниже и нам нужно, чтобы условие с А2 поменялось на А3.
Вводим точку с запятой, возвращаемся на вкладку Диапазоны и выбираем последний диапазон суммирования. Его тоже сделаем абсолютным.
Важно: переходите на другую вкладку только после точки с запятой, иначе при переходе у вас собьется предыдущий аргумент.
Формула готова, теперь скопируем ее из ячейки B2 в ячейку В3 (можно через пункты меню Копировать и Вставить, можно кликнуть на В2 и протянуть на ячейку ниже, можно использовать сочетания клавиш Ctrl + C, Ctrl + V):
АБСОЛЮТНЫЕ, ОТНОСИТЕЛЬНЫЕ И СМЕШАННЫЕ ССЫЛКИ. ФУНКЦИЯ ДВССЫЛ (INDIRECT)
Ссылки в Таблицах, как и в Excel, могут быть абсолютными и относительными.
Ровно так же они изменяются с помощью клавиши F4 или путем ввода знака доллара перед номером строки и/или перед номером столбца.
Читать этот подраздел дальше имеет смысл, если вы не работали с абсолютными и относительными ссылками в Excel или хотите повторить этот материал.
Относительные ссылки – это обычные ссылки вида A1, D10, AX127, которые по умолчанию появляются при щелчке на ячейку во время ввода формул. Такие ссылки смещаются вместе с формулой. Они являются относительными, так как ссылаются не на конкретную ячейку, а на ячейку, отстоящую на N строк и M столбцов от той, в которую введены. Так, если вы ввели в ячейку B1 формулу
=A1
то вы ссылаетесь не на A1, а на ячейку слева от ячейки с формулой, или отстоящую от нее на -1 (минус один) столбец и 0 (ноль) строк.
Поэтому при копировании этой формулы в ячейку C4 она будет выглядеть следующим образом:
=B4
то есть по-прежнему ссылаться на ячейку слева от себя.
Это не всегда подходит для ваших задач.
Так, если вам нужно перемножить числа из многих строк на одну ячейку со ставкой налога, то сразу протянуть формулу не получится:
Уже во второй строке формула ссылается не на нужную ячейку, а на пустую ячейку под ней.
Для таких случаев используются абсолютные ссылки. Абсолютная ссылка – это ссылка вида $A$1 или $B$5, которая не изменяется при перемещении формулы:
Ссылку можно сделать абсолютной, нажав F4 при вводе формулы (или щелкнув курсором на ссылку в строке формул). Последовательно нажимая F4, вы будете перебирать все 4 возможные комбинации:
A1 → $A$1 → A$1 → $A1 → A1.
A$1 и $A1 – это смешанные ссылки, в которых закреплена только строка или только столбец. Например, для составления обычной таблицы умножения требуются именно такие ссылки в формуле, ведь нам нужно перемножать числа из первой строки (фиксируем ее) и первого столбца (фиксируем столбец A):
На скриншоте ниже видно, как меняются адреса разных типов, если мы вводим их в столбец А и копируем в столбцы С, D и Е:
Абсолютная ссылка тоже может изменяться. Например, если вы добавите строку в примере выше, ссылка по-прежнему укажет на ячейку с 20 %, но это будет уже не $E$1, а $E$2: