Как обозначить любое число в excel
Функция ЗНАК в Excel предназначена для определения знака числа и чаще всего используется в составе других выражений. Формула ЗНАК() относится к группе математических функций Excel.
В Excel знак числа нужно определять далеко не всегда. Обычно сама по себе формула ЗНАК не используется, а применяется в составе других выражений. В данной статье мы сначала рассмотрим особенности функции ЗНАК и её использование в «чистом» виде, а потом в комбинации с другими формулами. Вторая часть рассмотрена на видео.
Начнём с синтаксиса функции.
Синтаксис функции ЗНАК в Excel
Функция принимает только один аргумент, в качестве которого обычно указывают адрес какой-нибудь ячейки. Также можно передавать в формулу определения знака переменную или константу. Последний вариант пригодится только для тренировки при изучении программы.
Обобщённый синтаксис для функции ЗНАК выглядит следующим образом:
ЗНАК(число)
Стоит помнить, что формула предназначена только для работы с числами. При попытке определения знака для нечисловых значений получаются непонятные результаты — примеры показаны на видео и в прикреплённом файле Excel. Будьте внимательны, если аргумент для ЗНАК() рассчитывается при помощи другого выражения.
Таким образом, относительно функции ЗНАК() можно сделать следующие выводы:
- Формула воспринимает все типы данных как числа (или пытается это сделать);
- Если Вы не уверены, что на входе функции аргумент всегда будет действительным числом, то выполняйте проверку при помощи функции ЕЧИСЛО.
При определении знака числа может быть три варианта в зависимости от знака и значения самого числа:
- 1 — для положительных чисел (больше нуля);
- -1 — для отрицательных чисел (меньше нуля);
- 0 — только для числа ноль;
Часто функцию определения знака числа используют вместе с формулой вычисления модуля.
При передаче в формулу ЗНАК ссылки на пустую ячейку мы получим также 0, поскольку при операциях с числами Excel рассматривает пустые ячейки именно как ноль. Также будьте аккуратны при передаче даты, поскольку это не число.
Других особенностей формула определения знака чисел не имеет. Добавить её в ячейку Вы можете либо при помощи Мастера функций, либо написав вручную.
Если Вы хотите научиться работать в Excel, то рекомендуем воспользоваться нашим специальным видеокурсом по данной программе (13 часов, 51 урок). С примерами уроков можно познакомиться на странице описания видеокурса.
Как определить знак числа в Excel (примеры)
Скачайте файл Excel, прикреплённый после статьи. В нём, а также на видео, рассмотрены несколько примеров определения знака числа при помощи функции ЗНАК, в том числе есть примеры с передачей в формулу значений, похожих на числа, и вообще недопустимых аргументов.
Функция ЗНАК обычно используется в составе сложных формул, про которые мы рекомендуем отдельно прочитать здесь.
Обратите внимание, что возвращаемые формулой значения представляют собой числа (1, -1 и 0), то есть это не имеет отношения к логическим значениям. Возвращаемые значения можно просто вывести в ячейку, но чаще всего они используются или как аргумент другого выражения. Если же нужно именно вывести результат в ячейку, то можно придать ему нужный и понятный вид при помощи функции ЕСЛИ.
Чтобы быстро проверить работу ЗНАК, просто скопируйте в любую ячейку Excel следующее выражение: «=ЗНАК(10)» (конечно, без кавычек). В ответ мы получим значение 1, так как указали в качестве аргумента положительное число. Поменяв знак числа внутри круглых скобок, на выходе получим -1.
Обратите внимание, что при передаче в формулу констант аргумент внутри круглых скобок не заключается в двойные кавычки, так как это не текст, а именно число.
Вы можете самостоятельно придумать интересные практические примеры применения функции определения знака в Excel и опубликовать их в комментариях к статье в помощь другим читателям.
Смотреть видео
Функция ЗНАК в Excel
Прикреплённые документы
Файлы для загрузки
Вы можете скачать прикреплённые ниже файлы для ознакомления. Обычно здесь размещаются различные документы, а также другие файлы, имеющие непосредственное отношение к данной публикации.
В начале предлагаю вспомнить определение подстановочных знаков и понять, что же это такое и для каких целей они применяются в Excel. А затем уже разберем применение на конкретных примерах.
Подстановочные знаки — это специальные символы, которые могут принимать вид любого произвольного количества символов, другими словами, являются определенными масками комбинаций символов.
Всего в Excel есть 3 типа подобных знаков:
- * (звездочка); Обозначает любое произвольное количество символов. Например, поиск по фразе «*ник» найдет слова типа «понедельник», «всадник», «источник» и т.д.
- ? (вопросительный знак); Обозначает один произвольный символ. К примеру, поиск по фразе «ст?л» найдет «стол», «стул» и т.д.
- ~ (тильда) с последующими знаками *, ? или ~. Обозначает конкретный символ *, ? или ~. Например, поиск по фразе «хор*» найдет все фразы начинающиеся на «хор» («хоровод», «хорошо» и т.д.). Поэтому для точного поиска «хор*» нужно использовать символ «~» и искать по фразе «хор~*». Наличие «~» гарантирует, что Excel прочитает следующий символ как текст, а не как подстановочный знак.
Использование таких спецсимволов может быть полезно при фильтрации данных, для сравнения текста, при поиске и замене текстовых значений. Давайте подробно остановимся на каждом из основных вариантов применения.
Фильтрация данных
Рассмотрим пример. Предположим, что у нас имеется список сотрудников компании и мы хотим отфильтровать только тех сотрудников, у которых фамилии начинаются на конкретную букву (к примеру, на букву «п»):
Для начала добавляем фильтр на таблицу (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или нажимаем сочетание клавиш Ctrl + Shift + L).
Для фильтрации списка воспользуемся символом звездочки, а именно введем в поле для поиска «п*» (т.е. фамилия начинается на букву «п», после чего идет произвольный текст):
Фильтр определил 3 фамилии удовлетворяющих критерию (начинающиеся с буквы «п»), нажимаем ОК и получаем итоговый список из подходящих фамилий:
В общем случае при фильтрации данных мы можем использовать абсолютно любые критерии, никак не ограничивая себя в выборе маски поиска (произвольный текст, различные словоформы, числа и т.д.).
К примеру, чтобы показать все варианты фамилий, которые начинаются на букву «к» и содержат букву «в», то применим фильтр «к*в*» (т.е. фраза начинается на «к», затем идет произвольный текст, потом «в», а затем еще раз произвольный текст).
Или поиск по «п?т*» найдет фамилии с первой буквой «п» и третьей буквой «т» (т.е. фраза начинается на «п», затем идет один произвольный символ, затем «т», и в конце опять произвольный текст).
Применение в функциях
Как уже говорилось выше, подстановочные знаки в Excel могут использоваться в качестве критерия при сравнении текста в различных функциях Excel (например, СЧЁТЕСЛИ, СУММЕСЛИ, СУММЕСЛИМН, ГПР, ВПР и другие).
Повторим задачу из предыдущего примера и подсчитаем количество сотрудников компании, фамилии которых начинаются на букву «п».
Воспользуемся функцией СЧЁТЕСЛИ, которая позволяет посчитать количество ячеек соответствующих указанному критерию.
В качестве диапазона данных укажем диапазон с сотрудниками (A2:A20), а в качестве критерия укажем запись «п*» (т.е. любая фраза начинающаяся на букву «п»):
Как и в первом примере, в результате мы получили ровно 3 фамилии.
Однако не все функции поддерживают применение подстановочных знаков. Некоторые из них (к примеру, функция НАЙТИ) любой символ воспринимают как текст, даже несмотря на то, что он может быть служебным.
С помощью функции НАЙТИ найдем в тексте позицию вхождения вопросительного знака и звездочки:
Обратным примером служит аналогичная функция ПОИСК, в которой мы должно четко указать что ищем именно служебный символ:
Как видим результат у функций получился одинаковым, однако обращение к подстановочным знакам разное.
Инструмент «Найти и заменить»
Подстановочные знаки в Excel также можно использовать для поиска и замены текстовых значений в инструменте «Найти и заменить» (комбинация клавиш Ctrl + F для поиска и Ctrl + H для замены).
Рассмотрим пример. Имеется список продукции магазина, в котором нам нужно найти продукт «молоко».
Предположим, что при вводе данных сделали ошибки из-за чего в списке появились продукты «малоко».
Чтобы несколько раз не искать данные по словам «молоко» или «малоко», при поиске воспользуемся критерием «м?локо» (т.е. вторая буква — произвольная):
При этом не стоит забывать, что с помощью данного инструмента можно не только искать текст, но и заменять его (к примеру, заменить «м?локо» на «молоко»).
Как заменить звездочку «*» в Excel?
Практически наверняка каждый сталкивался со следующей ситуацией — в тексте присутствует символ звездочки, который необходимо удалить или заменить на какой-либо другой текст.
Однако при попытке заменить звездочку возникают трудности — при замене меняются абсолютно весь текст, что естественно и логично, так как Excel воспринимает символ «*» как любой произвольный текст.
Но мы теперь уже знаем как с этим бороться, поэтому в поле Найти указываем текст «~*» (явно показываем, что звездочка является специальным символом), а в поле Заменить на указываем на что заменяем звездочку, либо оставляем поле пустым, если хотим удалить звездочку:
Аналогичная ситуация и при замене или удалении вопросительного знака и тильды.
Производя замену «~?» (для тильды — «~~») мы также без проблем сможем заменить или удалить спецсимвол.
Подстановочные знаки (* и ?) можно использовать в Условном форматировании , условиях отбора Расширенного фильтра , в ряде функций, а также при поиске и замене ТЕКСТовых значений штатными средствами EXCEL.
Если имеется диапазон с текстовыми значениями и необходимо произвести поиск или подсчет этих значений на основании какого-нибудь критерия, то использование подстановочных знаков (* и ?) существенно расширяет возможности поиска.
Подстановочный знак ? (знак вопроса) означает один любой символ, а знак * (звездочка) - последовательность символов любой длины.
Используйте
Чтобы найти
Один из символов Пример: условию «бар?н» соответствуют результаты «барин», «барон» и «баран»
Любое количество символов Пример: условию «*-восток» соответствуют результаты «северо-восток» и «юго-восток»
~ (тильда), за которой следует ?, * или ~
Вопросительный знак, звездочку или тильду. Пример: по условию поиска «ан06~?» будет найдено «ан06?»
Использование в функциях
Предположим, что стоит задача подсчета ячеек, содержащих все склонения слова молоток (например, молотку, молотком, молотка и пр.). Для решения этой задачи проще всего использовать критерии отбора с подстановочным знаком * (звездочка). Идея заключается в следующем: для условий отбора текстовых значений можно задать в качестве критерия лишь часть текстового значения (для нашего случая, это – молот) , а другую часть заменить подстановочными знаками. В нашем случае, критерий будет выглядеть так – молот*.
Предполагая, что текстовые значения (склонения слова молот ) находятся в диапазоне А2:А10 , запишем формулу =СЧЁТЕСЛИ(A2:A10;"молот*")) . В результате будут подсчитаны все склонения слова молоток в диапазоне A2:A10 .
Нижеследующие функции позволяют использовать подстановочные знаки:
- СЧЁТЕСЛИ() (см. статью Подсчет текстовых значений с единственным критерием ) и СЧЁТЕСЛИМН()
- СУММЕСЛИ() и СУММЕСЛИМН()
- СРЗНАЧЕСЛИ()
- В таблице условий функций БСЧЁТ() (см. статью Подсчет значений с множественными критериями ), а также функций БСЧЁТА() , БИЗВЛЕЧЬ() , ДМАКС() , ДМИН() , БДСУММ() , ДСРЗНАЧ()
- ПОИСК()
- ВПР() и ГПР()
- ПОИСКПОЗ()
Описание применения подстановочных знаков в вышеуказанных функциях описано соответствующих статьях .
Использование в инструменте Найти…
Также подстановочные знаки можно использовать в стандартном инструменте Найти… ( Главная/ Редактирование/ Найти и выделить/ Найти… или нажать CTRL+F )
На рисунке выше показано, как найти ячейки, содержащие слово из пяти букв, первые 4 из которых фраза "дрел".
Использование в Расширенном фильтре
Использование подстановочных знаков в Расширенном фильтре описано в этой статье .
Использование в Условном форматировании
Использование подстановочных знаков в правилах Условного форматирования описано в этой статье .
Сформируем последовательность 1, 2, 3, . Пусть в ячейке A2 введен первый элемент последовательности - значение 1 . В ячейку А3 , вводим формулу =А2+1 и копируем ее в ячейки ниже (см. файл примера ).
Так как в формуле мы сослались на ячейку выше с помощью относительной ссылки , то EXCEL при копировании вниз модифицирует вышеуказанную формулу в =А3+1 , затем в =А4+1 и т.д., тем самым формируя числовую последовательность 2, 3, 4, .
Если последовательность нужно сформировать в строке, то формулу нужно вводить в ячейку B2 и копировать ее нужно не вниз, а вправо.
Чтобы сформировать последовательность нечетных чисел вида 1, 3, 7, . необходимо изменить формулу в ячейке А3 на =А2+2 . Чтобы сформировать последовательность 100, 200, 300, . необходимо изменить формулу на =А2+100 , а в ячейку А2 ввести 100.
Другим вариантом создания последовательности 1, 2, 3, . является использование формулы =СТРОКА()-СТРОКА($A$1) (если первый элемент последовательности располагается в строке 2 ). Формула =СТРОКА(A2)-СТРОКА($A$1) позволяет создать вертикальную последовательность, в случае если ее первый элемент последовательности располагается в любой строке. Тот же результат дают формулы =ЧСТРОК($A$1:A1) , =СТРОКА(A1) и =СТРОКА(H1) . Формула =СТОЛБЕЦ(B1)-СТОЛБЕЦ($A$1) создает последовательность, размещенную горизонтально. Тот же результат дают формулы =ЧИСЛСТОЛБ($A$1:A1) , =СТОЛБЕЦ(A1) .
Чтобы сформировать последовательность I, II, III, IV , . начиная с ячейки А2 , введем в А2 формулу =РИМСКОЕ(СТРОКА()-СТРОКА($A$1))
Сформированная последовательность, строго говоря, не является числовой, т.к. функция РИМСКОЕ() возвращает текст. Таким образом, сложить, например, числа I+IV в прямую не получится.
Другим видом числовой последовательности в текстовом формате является, например, последовательность вида 00-01 , 00-02, . Чтобы начать нумерованный список с кода 00-01 , введите формулу =ТЕКСТ(СТРОКА(A1);"00-00") в первую ячейку диапазона и перетащите маркер заполнения в конец диапазона.
Выше были приведены примеры арифметических последовательностей. Некоторые другие виды последовательностей можно также сформировать формулами. Например, последовательность n2+1 ((n в степени 2) +1) создадим формулой =(СТРОКА()-СТРОКА($A$1))^2+1 начиная с ячейки А2 .
Создадим последовательность с повторами вида 1, 1, 1, 2, 2, 2. Это можно сделать формулой =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/3+1) . С помощью формулы =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/4+1)*2 получим последовательность 2, 2, 2, 2, 4, 4, 4, 4. , т.е. последовательность из четных чисел. Формула =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/4+1)*2-1 даст последовательность 1, 1, 1, 1, 3, 3, 3, 3, .
Примечание . Для выделения повторов использовано Условное форматирование .
Формула =ОСТАТ(ЧСТРОК(A$2:A2)-1;4)+1 даст последовательность 1, 2, 3, 4, 1, 2, 3, 4, . Это пример последовательности с периодически повторяющимися элементами.
Используем клавишу CTRL
Пусть, как и в предыдущем примере, в ячейку A2 введено значение 1 . Выделим ячейку A2 . Удерживая клавишу CTRL , скопируем Маркером заполнения (при этом над курсором появится маленький плюсик), значение из A 2 в ячейки ниже. Получим последовательность чисел 1, 2, 3, 4 …
ВНИМАНИЕ! Если на листе часть строк скрыта с помощью фильтра , то этот подход и остальные, приведенные ниже, работать не будут. Чтобы разрешить нумерацию строк с использованием клавиши CTRL , выделите любую ячейку с заголовком фильтра и дважды нажмите CTRL + SHIFT + L (сбросьте фильтр).
Используем правую клавишу мыши
Пусть в ячейку A2 введено значение 1 . Выделим ячейку A2 . Удерживая правую клавишу мыши, скопируем Маркером заполнения , значение из A2 в ячейки ниже. После того, как отпустим правую клавишу мыши появится контекстное меню, в котором нужно выбрать пункт Заполнить . Получим последовательность чисел 1, 2, 3, 4 …
Используем начало последовательности
Если начало последовательности уже задано (т.е. задан первый элемент и шаг последовательности), то создать последовательность 1, 2, 3, . можно следующим образом:
Получаем результат как в предыдущем случае. Если в ячейке А3 введено значение 3 , т.е. задан шаг последовательности равный 2, то мы получим последовательность нечетных чисел.
Создадим последовательность вида 1, 2, 3, 1, 2, 3. для этого введем в первые три ячейки значения 1, 2, 3, затем маркером заполнения , удерживая клавишу CTRL , скопируем значения вниз.
Использование инструмента Прогрессия
Воспользуемся стандартным средством EXCEL для создания прогрессий, в то числе и арифметических.
- вводим в ячейку А2 значение 1 ;
- выделяем диапазон A2:А6 , в котором будут содержаться элементы последовательности;
- вызываем инструмент Прогрессия ( Главная/ Редактирование/ Заполнить/ Прогрессия. ), в появившемся окне нажимаем ОК.
Использование в работе : Подходы для создания числовых последовательностей можно использовать для нумерации строк , сортировки списка с числами , разнесения значений по столбцам и строкам .
СОВЕТ: О текстовых последовательностях вида первый, второй, . 1), 2), 3), . можно прочитать в статье Текстовые последовательности . О последовательностях значений в формате дат (и времени) вида 01.01.09, 01.02.09, 01.03.09, . янв, апр, июл, . пн, вт, ср, . можно прочитать в статье Последовательности дат и времен . О массивах значений, содержащих последовательности конечной длины, используемых в формулах массива , читайте в статье Массив значений (или константа массива или массив констант) .
Смотрите также придется еще и доп.библиотека,которая содержит данныеЛюдмила григорьева в шрифте Webdings) нем. Проще всего и "барон"
( легко найти с в ячейке В2 "СЧЕТЕСЛИ", читайте в множественными критериями), а соответствуют результаты «северо-восток» по коду, т.д.в написано Сергей». В словами. Смотрите нижеРазберем, шрифт тягать, регистрировать, символы и её: ещё бы видео и функцию это определить с* (звездочка)CTRL+SHIFT+F помощью стандартного поиска
«с?ла», а в статье "Функция "СЧЁТЕСЛИ" также функций БСЧЁТА(), и «юго-восток» Подробнее об этом
Excel ячейку А3 пишем описание символа функциикак написать формулу в а главное не
можно установить в )) час потерял,ПОВТОР (REPT) помощью небольшой таблицы,Любое количество символов). Например, некоторые шрифты ( ячейке В5 напишем в Excеl". Нашлось БИЗВЛЕЧЬ(), ДМАКС(), ДМИН(),~ (тильда), за которой смотрите в статье. Про разные ссылки формулу. =A1&" "&A2 «СЦЕПИТЬ».Exce забывать об этом. Excel.Или есть способ так и не, которая умеет повторять которую можно сделать
Пример: условию "*-восток" включают дроби, знакиCTRL+F такую формулу. =СЧЁТЕСЛИ(A1:A10;B2) таких 4 слова. БДСУММ(), ДСРЗНАЧ() следует ?, * «Символ в Excel». в формулах смотритеПояснение в формуле* (звездочкаl, используя символы Я бы ни самому их сделать понял, бросил. заданный символ икс себе за пару
соответствуют результаты "северо-восток" национальных алфавитов и), т.к. им соответствуютО других способах примененияВ формуле написали словоПОИСК() или ~В Excel можно в статье «Относительные - ячейку А1)что означают символы в за что не и внедрить вSalsero раз:
минут и использовать и "юго-восток" символы национальных валют специальные комбинации символов подстановочных знаков читайте «молок» и поставилиВПР() и ГПР()Вопросительный знак, звездочку или сделать не просто и абсолютные ссылки сцепить (&), пробел– обозначает формулах Excel, стал так делать Excel.: В Office ExcelЕсть база телефонов, которые потом как справочник:~ (тильда), за которой (см. статью ). (^v, ^l). В
в статье "Примеры звездочку (*) –ПОИСКПОЗ() тильду. график, диаграмму по
в Excel». (" "), сцепитьлюбое количество символов вт. д.ber$erk
ber$erk 2007 заложено большое начинаются на 095Для всех ячеек столбца следует ?, * Встроенный шрифт Symbol окне стандартного поиска функции "СУММЕСЛИМН" в это значит, чтоОписание применения подстановочных знаковПример: по условию данным таблицы, а
@ ячейку А2. текстеС какого символа: Зачем специальным программам: А может создать количество разных символов, или 099, а В установлен шрифт
или ~ содержит стрелки, маркеры EXCEL использование таких Excel". Excel будет считать в вышеуказанных функциях поиска «ан06~?» будет сделать динамический график.(знак "собака" называем по-русски,О других способах. Например, нужно найти
начинается формула в шрифт тягать? шрифт с этими
но там нет мне нужно к Arial, для столбцаВопросительный знак, звездочку или и знаки, используемые комбинаций не предусмотрено, Как найти все слова все ячейки со описано соответствующих статьях. найдено «ан06?» При добавлении данных по-английски - at сцепить слова и
фамилии на «Ро». Excel?СоВопрос, я так символами (типа Windings символов,которые используются при каждой ячейке добавить C - шрифт тильду в науке. Имеются но из ситуации
с разными окончаниями словами, которые начинаютсяТакже подстановочные знаки можноПредположим, что стоит задача в таблицу, они (эт) или at числа, даты, смотрите Мы напишем взнака равно» (=)
понимаю, стоял -)? Встроить ее разработке чертежей,а именно +38. Как это Webdings и т.д.Пример: условию "фг06~?" также дополнительные шрифты, можно выйти по-другому. в на «молок», а использовать в стандартном подсчета ячеек, содержащих все или, только, commercial) в статье «Функция формуле «Ро*». Excel
. Все формулы начинают либо шрифт, либо в комп и символы допусков формы сделать? Спасибо. С Так что одна соответствует результат "фг06?"
такие как Wingdings,Для поиска, например, спецсимволаExcel. дальше имеют разные инструменте Найти… (Главная/ все склонения слова последние (например за- «СЦЕПИТЬ» в Excel».
найдет фамилии – писать, поставив, сначала, спец.программа? я предложил вуаля и расположения. макросами пробовал не и та жеВ стандартном наборе шрифтов содержащие декоративные символы. Разрыв страницы (см.Эти подстановочные знаки окончания. Редактирование/ Найти и молоток (например, молотку, последние семь дней)
Подстановочные знаки в MS EXCEL
преобразует число в текст~ (тильда) Рогов, Роговцева, Романов, в ячейке знак шрифтKuklPКак добавить новый полается. Я очень функция
Windows есть многоПримечание: статью Вставка символа можно вставлять неСимвол «звездочка» в выделить/ Найти… или молотком, молотка и будут автоматически отражаться Excel– если нужно
т.д. «равно» - этотZ: И потом тягать набор символов к далёк от Экселя
Читайте также: