Ошибки в формулах: виды и способы устранения

5) Ошибка ССЫЛКА!
Данная ошибка возникает в случае, когда ячейка или диапазон, на который ссылается формула, был удален, перемещен или стал недоступным.

Чтобы исправить ошибку, нужно отменить удаление ячейки или заново сослаться на нужный диапазон. Другой вариант — использовать функцию ДВССЫЛ для тех ячеек, которые могут быть удалены.

Еще один способ возникновения - файлы, на которые есть ссылки были перемещены, удалены или переименованы.

6) Ошибка ЗНАЧ!
Возникает чаще всего тогда, когда в формуле использован неверный тип данных. Помните, что текст, число или дата - разные типы данных и обрабатываются по разному. Для исправления проверьте соответствуют ли все аргументы требуемым типам данных, если нет - укажите правильные типы.

👉 @Excel_lifehack

А как вы сцепляете дату и текст в одной ячейке Excel?

👉 @Excel_lifehack

В новых Excel старый "Общий доступ" заменен на совместное редактирование файлов на OneDrive. Если нужен старый инструмент, то его можно найти в Командах не на ленте

👉 @Excel_lifehack

У сводных таблиц есть удобная опция фиксации ширины столбца при обновлении. А вот с высотой строки дело обстоит иначе. Иногда случается так, что нужно задать строкам сводной высоту больше, чем стандартная. Но при работе со срезами и обновлениями эта высота снова слетает к стандартной.

В некоторых случаях помогает выделение целиком нужных строк и отключении опции "Переносить текст" на Главной. А в некоторых можно использовать другой приём.

Выделяем в любом пустом столбце строки, высоту которых надо фиксировать, и меняем размер шрифта на тот, который даст нужную высоту. При этом можно даже не вводить никакой текст в эти ячейки. Изменение размера шрифта заставит Excel всегда держать высоту строк на определенном уровне (в т.ч. при обновлении сводной и работе со срезами).

👉 @Excel_lifehack

Как удалить все числа из таблицы?

👉 @Excel_lifehack

Ошибки в формулах: виды и способы устранения

7) Ошибка ПУСТО!
Крайне редкая ошибка, так как мало кто использует в работе оператор пересечения диапазонов. Возникает тогда, когда диапазоны не пересекаются.

Для исправления укажите пересекающиеся диапазоны — например, формула: ={A1:B5 A6:B10} выдаст ошибку.
А формула: ={A1:B5 A5:B10} будет работать безошибочно и вернет диапазон A5:B5.

8) Ошибка ЧИСЛО!
Еще одна не самая распространенная ошибка. Встречается, если задан недопустимый числовой аргумент, то есть тип данных указан верно (поэтому не ЗНАЧ!), но само число выбрано недопустимое.

Чаще всего встречается в финансовых функциях. Например, формула: =ПЛТ(-40;2;2) выдаст эту ошибку, так как аргумент Ставка не может быть отрицательным.
Для исправления введите допустимый числовой аргумент.

👉 @Excel_lifehack

Самый быстрый фильтр в сводных таблицах excel

👉 @Excel_lifehack

Очень крутая функция ТЕКСТРАЗД

Я вот в этом посте уже показывал, как можно применить функцию ТЕКСТРАЗД (TEXTSPLIT). Но сейчас хочу показать ее мощь в нескольких примерах. Оказалось, что возможностей разделения и представления текста у этой функции гораздо больше, чем кажется на первый взгляд.

Рассмотрим три варианта, как можно использовать функцию:

1️⃣ Разделение по строкам, используя один разделитель, например точку
=ТЕКСТРАЗД(A1;",")

2️⃣ Разделение по нескольким разделителям, используя при этом внутри функции массив
=ТЕКСТРАЗД(A2;{",";";";":"})

3️⃣ Разделение по столбцам, используя, опять же, разделитель
=ТЕКСТРАЗД(A3;;", ")

👉 @Excel_lifehack

Оси диаграммы обычно расположены снизу и слева. Для перемещения оси значений направо, или оси категорий - вверх, измените настройки формата оси. Укажите, где одна ось пересекает другую

👉 @Excel_lifehack

Популярный инструмент "Текст по столбцам" предоставляет много интересных и полезных возможностей. Одна из них - пропуск указанных столбцов при формировании итоговой таблицы.

Запускаете инструмент, указываете разделитель, а на последнем шаге - выделяете те столбцы, которые не должны остаться после разбора, и включаете для них опцию "Пропустить столбец". В итоге после нажатия "Готово" из всех "слипшихся" данных останутся только те столбцы, которые не были отмечены как ненужные. Остальные будут удалены.

👉 @Excel_lifehack

Как уменьшить размер файла Excel и заставить его работать быстрее

1) Уменьшаем размер используемого диапазона листа

Наиболее часто проблема увеличения размера книги и ее торможения бывает связана с разросшимся используемым диапазоном листа. Если вы используете на листе всего несколько ячеек, но когда-то там была таблица в 1000 строк, то даже после ее удаления Excel будет обрабатывать все эти строки, тем самым замедляя работу файла.

Чтобы проверить, есть ли на листе лишние пустые столбцы и строки следует нажать сочетание клавиш CTRL + END. Вы попадете в последнюю ячейку, которую использует программа. Если она находится явно за пределами ваших данных, то лишние строки и столбцы стоит удалить.

Для этого в столбце А встаем в ячейку ниже последней нужной нам строки и нажимаем CTRL + SHIFT + END — выделятся все лишние строки, которые нужно будет удалить, затем то же самое повторяем и для столбцов.

После проделывания всех этих операций обязательно сохраняем книгу.

👉 @Excel_lifehack

Если у Вас есть файл с какой-то таблице или формой, которую должен заполнять пользователь, далёкий от уверенной работы в Excel, то лучше предостеречься и защитить документ.

Для надежности можно запретить даже выделение тех ячеек, которые заблокированы. Пользователь сможет поставить курсор только туда, где ему разрешен ввод и редактирование.

Просто снимаем галочку в списке настроек при установке защиты на лист.

👉 @Excel_lifehack

Как уменьшить размер файла Excel и заставить его работать быстрее

2) Удаляем лишние объекты из книги

Часто при вставке данных в файл из сторонних программ вместе с ними вставляются лишние объекты (фигуры, картинки и прочее), которые не всегда можно заметить на листе.

Чтобы проверить, есть ли в файле лишние объекты, нужно найти на ленте команду Найти и выделить и выбрать Область выделения, после чего откроется список объектов листа.

Удалить все объекты можно выделив их и нажав клавишу Delete. Чтобы выделить все объекты, снова используем команду Найти и выделить, выбираем пункт Выделить группу ячеек и в открывшемся окне выбираем Объекты.

👉 @Excel_lifehack

Как посчитать возраст скрытой Функцией?

👉 @Excel_lifehack

В Excel есть много встроенных функций для финансовых расчетов. Например, размер платежа по кредиту при заданной ставке, сроке и размере займа подсчитает функция ПЛТ

Главный нюанс - если считаем месячную выплату, то и ставку надо перевести в месячную, и период тоже указать, как количество месяцев, а не лет.

👉 @Excel_lifehack

Как прибавить несколько месяцев к дате в Excel?

👉 @Excel_lifehack

Как уменьшить размер файла Excel и заставить его работать быстрее

3) Пересохраняем файл в другом формате

Если кто-то еще пользуется файлами в старом формате XLS, но уже сидит на более новой версии (Excel 2007 и новее), то есть смысл пересохранить файл в одном из новых форматов: XLSX, XLSM, XLSB.

Они более современны, лучше оптимизированы, меньше весят и при этом быстрее работают. Самый компактный из них — XSLB. При сохранении в этом формате размер файла существенно уменьшится (даже если пересохранить в него новый XLSX).

👉 @Excel_lifehack

Знаем, сколько платить. Знаем срок и ставку. Посчитать сумму займа может функция ПС.

При этом денежные потоки имеют разные знаки. То, что мы отдаем - отрицательное число. А то, что получаем (например, сумма кредита) - положительное. Ну или наоборот.

👉 @Excel_lifehack

Вот как правильно объединять ячейки в Excel!

👉 @Excel_lifehack

Как уменьшить размер файла Excel и заставить его работать быстрее

4) Уменьшаем размер сводных таблиц

Если в вашем файле есть сводные таблицы, то имеет смысл отключить хранение данных для них вместе с файлом. Это потребует обновления таблицы каждый раз при открытии книги, но уменьшит при этом размер файла.

Для отключения этой опции выберите нужную сводную таблицу, перейдите в ее Параметры и в появившемся окне на вкладке Данные снимите галочку с пункта Сохранять исходные данные вместе с файлом.

👉 @Excel_lifehack