В Excel есть окно символов, которое используется для поиска и вставки специальных символов в ячейку (рис. 66.1). Вы можете открыть это окно, выбрав «Вставить символы.
На вкладке Символы в раскрывающемся списке Шрифт выберите нужный шрифт. Для большинства шрифтов можно также выбрать категорию шрифта в раскрывающемся списке «Установить». Выберите нужный символ и нажмите кнопку Вставить. Продолжайте вставлять символы, если они вам все еще нужны, или нажмите кнопку «Закрыть», чтобы закрыть окно.
Рис. 66.1. Символы из категории Технические символы Arial Unicode MS
Если вы вставили символ из определенного шрифта, Excel будет продолжать отображать один и тот же символ независимо от шрифта, используемого в ячейке. Для большого набора символов используйте шрифт Arial Unicode MS.
Если вы часто используете персонажа, вы можете сделать его более доступным, например, KNS. В Excel это делается с помощью функции автозамены. Следуя этим инструкциям, вы сделаете нужный вам символ (в нашем примере он выделен на рис. 66.1) легкодоступным.
- Выберите пустую ячейку.
- Выполните команду «Вставить символы» и используйте диалоговое окно. Символы для поиска символов, которые вы хотите использовать. В нашем примере код символа 2318, а сам символ относится к категории технических символов в шрифте Arial Unicode MS.
- Вставьте этот символ в ячейку, нажав кнопку Вставить.
- Щелкните Закрыть, чтобы закрыть диалоговое окно символа.
- Нажмите Ctrl+C, чтобы скопировать символ в активной ячейке.
- Выберите «Параметры файла», чтобы открыть окно «Параметры Excel», перейдите в раздел «Правописание» и нажмите кнопку «Параметры автозамены», чтобы открыть диалоговое окно «Автозамена» (или просто нажмите Alt+TA).
- В окне Автозамена выберите одноименную вкладку.
- В поле «Заменить» введите последовательность символов, например (p).
- Перейдите к полю и нажмите Ctrl+V, чтобы вставить специальный символ.
- Нажмите 0K, чтобы закрыть окно автозамены.
После выполнения этих шагов Excel заменяет символ при вводе (p). При выборе строки замены укажите последовательность символов, которую вы обычно не печатаете. В противном случае вы можете обнаружить, что Excel делает замены там, где они вам не нужны. Помните, что вы всегда можете нажать Ctrl+Z, чтобы отменить автозамену. Дополнительные сведения об использовании автозамены см в разделе Настройка и совместное использование автозамены в Excel».
Визуализация значками с функцией СИМВОЛ (CHAR)
В стандартном наборе шрифтов Windows есть много специальных символов, которые иногда весьма приятны и полезны для использования на листе Excel:
- всевозможные звезды для визуализации рейтинга
- различные типы стрелок, чтобы показать динамику, рост или снижение
- значки людей для иллюстрации населения или количества сотрудников
- символы на различных объектах (автомобили, самолеты, книги), чтобы показать объем продаж и т д
В Microsoft Excel аналогичный трюк можно проделать с помощью функции CHAR (СИМВОЛ), которая умеет отображать символ по его коду:
где код — это число от 0 до 255, внутренний код символов Windows.
Обратите внимание, что символы с одним и тем же кодом могут выглядеть по-разному в разных шрифтах. Поэтому для отображения нужной иконки нужно знать название шрифта и код символа в нем. Проще всего это определить с помощью небольшой таблицы, которую можно составить самостоятельно за пару минут, а затем использовать в качестве ориентира:
Все ячейки в столбце B настроены на Arial, столбец C установлен на Webdings и т д. Таким образом, одна и та же функция CHAR показывает нам символы с заданными кодами в каждом шрифте.
Обратите внимание, что первые три десятка кодов во всех шрифтах зарезервированы для системных нужд и не отображаются как значимые символы. А с остальным можно поиграть.
Подробнее: Печатающая головка Excel на каждой странице
Также можно выбрать на вкладке Вставка команду Символ (Insert - Symbol), ввести нужный шрифт и увидеть код нужного символа в нижней части окна:
Например, вот как можно визуализировать увеличение или уменьшение продаж, используя символы с кодами 233 и 234 из шрифта Wingdings и простой тест с функцией ЕСЛИ (IF) :
При желании вы можете установить цвет стрелок, используя условное форматирование.
Или можно визуально показать, например, количество сотрудников в каждом филиале компании, используя символ человечка (код 128 в шрифте Webdings) и функцию ПОВТОР, которая может повторить заданный символ х раз:
Функции СИМВОЛ ЗНАК ТИП в Excel и примеры работы их формул
В Excel часто необходимо использовать в расчетах значения, привязанные к символам, числам и типу.
Для этого используются следующие функции:
Функция СИМВОЛ позволяет получить символ с его заданным кодом. Функция используется для преобразования числовых кодов символов, полученных с других компьютеров, в символы на этом компьютере.
Функция ТИП определяет типы данных ячейки, возвращая соответствующее число.
Функция ЗНАК возвращает знак числа и возвращает 1, если оно положительное, 0, если 0, и -1, если отрицательное.
Примеры использования функций СИМВОЛ, ТИП и ЗНАК в формулах Excel
Пример 1. Дана таблица кодов символов: от 65 до 74:
Необходимо использовать функцию СИМВОЛ для отображения символов, соответствующих заданным кодам.
Для этого введите в ячейку B2 следующую формулу:
Аргумент функции: Число - код символа.
В результате расчетов получаем:
Как использовать функцию СИМВОЛ в формулах на практике? Например, нам нужно отобразить текстовую строку в одинарных кавычках. Для Excel одинарная кавычка в качестве первого символа — это специальный символ, который преобразует значение ячейки в текстовый тип данных. Поэтому в самой ячейке одиночная кавычка не появляется в качестве первого символа:
Для решения этой задачи используем следующую формулу с функцией =SYMBOL(39)
Также полезно использовать эту функцию, когда вам нужно использовать формулу для переноса строки в ячейке Excel. И для других подобных задач.
Как вы уже догадались, значение 39 в аргументе функции — это код одинарной кавычки.
Как посчитать количество положительных и отрицательных чисел в Excel
Пример 2. В таблице 3 числа. Вычислите, какой знак имеет каждое число: положительный (+), отрицательный (-) или 0.
Введем данные в таблицу с формой:
Вводим формулу в ячейку E2:
Аргумент функции: Число — любое действительное числовое значение.
Копируя эту формулу вниз, мы получаем:
Сначала посчитаем количество отрицательных и положительных чисел в столбцах «Прибыль» и «ЗНАК»:
А теперь суммируем только положительные или только отрицательные числа:
Как сделать отрицательное число положительным, а положительное число отрицательным? Умножить на -1 очень просто:
Формулу можно упростить еще больше, просто добавив перед ссылкой на ячейку знак оператора вычитания - минус:
Но что, если вам нужно сделать число с положительным знаком? Затем необходимо использовать функцию ABS. Эта функция возвращает любое число по модулю:
Теперь не сложно догадаться, как составить число со знаком минус:
Проверка какие типы вводимых данных ячейки в таблице Excel
Пример 3. Используйте функцию ТИП для отображения типа данных, введенных в таблицу, в виде:
Функция ТИП возвращает код типов данных, которые можно ввести в ячейку Excel:
Подстановочные знаки (символы *, ? и
Рассмотрим использование подстановочных знаков в Excel (звездочка «*», тильда «
' и вопросительный знак '?') и их использование при поиске и замене текстовых значений.
Подробнее: Расчет кту в формате excel бесплатно
Приветствую всех, уважаемые читатели блога TutorExcel.Ru.
В начале предлагаю запомнить определение подстановочных знаков и понять, что они из себя представляют и для каких целей используются в Excel. А затем разберем приложение на конкретных примерах.
Подстановочные знаки — это специальные символы, которые могут принимать форму любого произвольного количества символов, другими словами, это определенные маски комбинаций символов.
Всего в Excel существует 3 типа таких символов:
- * (звездочка); Задает любое произвольное количество символов. Например, если вы ищете фразу «*псевдоним», вы найдете такие слова, как «понедельник», «всадник», «источник» и т д
- ? (вопросительный знак); Указывает произвольный символ. Например, если вы ищете термин «стул», вы найдете «стол», «стул» и т д
(тильда), за которой следуют *, ? или
. Указывает на конкретный символ *, ? или
. Например, если вы ищете фразу «кор*», вы найдете все фразы, начинающиеся с «кор» («хоровод», «добро» и т д.). Поэтому для поиска именно «кор*» необходимо использовать символ «
"и поиск по фразе" кор
' гарантирует, что Excel читает следующий символ как текст, а не как подстановочный знак.
Использование таких спецсимволов может быть полезно при фильтрации данных, для сравнения текста, при поиске и замене текстовых значений. Рассмотрим подробнее каждое из основных приложений.
Фильтрация данных
Рассмотрим пример. Предположим, у нас есть список сотрудников компании и мы хотим отфильтровать только тех сотрудников, чья фамилия начинается с определенной буквы (например, с буквы «р»):
Для начала добавьте в таблицу фильтр (выберите вкладку Главная -> Редактирование -> Сортировка и фильтрация или нажмите сочетание клавиш Ctrl+Shift+L).
Для фильтрации списка используем звездочку, а именно вводим в поле поиска "n*" (т.е фамилия начинается с буквы "p", после которой следует любой текст):
Фильтр определил 3 фамилии, соответствующие критериям (начинающиеся на букву "р"), нажимаем ОК и получаем окончательный список подходящих фамилий:
В общем случае при фильтрации данных мы можем использовать абсолютно любые критерии, никак не ограничивая себя в выборе маски поиска (произвольный текст, разные словоформы, числа и так далее).
Например, чтобы показать все варианты фамилий, которые начинаются с буквы "к" и содержат букву "в", то используйте фильтр "к*в*" (т.е выражение начинается на "к", затем идет свободный текст, затем «в», а затем снова произвольный текст).
Или поиск по "n?t*" найдет фамилии с первой буквой "p" и третьей буквой "t" (т.е фраза начинается с "p", затем следует один произвольный символ, затем "t", и на конец опять произвольный текст).
Применение в функциях
Как упоминалось выше, подстановочные знаки в Excel можно использовать в качестве критерия при сравнении текста в различных функциях Excel (например, СЧЁТЕСЛИ, СУММЕСЛИ, СУММЕСЛИМН, ГПР, ВПР и других).
Повторим задачу из предыдущего примера и посчитаем количество сотрудников в компании, чья фамилия начинается на букву «р».
Воспользуемся функцией СЧЁТЕСЛИ, которая позволяет подсчитать количество ячеек, удовлетворяющих заданным критериям.
В качестве диапазона данных укажем диапазон сотрудников (A2:A20), а в качестве критерия укажем запись "p*" (т.е любое предложение, начинающееся с буквы "p»):
Как и в первом примере, у нас получилось ровно 3 фамилии.
Однако не все функции поддерживают подстановочные знаки. Некоторые из них (например, функция НАЙТИ) воспринимают любой символ как текст, даже если он может быть служебным.
С помощью функции НАЙТИ находим место вхождения в текст знака вопроса и звездочки:
Обратный пример — аналогичная функция ПОИСК, где мы должны явно указать, что ищем служебный символ:
Как видите, результат функций оказался одинаковым, но использование подстановочных знаков отличается.
Инструмент «Найти и заменить»
Подстановочные знаки в Excel также можно использовать для поиска и замены текстовых значений в инструменте «Найти и заменить» (комбинация клавиш Ctrl+F для поиска и Ctrl+H для замены).
Рассмотрим пример. Есть список продуктов магазина, где нам нужно найти товар «молоко».
Предположим, что были допущены ошибки в данных, из-за которых в списке появилось «мало» товаров».
Чтобы не искать несколько раз данные со словами «молоко» или «лайт», при поиске будем использовать критерий «молоко? Локо» (т.е вторая буква произвольная):
При этом не забывайте, что с помощью этого инструмента можно не только искать текст, но и заменять его (например, заменять «молоко» на «молоко»).
Как заменить звездочку «*» в Excel?
Практически каждый сталкивался со следующей ситуацией - в тексте есть символ звездочки, который нужно удалить или заменить другим текстом.
А вот при попытке заменить звездочку возникают сложности - при замене меняется абсолютно весь текст, что естественно и логично, так как Excel воспринимает символ "*" как любой произвольный текст.
Но теперь мы уже знаем, как с этим обращаться, поэтому в поле «Найти» вводим текст «
*» (явно показывает, что звездочка — это специальный символ), а в поле Заменить на указываем, на что мы заменяем звездочку, или оставляем поле пустым, если хотим удалить звездочку:
Аналогичная ситуация, когда вы заменяете или удаляете вопросительный знак и тильду.
Заменив «
? (для тильды — «
»), мы также можем без проблем заменить или удалить специальный символ.
Как вставить в Excel символ: функции КОДСИМВ и СИМВОЛ
Здравствуйте дорогие читатели. Все символы, используемые Microsoft Excel, хранятся в таблицах символов ANSI и UNICODE. Они содержат обычные символы кириллицы, символы латиницы, цифры в разных способах представления, а также специальные символы (например, стрелки, греческий алфавит, другие символы). Как правило, на клавиатуре нет определенных символов, и для их вставки приходится использовать функции.
Чтобы вставить определенный символ, вы можете использовать команду ленты Вставка - Символы - Символ. Выберите нужный элемент и дважды щелкните по нему.
Вставка символа с помощью ленты
Если вы используете символ, которого нет на клавиатуре, вы можете использовать функции Excel. Например, я помню коды символов, которые часто использую.
Чтобы определить код символа, используйте функцию CHARCODE. Выполнение следующей операции даст результат «62»:
То есть знак «больше» в таблице символов находится под номером 62. Это число также можно увидеть в окне «Символ» в поле «Код знака» (см рисунок).
Использование функции КОД
Чтобы вставить символ и узнать код, мы используем функцию CHAR:
Функция СИМВОЛ в Excel
Результатом выполнения этой функции будет символ «>» в ячейке. Таким образом, вы можете получить все символы из таблицы кодирования. Это полезно, когда вставка символа является частью более крупной условной формулы.
потренируйтесь в использовании функций CODE и CHAR, необходимость в их использовании может возникнуть в любой момент, вы должны быть к этому готовы.
Кстати, если вы чувствуете, что не очень хорошо подготовлены к написанию формул и использованию функций, вам будут интересны статьи о написании формул и использовании функций в Excel.
А в следующем посте я расскажу, как сравнить две текстовые строки. Почему эти функции нуждаются в глубоком изучении? Потому что правильное сравнение данных может заменить ручной поиск строк и сэкономить ваше время. Присоединяйтесь, пригодится!
