9 ошибок Excel, которые вас достали

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

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

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

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

Чаще всего не стоит беспокоиться по поводу регистра символов текста. Если же необходимо при сравнении учитывать регистр символов, можно использовать функцию СОВПАД. Приведенная ниже формула возвращает значение ИСТИНА только в том случае, если ячейки А1 и А2 содержат абсолютно идентичные записи.

Знач в Excel: как исправить, что значит ошибка и почему появляется –. Новости и советы

• Выделите ячейку, в которой вы хотите запустить формулу,
• Перейдите на вкладку «Формулы» в верхней части навигации.
• Выберите «Вставить функцию«. Если вы используете Microsoft Excel 2007, этот параметр будет находиться слева от панели навигации «Формулы».

Следующая формула укорачивает текст, который оказался слишком длинным. Если в ячейке А1 содержится более десяти символов, формула возвращает первые 9 символов, за которыми следует символ троеточия (в кодовой таблице ANSI этот символ имеет код 133). Если символов десять или меньше, возвращается вся строка.

Проверка данных в Excel

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

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

Чтобы сделать проверку данных в Excel для определенных ячеек, их необходимо выделить, перейти на вкладку «Данные», и найти в разделе «Работа с данными» меню «Проверка данных». При нажатии на стрелочку справа от данного пункта появляется три пункта меню, из которых нам нужен самый первый «Проверка данных…».

В появившемся окошке «Проверка вводимых значений» настраивается проверка. На вкладке «Параметры» выбирается из списка тип данных, которые будут проверяться и вводиться в выделенный нами диапазон ячеек. Для примера выберем «Целое число».

После выбора типа данных становится возможным выбор условия соответствия вводимого значения в графе «Значение:». Выберем для примера «Между».

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

На следующей вкладке «Сообщение для ввода» можно указать заголовок и само сообщение подсказку, которое будет высвечиваться при активации ячейки с проверкой вводимых данных.

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

В отдельных случаях, когда возникает необходимость, вы можете использовать собственный числовой формат. Для того чтобы заполнить символами пустые позиции ячейки, достаточно просто включить в собственный формат звездочку (*). Например, используя следующее определение числового формата, можно дополнить число знаками тире:

Excel если ячейка содержит значение то — Как в Excel применять функцию ЕСЛИ() — Как в офисе.

1. Создайте формулы, преобразующие исходные данные. 2. Выделите ячейку с формулой. 3. Выберите команду Главная→Буфер обмена→Копировать. 4. Выделите исходную ячейку с формулой. 5. Выберите команду Главная→Буфер обмена→Вставить→Вставить значения.

Как создать зависимый выпадающий список в Excel?

Существует несколько вариантов. Один из них, это сочетание именованных диапазонов и функции ДВССЫЛ .

Именованный диапазон в Excel – это ячейка (или диапазон ячеек), которой присвоено имя.

Функция ДВССЫЛ в Excel преобразовывает текст в ссылку.

Ниже приведен еще один пример использования функции СИМВОЛ. Следующая формула возвращает строку Stop, объединяя четыре символа, полученные с помощью функции СИМВОЛ:

Зависимый выпадающий список в Excel и Google таблицах · BIRDYX

Excel использует знак & как оператор конкатенации (объединения строк). Конкатенация – это просто модный термин, указывающий, что результат объединяет содержимое нескольких ячеек. Например, если ячейка А1 содержит текст Tucson, а ячейка А2 – California, приведенная ниже формула возвратит текст TucsonCalifornia:

Как исправить

Теперь разберемся подробнее, как исправить в Эксель ЗНАЧ в формуле, когда не удается сделать вычисление в обычном режиме. Рассмотрим разные проблемы и методы решения.

Вычитание

Наиболее распространенная ситуация, когда ошибка «ЗНАЧ» в Excel возникает из-за трудностей с вычитанием. Чтобы исправить сбой, могут помочь рассмотренные ниже шаги.

Для начала проверьте правильность ввода формулы вычитания. Сделайте следующие шаги, чтобы исправить ситуацию в Excel:

• Как вариант, используйте опцию «СУММ» с цифрами выше / ниже нуля. Для этого введите параметр выше нуля в одной, а ниже — в другой ячейке. В третьей применяется опция «СУММ» для складывания. Пример: =СУММ (D5;E5).

Еще один способ, как убрать ЗНАЧ в Excel — ввести правильную функцию вычитания. Алгоритм действий такой:

1. Сделайте проверку. Для этого в новом файле введите «3» в А1 и 6 в В1. Если при формуле =В1-А1 появляется ЗНАЧ, переходите к следующему шагу.
2. Войди на Виндовс в раздел «Региональные стандарты». В «десятке» в поисковой строке введите «регион», а после выберите панель управления «Региональные стандарты».
3. На вкладке «Форматы» кликните на «Дополнительные параметры» и найдите «Разделитель элементов списка». При наличии здесь знака «минус» поменяйте его на другой элемент и жмите ОК.
4. Войдите в книгу Excel. Если в ячейке имеется надпись #VALUE!, два раза жмите на нее для внесения правок. В случае, когда вместо «минуса» стоит запятые, замените их на «минус». Далее жмите «Ввод» и повторите эти шаги для других ячеек, для которых возникла ошибка.

Распространенная причина, почему в Экселе выходит ЗНАЧ — неправильные операции с датами. Для решения вопроса сделайте следующее:

Следующий способ, как исправить ЗНАЧ в Excel — убедиться, что программа распознает текст, как дату для проведения проверки. Чтобы исправить сбой, сделайте следующее:

• Два раза жмите на дату, используемую в формуле.
• Разместите курсор на старте и убедитесь, можно ли выбрать один или более пробелов.
• Выделите столбец с датой, нажав на заголовок.
• Кликните на «Данные», а потом — «Текст по столбцам».

• Два раза жмите «Далее».
• На третьем шаге в секции «Формат данных столбца» и переместите переключатель «Дата».
• Выберите формат даты и кликните «Готово».
• Повторите эти шаги для разных столбцов, чтобы убедиться в них нет пробелов возле даты.

Пробелы и текст

Еще одно объяснение, что значит ЗНАЧ в Excel — проблемы с текстами / пробелом. В таком случае воспользуйтесь одним из рассмотренных ниже шагов.

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

• Выделите ячейки, куда направляют ссылки.
• Перейдите в раздел «Главная», а поле жмите «Найти и выделить».
• Кликните «Заменить».
• В поле «Найти» введите пробел, а в поле «Заменить» удалите все, что там имеется.

• Замените одно / все вхождения.
• Если ЗНАЧ не удалось исправить, включите фильтр в разделе «Главная», а далее «Сортировка и фильтр» — «Фильтр».
• Жмите стрелку внизу и удалите флажок с поля «Выделить все», а после установите отметку «Пустые».
• Поставьте флажки в местах без названия.
• Выделите незаполненные ячейки и удалите их.
• Кликните на стрелку вниз и выберите «Удалить фильтр из…».

Если ошибка ЗНАЧ появлялась из-за пробелов, рассмотренным методом удается исправить ситуацию. В ином случае может потребоваться применение другого варианта.

Причиной, почему вы видите ЗНАЧ в Эксель, может быть текст / специальные знаки. Обратите внимание на ячейки и удалите лишнее. Может потребоваться применение функции «Заменить» или «ПЕЧСИМВ» или «ЗАМЕНИТЬ».

Еще один способ, как исправить, если в Excel вместо цифр ЗНАЧ — использовать формулы вместо операций. При наличии «+» или «*» рассчитать ячейки с текстом / пробелом не получится. Для решения проблемы может потребоваться применение специальных функций. К примеру, вместо сложной формулы =А2+В2+С2 укажите =СУММ (А2:С2).

Дополнительные шаги

Рассматривая пути, как исправить ошибку ЗНАЧ в Excel, можно использовать и другие пути решения проблемы:

• Определите источник ошибки. Для начала выделите секцию, где имеется параметр ЗНАЧ, а после этого жмите на «Формулы», «Вычислить формулы», а после — «Вычислить». В этом случае Эксель обрабатывает каждую часть формулы отдельно. При этом можно увидеть, где проблема, а после исправить ее.

• Замените ЗНАЧ другим параметром Excel, чтобы исправить проблему. Попробуйте добавить формулу ЕСЛИОШИБКА, которая проверят наличие сбоев и заменяет ЗНАЧ другим значением, к примеру, двумя дефисами.
• Восстановите подключение. Попросите создателя файла Excel сделать новую книгу, чтобы в ней были только параметры без подключений. Для этого копируйте ячейки и вставьте только значения. Это происходит следующим образом — «Главная», а потом «Вставить» и «Специальная вставка» — «Значения».

Как вариант, можно зайти в сообщество Excel по ссылке go.microsoft.com/fwlink/?linkid=827514, где можно задать вопрос и исправить проблему с Excel.

Теперь вы знаете, почему в Экселе пишет ЗНАЧ и не считает формулу, как исправить ситуацию, и какие шаги необходимо осуществлять в первую очередь. Более подробные сведения можно найти на официальном сайте по ссылке support.microsoft.com/ru-ru/office/исправление-ошибки-знач-ошибка-15e1b616-fbf2-4147-9c0b-0a11a20e409e.

В комментариях расскажите, какое из приведенных решений вам помогло исправить проблему с Excel, и какие еще можно использовать варианты.

Для работы с кодами символов Excel предоставляет две специальные функции: КОДСИМВ и СИМВОЛ. Несмотря на то, что эти функции не столь популярны, как остальные, они пригодятся при совместном использовании с другими функциями.

9 ошибок Excel, которые вас достали

Обратите внимание, что пробелы тоже считаются символами и включаются в результирующее значение. К примеру, если ячейка А1 содержит строку Продажи и приведенная выше формула вернет значение 8, значит, в начале или конце строки есть пробел.

Excel если ячейка содержит значение то

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

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

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

Функция ЕТЕКСТ принимает один аргумент и возвращает значение ИСТИНА, если ячейка содержит текст, или значение ЛОЖЬ в противном случае. К примеру, следующая формула вернет значение ИСТИНА, если ячейка А1 содержит текст:

Функция ТИП также имеет один аргумент и возвращает значение, которое указывает тип данных, содержащихся в ячейке. Например, если ячейка А1 содержит текстовую информацию, формула, приведенная ниже, вернет значение 2 (кодовый номер текстового формата):

Функция ЕТЕКСТ считает текстом также числовое значение, перед которым расположен апостроф. Однако она не считает текстом число, отформатированное как текст (если текстовый формат применен после ввода числа в ячейку).

Каждому символу, который виден на экране компьютера, соответствует определенное кодовое число. Для работы в системе Windows приложение Excel использует стандартный набор символов ANSI, который содержит 255 символов, пронумерованных числами в диапазоне от 1 до 255.

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

Для работы с кодами символов Excel предоставляет две специальные функции: КОДСИМВ и СИМВОЛ. Несмотря на то, что эти функции не столь популярны, как остальные, они пригодятся при совместном использовании с другими функциями.

Функции КОДСИМВ и СИМВОЛ работают только со строками в кодировке ANSI. Эти функции не будут выполняться для строк в двухбайтовой кодировке Unicode.

Функция КОДСИМВ, которая используется в приложении Excel, возвращает код символа, введенного в качестве аргумента функции. Например, формула, приведенная ниже, возвращает значение 192 – код русского символа А, введенного в верхнем регистре.

В том случае, если аргумент функции КОДСИМВ содержит несколько символов, функция использует только первый символ. Например, следующая формула возвращает значение 196 – код символа Д:

По своей сути функция СИМВОЛ полностью противоположна функции КОДСИМВ. Ее аргументом является числовое значение в интервале от 1 до 255, а сама функция возвращает символ, соответствующий этому значению. Например, приведенная ниже формула возвращает русский символ А:

Чтобы продемонстрировать разницу между функциями КОДСИМВ и СИМВОЛ, введите в ячейку следующую формулу:

Формула вернет символ А. Этот пример лишь иллюстрирует действие функций, вряд ли он будет полезен на практике. Сначала введенный символ преобразуется в соответствующее значение кода (192), после чего функция СИМВОЛ возвращает символ А, который соответствует данному значению.

Теперь предположим, что ячейка А1 содержит символ А (в верхнем регистре). Тогда следующая формула вернет символ а (в нижнем регистре):

Чтобы определить, содержат ли две ячейки идентичные записи, используйте простую логическую формулу. Например, ниже приведена формула, с помощью которой можно определить, содержит ли ячейка А1 то же значение, что и ячейка А2:

Чаще всего не стоит беспокоиться по поводу регистра символов текста. Если же необходимо при сравнении учитывать регистр символов, можно использовать функцию СОВПАД. Приведенная ниже формула возвращает значение ИСТИНА только в том случае, если ячейки А1 и А2 содержат абсолютно идентичные записи.

Следующая формула возвращает значение ЛОЖЬ, поскольку первая строка содержит в конце пробел:

Excel использует знак & как оператор конкатенации (объединения строк). Конкатенация – это просто модный термин, указывающий, что результат объединяет содержимое нескольких ячеек. Например, если ячейка А1 содержит текст Tucson, а ячейка А2 – California, приведенная ниже формула возвратит текст TucsonCalifornia:

Обратите внимание, что эти две строки объединены без промежуточного пробела. Для того чтобы добавить пробел между двумя строками и получить текст Tucson California, необходимо использовать следующую формулу:

Другой способ, который, может быть, даже лучше предыдущего, – использовать запятую и пробел, чтобы получить текст Tucson, California.

Еще один способ объединения строк состоит в применении функций СИМВОЛ с соответствующими аргументами. Обратите внимание, что в приведенном ниже примере использования функции СИМВОЛ в объединяемый текст вставляются запятая (44) и пробел (32).

Ниже приведен еще один пример использования функции СИМВОЛ. Следующая формула возвращает строку Stop, объединяя четыре символа, полученные с помощью функции СИМВОЛ:

Рассмотрим еще один пример использования оператора конкатенации, В данном случае формула объединяет текст с результатом выражения, которое возвращает максимальное значение столбца С.

Имейте в виду, что в Excel есть также функция СЦЕПИТЬ, которая поддерживает до 255 аргументов. Эта функция объединяет свои аргументы в единую строку. Многие пользователи предпочитают применять именно ее, однако использование оператора конкатенации (&) значительно проще.

По существу, эта формула объединяет текстовую строку с содержимым ячейки ВЗ и отображает полученный результат. Обратите внимание, что в А5 к содержимому ВЗ не был применен ни один специальный формат. Тем не менее при желании можно установить для нее денежный формат с использованием пробелов и символа валюты.

Имейте в виду, что, вопреки ожиданиям, применение числового формата ко всей ячейке, содержащей формулу, не даст никакого эффекта. Все дело в том, что используемая формула возвращает строку, а не числовое значение.

Применить формат к содержимому ячейки ВЗ в ячейке А5 можно с помощью функции ТЕКСТ. Для этого нужно ввести в А5 такую формулу:

Эта формула будет отображать и текст, и само отформатированное числовое значение следующим образом:

Второй аргумент функции ТЕКСТ содержит стандартное определение числового формата, используемого в приложении Excel. В качестве этого аргумента можно ввести любое другое допустимое определение числового формата.

В предыдущем примере мы использовали простую ссылку на ячейку ВЗ. Но это не единственная возможность. Вместо ссылки на ячейку можно использовать любое выражение. Ниже приведен пример, в котором текст объединяется с числом, полученным путем вызова функции СРЗНАЧ.

Теперь мы рассмотрим другой пример, в котором используется функция СЕГОДНЯ, возвращающая текущую дату и время. Использование этой функции совместно с функцией ТЕКСТ позволяет отобразить на экране текущую дату и время, представленные в удобном для восприятия формате.

Отображение денежных значений, отформатированных как текст

В отдельных случаях функция РУБЛЬ может использоваться вместо функции ТЕКСТ. Однако намного эффективнее применение функции ТЕКСТ, которая является более гибкой, поскольку не ограничивает вас определенным числовым форматом.

Приведенная ниже формула возвращает такой текст: Итого: 1 287,37р. Второй аргумент функции РУБЛЬ определяет количество десятичных знаков после запятой.

Удаление пробелов и непечатных символов

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

• Функция СЖПРОБЕЛЫ удаляет из строки ведущие и замыкающие пробелы. Кроме того, внутренние последовательности пробелов она заменяет одним пробелом. • Функция ПЕЧСИМВ удаляет из строки все непечатаемые символы, которые в импортированном формате были служебными символами.

Рассмотрим пример использования функции СЖПРОБЕЛЫ. Приведенная ниже формула возвращает строку “Чистый доход за квартал” без лишних пробелов.

Функция ДЛСТР принимает только один аргумент и возвращает количество символов, содержащихся в ячейке. Возьмем, например, ячейку А1, содержащую строку Продажи в сентябре. Формула, приведенная ниже, вернет значение 18.

Обратите внимание, что пробелы тоже считаются символами и включаются в результирующее значение. К примеру, если ячейка А1 содержит строку Продажи и приведенная выше формула вернет значение 8, значит, в начале или конце строки есть пробел.

Следующая формула укорачивает текст, который оказался слишком длинным. Если в ячейке А1 содержится более десяти символов, формула возвращает первые 9 символов, за которыми следует символ троеточия (в кодовой таблице ANSI этот символ имеет код 133). Если символов десять или меньше, возвращается вся строка.

Функция ПОВТОР предназначена для того, чтобы повторить любую строку текста или символ (первый аргумент) заданное количество раз (второй аргумент). Например, следующая формула возвращает текст НаНаНа:

Эту функцию удобно использовать для создания горизонтального разделителя между ячейками. Например, приведенная ниже формула создает строку, состоящую из 20 волнистых линий (тильд), расположенных по длине строки:

Средства условного форматирования позволяют создавать простые гистограммы непосредственно в ячейках.

Добавление к числу заданных символов

Вероятно, многие из вас уже не раз сталкивались с таким распространенным (особенно при печати чеков) методом защиты, как дополнение числовых значений справа звездочками. Приведенная ниже формула, наряду со значением, содержащимся в ячейке А1, отображает знаки звездочек, дополняя общее количество символов до 24. Таким образом, пустые позиции ячейки заполняются звездочками.

Используя следующую формулу, можно добавить звездочки к числу слева:

Представленная далее формула отображает звездочки, дополняющие число сразу с двух сторон. Она возвращает 24 символа в том случае, если число в ячейке А1 содержит четное количество символов, и 23 символа, если число содержит нечетное количество символов.

Приведенные выше формулы не столь совершенны, поскольку они могут отображать не все отформатированные числа. Ниже приведена усовершенствованная версия формулы, которая отображает значение, содержащееся в ячейке А1 (отформатированной ячейке), а также знаки звездочек слева от этого значения.

В отдельных случаях, когда возникает необходимость, вы можете использовать собственный числовой формат. Для того чтобы заполнить символами пустые позиции ячейки, достаточно просто включить в собственный формат звездочку (*). Например, используя следующее определение числового формата, можно дополнить число знаками тире:

Чтобы просто дополнить число звездочками, используйте в определении формата две звездочки, как показано ниже.

Приложение Excel предлагает три весьма удобные функции, которые позволяют изменить регистр символов.

1. Функция ПРОПИСН преобразует все символы текста в верхний регистр. 2. Функция СТРОЧН преобразует весь текст в нижний регистр. 3. ПРОПНАЧ преобразует первый символ каждого слова в верхний регистр, а все остальные символы – в нижний.

Эти функции действуют очень просто. Например, следующая формула преобразует все символы текста в ячейке А1 в верхний регистр. Если ячейка А1 содержит текст MR. JOHN Q. PUBLIC, она вернет строку Mr. John Q. Public.

Следует отметить, что эти функции применяются только для символов, вошедших в алфавит. Все остальные символы они просто игнорируют и возвращают неизменными.

Функция ПРОПНАЧ не всегда приводит к нужному результату, поскольку она может неправильно трактовать некоторые слова. К примеру, фамилию McCartney она изменит на Mccartney.

Преобразование данных с помощью формул

Многие примеры этого раздела описывают методы использования функций для преобразования данных тем или иным способом. Например, можно применить функцию ПРОПИСН для преобразования текстовой строки в верхний регистр. Чаще всего исходные данные требуется заменить преобразованными данными. Для этого вставьте преобразованное значение поверх исходного текста.

1. Создайте формулы, преобразующие исходные данные. 2. Выделите ячейку с формулой. 3. Выберите команду Главная→Буфер обмена→Копировать. 4. Выделите исходную ячейку с формулой. 5. Выберите команду Главная→Буфер обмена→Вставить→Вставить значения.

После выполнения приведенных выше действий формула удаляется, а на ее место вставляются преобразованные данные.

Извлечение заданных символов из строки

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

• Функция ЛЕВСИМВ возвращает заданное количество символов с начала строки. • Функция ПРАВСИМВ возвращает заданное количество символов с конца строки. • Функция ПСТР возвращает заданное количество символов, начиная с любой позиции в пределах строки.

Формула, приведенная ниже, возвращает последние 10 символов содержимого ячейки А1. Если ячейка А1 содержит менее десяти символов, формула вернет текст ячейки в полном объеме.

В следующей формуле используется функция ПСТР. Она возвращает из ячейки А1 пять символов, начиная со второго. Другими словами, формула возвращает символы со второго по шестой.

Таким образом, если бы ячейка А1 содержала текст ПЯТЫЙ КВАРТАЛ, данная формула вернула бы текст Пятый квартал.

Ниже приведена формула, в которой используется функция ПОДСТАВИТЬ для замены значения года 2001 на 2002 в строке Бюджет 2001. Эта формула возвращает значение Бюджет 2002.

Следующая формула использует функцию ПОДСТАВИТЬ для удаления из строки всех пробелов. Другими словами, она заменяет все пробелы пустой строкой и возвращает строку Белыйшоколадсизюмом.

Приведенная далее формула использует функцию ЗАМЕНИТЬ для замены всего одного символа, расположенного в пятой позиции, ничего не вставляя вместо него. Иными словами, она просто удаляет шестой символ (дефис) и возвращает текст Часть544.

Например, если ячейка А1 содержит строку Part-2A — Z(4М1)_А*, данная формула вернет Part2AZ4M1A.

Найти местонахождение определенного текста или символа в пределах одной строки в приложении Excel можно с помощью функций НАЙТИ и ПОИСК.

В формуле, приведенной ниже, применена функция НАЙТИ, которая возвращает значение 7, т.е. номер позиции первого символа m, встречающегося в строке. Следует отметить, что эта формула учитывает регистр символов:

Следующая формула использует функцию ПОИСК и возвращает значение 5 – номер позиции первого встреченного символа m. В этом случае регистр не учитывается.

В первом аргументе функции ПОИСК можно использовать следующие символы макроподстановки:

• вопросительный знак (?) соответствует любому одиночному символу; • звездочка (*) соответствует любой последовательности символов.

Чтобы найти символы вопросительного знака или звездочки, введите в формуле перед этими символами тильду (~).

Следующая формула исследует текст в ячейке А1 и возвращает позицию первой последовательности из трех символов, содержащей в середине дефис. Иначе говоря, формула ищет любой символ, за которым следует знак дефиса и любой другой символ. Таким образом, если ячейка А1 содержит текст Part-А90, формула возвращает значение 4.

Функция ПОИСК может использоваться совместно с функцией ЗАМЕНИТЬ, что позволяет заменить часть найденной текстовой строки другой строкой. В этом случае функция ПОИСК используется для того, чтобы найти начало расположения символов, с которым затем будет работать функция ЗАМЕНИТЬ.

Предположим, что ячейка А1 содержит текст Отчет о прибыли. Формула, приведенная ниже, отыскивает в этой строке слово прибыли и заменяет его на слово доходах.

Следующая формула использует функцию ПОДСТАВИТЬ для достижения того же эффекта, но более удобным способом:

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

Как сопоставить данные в Excel?

Ошибка #ЗНАЧ! появляется когда в формуле присутствуют пробелы, символы либо текст, где должно стоять число. Разные типы данных. Например, формула =A15+G14, где ячейка A15 содержит «число», а ячейка G14 — «слово».