Как excel отличает число от текста текст от функций
В ячейках листа Excel могут храниться данные нескольких типов: текст, числа или формулы. И так как для Excel это разные типы данных, то и работает он с ними по-разному (например, к тексту нельзя применять математические операции, а числа не получится использовать во многих текстовых функциях).
Проблемы начинаются тогда, когда на листе появляются числа, которые программа воспринимает как текст. Наиболее часто такая ошибка возникает при выгрузке данных из других приложений, учетных систем и программ (например, из 1С или какой-то ERP-системы). Последствия такой ошибки достаточно существенны. Число, выгруженное как текст, не участвует в расчетах, неправильно обрабатывается сводными таблицами, даёт ошибки в формулах, неверно сортируется и фильтруется.
Одна такая ошибка среди большого массива чисел, хотя и будет почти незаметна визуально (особенно для неопытного пользователя), исказит все результаты расчетов. Как же найти число, сохраненное как текст?
Признаки того, что число сохранено как текст
- Значение в ячейке выравнено по левому краю (по умолчанию, текст выравнивается по левому краю, а числа - по правому);
- В левом верхнем углу ячейки горит индикатор ошибки (маленький зеленый уголок). При выделении такой ячейки в правом верхнем углу появляется пиктограмма с восклицательным знаком. При наведении на него или при нажатии - появляется указание, что число сохранено как текст.
- Число не учитывается математическими формулами (например, не попадает в общую сумму, как на рисунке выше).
Существует достаточно много других способов исправления ошибки.
1. Использование формулы с математическим действием
Если применить к проблемному числу математическое действие, то оно превратится в настоящее число, воспринимаемое программой. Разумеется, математическая операция не должна изменять значение числа (такими операциями являются: умножение и деление на единицу, прибавление и вычитание нуля, возведение в первую степень, двойное отрицание).
Минус такого подхода - исправленное число появится в ячейке, в которую введена формула (его скорее всего потребуется заменить на значение). Исходный диапазон не изменится (по крайней мере, пока Вы не замените его на полученные верные значения).
2. Использование формулы с функцией ЗНАЧЕН()
Встроенная функция ЗНАЧЕН() предназначена как раз для решения описанной проблемы. Она преобразует число как текст в нормальное число. Аргумент у нее один - ячейка с проблемными данными (например =ЗНАЧЕН(А1) исправит число в ячейке А1).
Минус - тот же, что и в способе выше. С другой стороны, если Вы постоянно подгружаете проблемные данные в одни и те же ячейки, которые потом используете в формулах, то можно один раз прописать в формулу функцию ЗНАЧЕН() и больше не вспоминать про то, верно ли выгружены числа в исходный диапазон.
3. Использование специальной вставки с применением математической операции
Вероятно, самый полезный способ. По своей сути аналогичен первому, но позволяет исправлять данные прямо в исходных ячейках.
Выделите любую ПУСТУЮ ячейку, скопируйте ее, затем выделите ячейки с проблемными данными и нажмите сочетание клавиш Ctrl+Alt+V. В открывшемся окне специальной вставке выберите "значения" и операцию "сложить" или "вычесть". В результате прямо в исходных ячейках будет произведено прибавление к данным нуля (или вычитание, в зависимости от выбранной операции), и проблемные числа будут преобразованы в обычные.
4. Использование индикатора-ошибки
Если ячейки с проблемными данными помечены соответствующим индикатором ошибки, то задача по их исправлению сводится к двум кликам. Выделяем ячейку или проблемный диапазон, кликаем на появившейся иконке восклицательного знака на желтом фоне и выбираем "Преобразовать в число".
Быстро и удобно. К сожалению, очень часто Excel не помечает проблемные числа индикатором. Если это Ваш случай - пользуйтесь одним из способов выше.
Каждый из описанных способов хорош в определенной ситуации. Выбирайте подходящий, исходя из своих задач.
Всё вышеописанное справедливо и по отношению к датам и времени (так как они в Excel на самом деле являются числами).
Видеоверсию данной статьи смотрите на нашем канале на YouTube
Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel - подписывайтесь на наш канал в Telegram Excel Everyday
Куча интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point, Visio и т.д.) - на нашем канале в Telegram Office Killer
Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot
Вопросы по другому ПО (кроме Excel) задавайте второму боту - @KillOfBot
Нужна функция, возвращающая истину, если значение ячейки можно интерпретировать как число, и ложь, если там есть буквы или другие символы, кроме цифр. Ну, или наоборот, в первом случае ложь, а во втором истину.
Сергей Шепелев Мудрец (14348) Achez, если формат ячейки текстовый, то функция в любом случае вернет результат FALSE
Задача в задачнике. Сделать содержимое ячейке синим, если там 0, зелёным, если положительное число, красным, если отрицательное, жёлтым, если текст. Первые три пункта сделал, затык на четвёртом.
magistr_s Мыслитель (5387) Achez, ..а что, текст не может содержать цифры - почему нет например "10 негритят" - это же текст
Есть, у меня, функция, позволяющая выделить цифры или текст
Всё сложней, чем кажется!
Понятно, если в ячейке стоят не только цифры! А если.
Пусть в A1 стоит 123, а в A2 ="123"&""
Будет выглядеть одинаково (только первое значение будет прижато вправо в ячейке, а второе - влево).
Но =ЕЧИСЛО (A1) вернёт ИСТИНА, а =ЕЧИСЛО (A2) вернёт ЛОЖЬ.
И наоборот (для функции =ЕТЕКСТ ()).
Но =ЕЧИСЛО (-A2) (или =ЕЧИСЛО (A2+0) или =ЕЧИСЛО (A2*1)) вернёт ИСТИНА. Excel САМ преобразует подходящие тексты в числа для арифметических операций.
Так ЧТО стоит в ячейке A2.
поскольку вопрос "корявый" - то и ответ такой же
". Есть ли функция в Excel, отличающая число от текста. "
да, есть 2-е функции =Ч (А1) и =Т (А1) которые отличают числа и текст
Е4=Т (D4) Если ячейка D4 содержит текст тогда Е4 повторит этот текст
H4=Т (G4) если ячейка G4 находится число тогда Н4 ничего отображать не будет
=Ч (D5) если D5 текст тогда Е5=0
Н5=Ч (G5) если G5 число тогда G5 повторит это число
Если у вас ячейка содержит "смешанный текст" (Лена родила девочку 4,640 кг) тогда образец ТАК ЕСТЬ и ТАК ХОЧУ в студию
Вы вопрос прочитали? Он предельно чёток. Мне не нужно повторять написанное, мне нужна истина или ложь.
Abram Pupkin Гений (83376) Achez,
1. Укажите на ячейку В15 и щелкните левой кнопкой мыши.
2. Напечатайте в ячейке В15 следующую формулу: = В3 - В13 и нажмите Enter.
Вместо реального текста, введенного при выполнении вышеописанной операции 2, в ячейке В15 появилось числовое значение - ноль (0). Чтобы понять почему это случилось, вам достаточно взглянуть на содержимое строки формул Excel.( см. рис. 2.6. )
Обратите внимание на то, что в ней присутствует следующее обозначение:
Введенный в начале этого обозначения знак равенства (=) указывает на то, что в этой ячейке должна содержаться математическая формула, а не реальный текст, который вы ввели. В данном случае введенная формула служит указанием на то, что значение, которое будет помещено в ячейку В15, должно быть рассчитано вычитанием содержимого ячейки В13 из содержимого ячейки В3. Поскольку обе ячейки, на которые вы сослались, в данный момент пусты, результат этого подсчета, как вы видите на рисунке 2.6, равен нулю.
Относительные ссылки на ячейки
1. Укажите на маленький квадратик в нижнем правом углу ячейки В15. (Когда вы будете указывать на элемент заполнения, экранный курсор превратится в маленькое перекрестие). Будем называть его маркер заполнения.
2. Нажмите и не отпускайте левую кнопку мыши.
3. Двигайте мышь вправо, пока возникающая рамка не охватит ячейки В15-G15.
4. Отпустите левую кнопку мыши.
5. Щелкните по ячейке G15.
Ваш экран должен стать похожим на рисунок 2.7. Заметьте, что все ячейки, выбранные с помощью автозаполнения теперь содержат . Обратите внимание и на то, что строка формул для ячейки G15 содержит следующее обозначение:
Excel автоматически применил относительную ссылку на ячейки к содержимому первоначально указанной ячейки, поскольку функция автозаполнения воспроизвела эту ячейку в указанных местах. Иными словами, она скопировала формулу, видоизменив ее в соответствии с тем, в каком столбце находится та или иная ячейка.
Есть несколько вариантов копирования формул, которые будут рассмотрены позже. Сейчас вы знакомитесь с одним из самых простых вариантов копирования формул в соседние ячейки.
Рассмотрим подробнее как работает функция копирования формул. Для этого проделайте следующее:
Заполните столбцы С и D числами, подобными значениям в столбце В.
В ячейке В12 запишите формулу суммирования столбца В. После чего у вас должно появиться значение суммы данного столбца.
Выделите ячейку В12 и перетащите маркер заполнения с помощью левой клавиши мыши вправо по горизонтали так, чтобы смежные ячейки С12 и D12 были выделены пунктирной рамкой. Отпустите клавишу мыши.
Excel скопирует формулу суммирования в ячейку С12 и D12, причем номера столбцов будут соответственно заменены на С и D. Убедитесь в том какие формулы получились в ячейках С12 и D12, выделив конкретную ячейку и посмотрев на строку формул.
Самостоятельно посчитайте сумму по строке в ячейке, например, Е5 и скопируйте эту формулу на другие строки.
Запишите в ячейку D19 текст Высшее и проделайте подобные манипуляции (см. п.3) вправо и вниз или вверх. Excel скопирует текст. Этим способом можно копировать и числа. Попробуйте самостоятельно проделать операцию копирования чисел.
Ввод формул
При вводе формул в ячейки может оказаться не результат значения, проделанных по формуле операций, а сама формула, поэтому прежде, чем работать с вводом данных,, посмотрите, чтобы в меню Сервис, команда Параметры, вкладка Вид не стоял флажок около названия Формулы. Поставьте флажок около этого названия и посмотрите как изменится содержимое ячейки В15. Потом уберите этот флажок.
В ячейке В12 посчитайте значение по следующей формуле: =В5+В6+В7, записав формулу в ячейку и зафиксировав полученное значение нажатием клавиши ENTER.Затем удалите полученный результат нажатием клавиши DELETE.
Посчитайте еще раз значение по формуле (см. п.3) в ячейке В12, записав формулу в строку формул. При этом, вместо ввода ячеек В5, В6, В7 нажимайте на них левой кнопкой мыши.
Последовательность введения формулы следующая:
Пометьте ячейку В12.
Встаньте на строку формул.
Введите символ “=“.
Нажмите мышью на ячейке В5.
Введите символ “+”.
Нажмите на ячейке В6 и т.д.
Номера ячеек появятся в строке формул. Зафиксировать значение в ячейке нажатием кнопки ввод в строке формул.
Отмените полученный результата нажатием Ctrl+Z
Произведите суммирование по столбцу В следующим образом:
1. Щелкните мышью на ячейке В5.
2. Выделите ячейки с В5 по В12, используя левую клавишу мыши.
4. В ячейке В12 появится сумма данных.
5. Зафиксируйте ввод данных. Посмотрите какая формула получилась в строке формул.
3. Контрольные вопросы:
Как Excel отличает число от текста, текст от функций?
Что такое функция?
Как увидеть формулу, записанную в ячейку?
Как сделать так, чтобы в ячейке отражался не результат вычислений по формуле, а сама формула?
Вспомните о других настройках в Excel.
4. Используемая литература.
1. Наймершайм Дж. Учебное пособие / Excel 4.0 для Windows.
2. О.Ефимова, В.Морозова, Ю.Шафрин Курс компьютерной технологии (в 2-х томах).
3. С.Грошев, А.Комягин, С.Коцюбинский Современный самоучитель (в 2-х томах).
Большое количество функций предназначено для обработки текстов. С помощью этих функций пользователь может преобразовывать прописные литеры в строчные, текстовые значения в числовые и обратно, а также выполнять целый ряд других операций. В качестве аргументов текстовых функций используются, как правило, цепочки символов.
Функция ДЛСТР
Результат: Количество символов в текстовом аргументе текст.
Функция ЗАМЕНИТЬ
Результат: В текстовом аргументе старый_текст заменяет число_литер символов, начиная с нач_ном-ого, на текст новый_текст.
- старый_текст — текст, в котором желательно заменить некоторые литеры;
- нач_ном — позиция символа в тексте старый_текст, начиная с которой будет произведена замена;
- число_литер — число символов в тексте старый_текст, которые заменяются текстом новый_текст;
- новый_текст — текст, который заменяет символы в тексте старый_текст.
Функция ЗНАЧЕН
Результат: Преобразует текстовый аргумент текст в числовой формат.
Обычно Excel при необходимости автоматически осуществляет преобразование из текстового представления в числовое.
Функция КОДСИВМ
Функция ЛЕВСИМВ
Результат: Возвращает первые количество_символов символов текстового аргумента текст в виде текстового значения.
- количество_символов — число больше 0; если значение аргумента количест-во_символов больше, чем длина текста, то будет выведен весь аргумент текст\ если аргумент количест-во_символов опушен, Excel принимает его равным 1.
Функция НАЙТИ
Результат: Находит вхождение одной текстовой строки в другую текстовую строку и возвращает номер символа, с которого начинается первое вхождение искомой строки. Для поиска вхождений одной текстовой строки в другую текстовую строку можно использовать также функцию ПОИСК, но в отличие от функции ПОИСК функция НАЙТИ учитывает регистр и не допускает символов шаблона.
- искомый_текст — искомый текст; если искомый_текст — это (пустая строка), то функция НАЙТИ считает подходящим первый символ в просматриваемой строке (то есть будет возвращено значение аргумента нач_позиция или 1); аргумент нач_позиция не должен содержать никаких символов шаблона;
- просматриваемый_текст — текст, содержащий искомый текст;
- нач_позиция — позиция символа, с которой следует начинать поиск. Первый символ в аргументе просматриваемый_текст имеет номер 1. Если аргумент нач_позиция опущен, то он полагается равным 1.
Функция ПЕЧСИМВ
Результат: Из аргумента текст будут удалены все управляющие символы.
Функция ПОВТОР
Результат: Текст, указанный в аргументе текст, повторяется сколько_раз раз.
Функция ПОДСТАВИТЬ
Результат: Текст, указанный в аргументе старый_текст, заменяется в аргументе текст на текст новый_текст.
Функция ПОИСК
Функции НАЙТИ и ПОИСК почти идентичны, но функция НАЙТИ различает написание прописными и строчными буквами, а ПОИСК — нет; кроме того, функция ПОИСК, в отличие от функции НАЙТИ, разрешает использование в аргументе искомый_текстсимволов подстановки (*) и (?).
Функция ПРАВСИМВ
Результат: Последние число_символов символов текстового аргумента текст в качестве текстового значения.
Функция ПРОПИСИ
Результат: Преобразует все буквы в тексте в прописные.
Функция ПРОПНАЧ
Результат: Первые буквы слов и все буквы, следующие за знаками препинания, будут преобразованы в прописные, все остальные — в строчные.
Функция ПСТР
Результат: Текстовое значение, которое включает количество_символов символов из текстового аргумента текст, начиная с позиции номер нач_позиция.
Функция РУБЛЬ
- число — любое число;
- число_знаков — целые числа (положительные устанавливают разряды после запятой, которые будут отображаться, отрицательные устанавливают разряды перед запятой, до которых будет произведено округление; если аргумент опущен, Excel предполагает наличие 2 разрядов после запятой).
Функция СЖПРОБЕЛЫ
Результат: Удаляет все пробелы из текстового аргумента текст, оставляя только по одному между словами.
Функция СИМВОЛ
Результат: Символ, соответствующий ASCII-коду числа, заданного аргументом число. Функция CHAR используется для преобразования текстовых файлов в текстовый формат данного компьютера.
Функция СОВПАД
Результат: Значение ИСТИНА, если аргументы текст1 и текст2 абсолютно одинаковы, в противном случае — значение ЛОЖЬ.
Функция СТРОЧН
Результат: Все прописные буквы текстового аргумента текст будут преобразованы в строчные.
Функция СЦЕПИТЬ
Результат: Объединяет несколько текстовых элементов в один.
Функция Т
Результат: Аргумент значение выводится в текстовом виде. Если аргументу значение не соответствует текст, то функция Т возвращает «».
Обычно Excel автоматически преобразует значения к нужному типу.
Функция ТЕКСТ
Результат: Преобразует значение в текст в заданном числовом формате.
- значение — либо числовое значение, либо формула, вычисление которой дает числовое значение, либо ссылка на ячейку, содержащую числовое значение;
- строка_формат — числовой формат в текстовой форме (вкладка Число диалогового окна Формат ячеек). Запрещено использование символа (*) и формата Общий.
Функция ФИКСИРОВАННЫЙ
Результат: Число число, округленное до число_знаков десятичных разрядов, в формате с фиксированной запятой в виде текстовой строки.
- число — число, которое округляется и преобразуется в текст;
- число_знаков — число цифр справа от десятичной запятой;
- без_разделителей — логическое значение, причем, если аргумент без_разде-лителей имеет значение ИСТИНА, то функция ФИКСИРОВАННЫЙ не включает разделители тысяч в возвращаемый текст. Если аргумент без разделителей имеет значение ЛОЖЬ или опущен, то возвращаемый текст будет включать разделители (обычно это так).
Excel гарантирует точность только до 15 разрядов; результатом является текстовое значение — в отличие от чисел, форматированных с помощью команды Ячейка (меню Формат).
Формула вернет символ А. Этот пример лишь иллюстрирует действие функций, вряд ли он будет полезен на практике. Сначала введенный символ преобразуется в соответствующее значение кода (192), после чего функция СИМВОЛ возвращает символ А, который соответствует данному значению.
Рассмотрим еще один пример использования оператора конкатенации, В данном случае формула объединяет текст с результатом выражения, которое возвращает максимальное значение столбца С. Если же вы хотите что-то уточнить, обращайтесь ко мне!
В отдельных случаях функция РУБЛЬ может использоваться вместо функции ТЕКСТ. Однако намного эффективнее применение функции ТЕКСТ, которая является более гибкой, поскольку не ограничивает вас определенным числовым форматом.
Текстовые функции
- старый_текст — текст, в котором желательно заменить некоторые литеры;
- нач_ном — позиция символа в тексте старый_текст, начиная с которой будет произведена замена;
- число_литер — число символов в тексте старый_текст, которые заменяются текстом новый_текст;
- новый_текст — текст, который заменяет символы в тексте старый_текст.
По существу, эта формула объединяет текстовую строку с содержимым ячейки ВЗ и отображает полученный результат. Обратите внимание, что в А5 к содержимому ВЗ не был применен ни один специальный формат. Тем не менее при желании можно установить для нее денежный формат с использованием пробелов и символа валюты.
Для реализации поставленной задачи, нам понадобится целых 6 функций для работы с текстом в Excel:
В первую очередь отделим фамилию. Для этого нам понадобится функция для печати n-го количество знаков с начала строки(с лева строки):
=ЛЕВСИМВ(текст; [количество знаков])
Кроме того, нам надо найти положение первого пробела в строке в столбце "А". Для этого воспользуемся функцией
=НАЙТИ(искомый_текст;просматриваемый_текст;[нач_позиция]) , где:
искомый_текст - текст, который мы ищем, в нашем случае это " " (знак пробела, вводить нужно с кавычками);
просматриваемый_текст - исходный текст, в котором мы ищем знак " ", т.е. ячейка "А1"
нач_позиция - позиция в "просматриваемый текст" с которого начинать поиск. В нашем случае этот параметр можно опустить, либо указать "1" - т.е. просмотр с первого символа.
Объединяем две функции в следующую конструкцию (в ячейку "В1"):
=ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)-1)
Обратите внимание, что после функции НАЙТИ() у нас строит "-1". Дело в том, что функция НАЙТИ возвращает позицию с учетом найденного символа, т.е. для случая в ячейке "А1", вернет не 6 а 7, а нам нужно отрезать именно 6 знаков.
Все, первый шаг из трех мы сделали - фамилия у нас уже отделилась. Если все сделать правильно, то скопировав формулу на остальные ячейки в столбце "В", получим такой результат:
Теперь попробуем отделить имена наших жертв в столбец "С":)). Задача усложняется, теперь, нам нужно найти как начальную позицию для вырезания, так и конечную. Основной функцией у нас будет:
=ПСТР(текст;начальная_позиция;количество_знаков) , где:
текст - текст, из которого вырезаем нужную строку, у нас "А1"
начальная_позиция - начальная позиция в тексте, из которого будет вырезан фрагмент
количество_знаков - количество знаков, которое необходимо вырезать
Чтобы определится с количеством вырезаемых знаков, воспользуемся функцией
=ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция]) , где:
искомый_текст - текст, который мы ищем, в нашем случае это пробел " "
текст_для_поиска - текст, в котором необходимо произвести поиск, у нас "А1"
нач_позиция - позиция в "текст_для_поиска" с которого необходимо начать поиск
В итоге, у нас должна получится следующая конструкция из функций: =ПСТР(A1;ДЛСТР(B1)+2;ПОИСК(" ";A1;ДЛСТР(B1)+2)-(ДЛСТР(B1)+2))
При помощи функции =ПСТР из текста в ячейке "А1" вырезаем текст, начиная с позиции равной длине текста в ячейке "В1" + 2 знака, эквивалентные пробелу и следующему за ним символу; количество символов для вырезания определяется как разность позиции, которую определяем функцией =ПОИСК второго знака пробел " " в тексте "А1" с начальной позицией для поиска, переходящей за первый пробел в тексте "А1" (часть =ДЛСТР(В1)+2) и длиной текста в ячейке В1 + 2 знака на пробел (фактически, определяем порядковый номер второго пробела и вычитаем длину уже известной части - фамилии, с учетом всех пробелов).
В итоге, при копировании формулы в остальные ячейки, должно получится следующее:
Третий шаг - вырезаем отчество. Упростим себе задачу, воспользовавшись функцией =ПРАВСИМВ(текст;[число_знаков])
Данная функция вырезает заданное число знаков, начиная с конца строки.
При помощи =ДЛСТР определяем общую длину текста в ячейке "А1" и отнимаем длину текста в "В1" и "С1", учитывая 2 пробела. Получаем такую конструкцию: =ПРАВСИМВ(A1;ДЛСТР(A1)-2-ДЛСТР(B1)-ДЛСТР(C1))
Скопируем получившуюся формулу в нижние строки и получаем результат:
Таким способом можно разделять текст в ячейках, признаком деления может быть любой символ.
Чтобы выполнить обратную задачу - сцепить текст из нескольких ячеек в одну, можно воспользоваться специальной текстовой функцией Excel: =СЦЕПИТЬ(текст1;текст2;. ) Так, чтобы в ячейке E1 получить сочетание И.О.Ф. достаточно добавить туда следующую функцию:
=СЦЕПИТЬ(C1;" ";D1;" ";B1)
Читайте также: