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

Что же делать? Самый простой способ распознать разницу – это выполнить равенство двух ячеек. Если они будут одинакового типа данных, то результат будет Истина, а если нет – Ложь. Если получен результат Ложь, то необходимо приводить к единому типу данных – или в обоих случаях текст или число. Многие, конечно же, пробуют прибегнуть к самому простому – изменению типа данных через Формат ячеек. Однако, это не решает проблемы.

Решение подобной проблемы возможно с использованием текстовых функций: ЗНАЧЕН и ТЕКСТ.

1-я ситуация: преобразуем текст в число.

Функция ЗНАЧЕН [VALUE] прекрасно в этим справляется. Эта функция имеет всего один аргумент – то текстовое значение, которое вы хотите преобразовать в число.

2-я ситуация: преобразуем число в текст.

Функция ТЕКСТ [TEXT]:

• Значение [Value] – числовое значение, которое нужно преобразовать в текстовое
• Формат [Format] – формат числа в тестовом формате, как в поле “числовые форматы” формата ячеек.

Итак, преобразовать текст в число =ЗНАЧЕН(Текст) – результат число, а преобразовать число в текст =ТЕКСТ(Число; “0”)

Использование функции ДВССЫЛ значительно ускоряет процесс получения данных как на одном, так и на разных листах. Это так удобно – написать одну формулу, задать в ней изменяющиеся или постоянные параметры, такие как название листа, адрес ячейки или диапазона, скопировать формулу и получить результаты!

Сперва многим кажется не привычным – “сочинять” адрес ячейки или диапазона, но вскоре этот барьер преодолевается. И одна из сложностей, с которой сталкиваются пользователи, – это наличие в названии листов пробелов, дефисов и т.д. Однако, и это не может служить проблемой, стоит только вспомнить/понять как Excel обозначает такие ссылки и проблема решена.

Адрес ячейки с другого листа выглядит в общем виде так: ИмяЛиста!Ячейка, если имя листа написано с какими-нибудь разделителями, то имя листа заключается в апострофы: ‘ИмяЛиста’!Ячейка.

Поэтому в функции ДВССЫЛ [INDIRECT] простая сцепка названия листа (значение ячейки A2) и ячейки C1 дает результат только там, где название города написано без каких-либо разделителей:

Т.к.  результат аргумента функции не будет содержать апострофы, а значит Excel не сможет понять это правильно. Отсюда и ошибка #ССЫЛКА!

Поэтому, чтобы исправить данную ошибку, следует принудительно заключить название листа в апострофы:

Таким образом, более универсальная формула для работы с именами листов будет:

=ДВССЫЛ(“‘”&A2&”‘!C1”) или =INDIRECT(“‘”&A2&”‘!C1”)

С помощью Проверки данных [Data Validation] можно организовать ввод данных путем выбора из предлагаемого списка, значения которого зависят от другого списка. Причем, используя функцию СМЕЩ [OFFSET] можно создать вариант, когда добавленные исходные значения будут отображаться в списках для выбора нужных значений. Если значения списка зависят от выбранного значения из другого списка, то можно создать связанный (зависимый) список нужных значений. Это позволит в значительной степени избежать не корректных комбинаций вводимых значений.

Например, в поле Европа происходит выбор одного из двух значений: Западная или Восточная, после этого в поле Страна предлагается список с соответствующими значениями.

Последовательность создания:

• Выделить ячейку F2, где будет выбираться Европа.
  На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation] и на вкладке Параметры [Option], задать Условие проверки [Validation criteria] – Список [List], в качестве источника выделить ячейки B2 и C2

• Ячейкам значений стран (данные в столбцах B и C) необходимо присвоить имена – Западная и Восточная, с возможностью автоматического определения диапазона ячеек по мере изменения количества значений в соответствующих столбцах:
  На вкладке Формулы [Formulas] выбрать Диспетчер имен [Name Manager] или нажать клавиши Ctrl+F3.
  Создать имена с использованием функции СМЕЩ:

• Выделить ячейку F3, где будет выбираться Страна.
  На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation] и на вкладке Параметры [Option], задать Условие проверки [Validation criteria] – Список [List], в качестве источника ввести формулу:
  =ЕСЛИ($F$2=”Западная”;Западная;Восточная)
  [=IF ($F$2=”Западная”;Западная;Восточная)], где F2 – ячейка, которая содержит значение, выбираемого из первого списка.

При добавлении новых данных, они будут сразу показаны в выпадающем списке:

Примечание: подразумевается, что элементы списка вводятся последовательно, т.е. элемент списка не содержит пустые ячейки.