Как из текста сделать формулу в excel

Работа с текстом в Excel

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

Функция ТЕКСТ в Excel

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

Для определения формата следует предварительно клацнуть по значению правой кнопкой мышки – и в выпадающем меню выбрать одноименную опцию, либо нажать сочетание клавиш Ctrl+1. Перейти в раздел «Все…». Скопировать нужный формат из списка «Тип».

Чтобы понять, как работает функция, необходимо открыть книгу и создать таблицу с данными.

1. Кликнуть по любому свободному месту, например, G Ввести знак «=» и ссылку на адрес ячейки – B2. Активировать Мастер функций, нажав на кнопку fx (слева) во вкладке «Формулы», или с помощью комбинации клавиш Shift+F3.
   
2. На экране отобразится окно Мастера. В строке поиска ввести название функции и нажать «Найти».
   
3. В списке нужное название будет выделено синим цветом. Нажать «Ок».
   
4. Указать аргументы: ссылку на число и скопированное значение формата. В строке формулы после B2 вписать знак «&».
   
5. В результате появится сумма в денежном формате вместе с наименованием товара. Протянуть формулу вниз.
   

Еще один пример.

Необходимо объединить текстовые и числовые значения с помощью формулы =A14&» «&»составляет»&» «&B14&»,»&» «&A15&» «&ТЕКСТ(B15;»ДД.ММ.ГГ;@»).

Таким образом любые данные преобразовываются в удобный формат.

Объединение ячеек в Excel

Чтобы объединить ячейки в одну строку без потери данных, можно использовать или знак «&», или функцию СЦЕПИТЬ (в новых версиях – ОБЪЕДИНИТЬ).

=СЦЕПИТЬ(ячейка_1;» «;ячейка_2;» «;ячейка_3;» «)

Пустые кавычки (» «) – это пробел. Без пробелов слова будут отображаться слитно. С помощью кавычек в формулу добавляется любой знак («.» или «,») и текст.

1. Следует установить курсор в месте, где будет отображаться готовое значение.
2. Написать формулу.
3. Нажать «Enter».
   

Второй вариант формулы тоже простой: =ячейка1&» «&ячейка2&» «&ячейка3.

Если в результате строка будет содержать 32767 символов и более, появится ошибка #ЗНАЧ!.

Перенос текста в ячейке в Excel

Перенести текст внутри одной ячейки на следующую строку можно несколькими способами:

1. Выделить ячейку и кликнуть по опции «Перенос…», которая расположена во вкладке «Главная».
   
2. Щелкнуть правой кнопкой мышки по выделенной ячейке, в выпадающем меню выбрать «Формат…». В появившемся на экране окне перейти на вкладку «Выравнивание», поставить галочку в поле «Переносить по словам». Сохранить изменения, нажав «Ок».
   
3. При наборе текста перед конкретным словом зажать комбинацию клавиш Alt+Enter – курсор переместится на новую строку.
4. С помощью функции СИМВОЛ(10). При этом нужно объединить текст во всех ячейках, а поможет сделать это амперсанд «&»: =A1&B1&СИМВОЛ(10)&A2&B2&СИМВОЛ(10).
   
5. Также вместо оператора «&» можно использовать функцию СЦЕПИТЬ. Формула будет иметь вид: =СЦЕПИТЬ(A2;» «;СИМВОЛ(10);B2;» «;C2;» «;D2;СИМВОЛ(10);E2;СИМВОЛ(10);F2).
   

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

Какой бы метод не был выбран, текст распределяется по ширине столбца. При изменении ширины данные автоматически перестраиваются.

Количество символов в ячейке в Excel

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

Функция работает только с одним значением.

1. Выделить ту ячейку, где будет показан подсчет.
2. Вписать формулу, указывая ссылку на адрес определенной ячейки.
3. Нажать «Enter».
4. Растянуть результат на другие строки или столбцы.
   

Можно посчитать общее количество символов:

1. Выделить все значения, во вкладке «Главная» на панели справа найти инструмент «Сумма».
   
2. Кликнуть по одноименной опции. Рядом (под или с боковой стороны от выделенного диапазона) отобразится результат.
   

В разбросанных ячейках

В этом случае необходимо воспользоваться ДЛСТР и суммированием:

1. Установить курсор в желаемом месте.
2. Ввести формулу =ДЛСТР(значение1)+ДЛСТР(значение2)+ДЛСТР(значение3) и т.д.
3. Нажать «Enter».
   

Как преобразовать текст в число

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

Когда возле значения (в левом углу сверху) есть зеленый треугольник, значит, где-то допущена ошибка. Существует несколько легких способов преобразования:

1. Через меню «Ошибка». Если кликнуть по значению, слева появится значок с восклицательным знаком. Нужно навести на него курсор, клацнуть правой кнопкой мышки. В раскрывшемся меню выбрать вариант «Преобразовать в число».
   
2. Используя простое математическое действие – прибавление / отнимание нуля, умножение / деление на единицу и т.п. Но необходимо создать дополнительный столбец.
   
3. Добавив специальную вставку. В пустой ячейке написать цифру 1 и скопировать ее. Выделить диапазон с ошибками. Кликнуть по нему правой кнопкой мышки, из выпадающего меню выбрать «Специальную вставку». В открывшемся окне поставить галочку возле «Умножить». Нажать «Ок».
   
4. При помощи функций ЗНАЧЕН (преобразовывает текстовый формат в числовой), СЖПРОБЕЛЫ (удаляет лишние пробелы), ПЕЧСИМВ (удаляет непечатаемые знаки).
   
5. Применив инструмент «Текст по столбцам» к значениям, которые расположены в одном столбце. Нужно выделить все числовые элементы, во вкладке «Данные» найти указанную опцию. В открывшемся окне Мастера нажимать далее до 3-го шага – проверить, какой указан формат, при необходимости – изменить его. Нажать «Готово».
   

Как преобразовать дату в текст

Функция ТЕКСТ позволяет определить день недели по дате:

1. Кликнуть по ячейке.
2. Написать формулу =ТЕКСТ(значение;»ДДДД»).
   
3. Нажать «Enter».
4. Протянуть формулу вниз до последней даты.
   

Для представления даты в текстовом виде используется формула =ТЕКСТ(значение;»ДД МММ ГГГГ»).

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

Как использовать формулы в таблицах Excel

Excel — это одна из программ пакета Microsoft office, созданного для производства различных документов. Эксель позволяет создавать табличные документы, в нем удобно делать и самые простые вычисления, используя как калькулятор, и сложные расчеты, такие как, например, строительные сметы.

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

Как вставить формулу в ячейку

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

Ввести значения: числа и алгебраические знаки (в этом случае после нажатия клавиши «Ввод» в выбранном поле появится результат алгебраического действия) или значения из других полей, тогда необходимо выделить эти поля и поставить необходимый алгебраический знак между ними. Например, в выделенном поле нам необходимо получить сумму стоимостей единицы Товара 5 и Товара 8. В таком случае после постановки знака «=» необходимо выделить поле С6, потом поставить «+», а затем выделить С9.

Затем нажимает «Ввод», в поле автоматически появляется результат сложения двух значений.

В Экселе используются стандартные математические символы:

• + — сложение;
• — — вычитание;
• * — умножение;
• / — деление;
• ^ — степень.

Как создать таблицу с формулами

Функционал Экселя позволяет не просто вычислять переменные для каждого конкретного поля, но и задавать алгоритмы заполнения их множества одновременно.

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

Чтобы рассчитать общую стоимость каждого наименования товаров, необходимо стоимость единицы каждого из них умножить на количество единиц этого товара. Вычислим общую стоимость Товара 1. Для этого выделяем поле D2, ставим знак равенства, выделяем В2, прописываем алгебраический символ умножения «*» и выделяем С2.

В поле появился алгоритм вычисления полной стоимости, после нажатия кнопки ввода в нем отразится искомое число.

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

После отпускания клавиши мыши в захваченный полях появятся искомые значения.

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

Алгоритм автоматически составился для конкретной строки, прописав в нее новые координаты. Если необходимо, чтобы при таком действии одна или несколько ячеек из первоначального алгоритма оставались неизменными, их необходимо обозначить как постоянные.

Как обозначить постоянную ячейку

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

Например, при исчислении общих стоимостей товаров мы закрепим как постоянную ячейку со стоимостью единицы Товара 1. Для этого перед С2 в алгоритме прописываем знак $, указать его необходимо как перед буквой, так и перед цифрой в обозначении.

Исчислим результат для Товара 1 и растянем алгоритм на весь столбец товаров, как это описано выше. Итоговые значения в строках будут иными.

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

Примеры формул в Экселе

Эксель дает возможность создавать самые разнообразные алгоритмы и проводить сложные операции не только с числами, но и текстом. Рассмотрим некоторые из них.

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

Сформируем цельные даты в столбце D. Для этого выделим D2, а в меню формул выбираем «ДАТА».

В появившемся окне впишем наименования ячеек, в которых содержится информация о годе, месяце и дне, это А2, В2 и С2 соответственно.

После нажатия кнопки «ОК» в выделенном поле появится дата.

Формулу распространяем на ячейки ниже в столбце, растянув D2, после чего появятся даты.

Текст

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

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

Выделим верхнюю ячейку этого столбца — Е2 и выберем в меню формул «ТЕКСТ».

В появившемся окне заполним строки: значение — это координаты, откуда взято число, — D2, формат — текстовое обозначение — #руб.

После нажатия кнопки «ОК» в ячейке появится итоговая цифра с наименованием валюты.

Действие распространяем на ячейки ниже.

Автосумма

Функция автосуммы позволит быстро вычислить общую сумму значений выбранных ячеек.

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

Нажимаем значок автосуммы в разделе формул, итоговая цифра появится в пустой захваченной ячейке.

Другие примеры

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

Эксель — это наиболее полный и удобный сервис по работе с таблицами, но некоторые функции предоставляет и Ворд.

В excel преобразовать текст в формулу

​Смотрите также​​ выделил юзер при​ рабочего времени не​ сохранить. Но книга​ в число».​: Спасибо​ только в Excel​sgkorolew​ форматирования (см. здесь). ​ В Microsoft Office​ — формат даты.​»Табельный номер» («000-00-0000»).​ «# ##0,00 ₽;​»# ;»​ТЕКСТ​ которые вы найдете​)​Примечание:​ вводе формулы. И​ Ваш вопрос был?​ Excel должна быть​

​Применить любые математические действия.​​kolek291​​ 2013)​, здравствуйте, как вариант​Одной из самых полезных​​ MVP у Mynda​​К сожалению, это невозможно​Дополнительные форматы зависят от​[Красный]​, число 12200000 отображается​.​ в этой статье,​

​Денежный формат с разделителем​​ Мы стараемся как можно​ человек просил без​ Посторался что-то сделать.​ сохранена в формате​ Подойдут простейшие операции,​: Всем привет! Есть​3. Получить текст​ — формат ячейки​ свойств функции ТЕКСТ()​ Treacy) есть отличное​ сделать с помощью​ региональных параметров. Если​# ##0,00 ₽», то​ как 12 200 000.​Выбор числового формата​

Технические сведения

​ а также некоторые​

​ групп разрядов и​​ оперативнее обеспечивать вас​

​ знака =, так​​User​​ с поддержкой макросов.​

​ которые не изменяют​

​ текст в ячейки​

​ формулы с помощью​

​текстовый​ является возможность отображения​

​ решение в этой​

​ функции​ же дополнительные форматы​ функция ТЕКСТ воспримет​

Общие сведения

​Пробел после заполнителя цифры​ ​

​ дополнительные. Вы можете​ двумя разрядами дробной​ актуальными справочными материалами​

​ что без mid​: Не не не. ​Теперь возвращаемся на страницу​ результат (умножение /​ =’\\G77pavfs01\Paveletskiy_IB0\ЦООП\электронный журнал ЦООП\2016\ноябрь​ настроек: Файл -​AlexM​

​ в текстовой строке​

​ статье, которое отображается​

​текст​​ недоступны для вашего​​ его, но цвет​

​ задает деление числа​Выбор числового формата​ подписаться на них​ части, например: 1 234,57 ₽.​ на вашем языке.​ или replace не​ К сумме прописью​ с цифрами. Выделяем​

​ деление на единицу,​​ 2016\[Магистральный.xls]22′!E10, как мне​​ Параметры — Дополнительно​

​: Сначала формат, потом​ чисел и дат​

​ в разных языках​​, поэтому необходимо​​ региона или не​

​ отображаться не будет.​ на 1000. Например,​

​Нули в начале​​ или создать собственные​​ Обратите внимание: Excel​

​ Эта страница переведена​

​ обойтись​​ это не имеет​​ столбец с данными.​

​ сделать так, чтобы​​ — Показывать формулы,​​ вставить формулу.​

​ в нужном формате​

​ . В нем​​ использовать код Visual​​ подходят для ваших​

​Способ отображения дат можно​ если используется код​Разделитель групп разрядов.​ коды форматов функций​ округляет значение до​

​ автоматически, поэтому ее​​Igor67​​ отношения. Постарался описать​

​ Нажимаем кнопку «Макросы».​

​ нуля, возведение в​​ этот текст стал​

​ а не их​Если наоборот, то​

​ (см. подробнее об​​ также содержится пример​​ Basic для приложений​

​ нужд, вы можете​ изменять, используя сочетания​

​ формата​​Числовые, денежные и финансовые​​ текста. Примеры загрузки​

​ двух разрядов дробной​ текст может содержать​: To kaa, возвращает​

​ точнее в файле.​​ В открывшемся окне​ первую степень и​ формулой и скопировал​ значения​ значение полученное формулой​ отображении чисел, дат​ книги, которую можно​​ (VBA). Вот как​​ создать собственный формат,​​ символов «Д» (для​​»# ;,0 «​ форматы​ текстовых функций в​ части.​​ неточности и грамматические​​ формулу ВБА, добавил​​Guest​​ – список доступных​

Скачивание образцов

​ т.д.).​ данные ячейки Е10​4. Получить текст​ преобразуется в текст.​ и времени). В​ загрузить.​ можно преобразовать числовое​ выбрав в диалоговом​ дня), «М» (для​, число 12200000 отображается​Даты​ Excel​=ТЕКСТ(СЕГОДНЯ();​

Другие доступные коды форматов

​ ошибки. Для нас​ локал, после нормально​: Igor, ему просто​​ для данной книги​​Добавить специальную вставку. Здесь​

​ в новую книгу. ​​ формулы с помощью​​sgkorolew​ ​ файле примера приведен​​Создание и удаление пользовательских​ значение в английские​​ окне​​ месяца) и «Г»​

​ в Excel как​​Значения времени​​Просмотреть другие доступные коды​

​»ДД.ММ.ГГ»​​ важно, чтобы эта​​ на русской а​

​ нужно в какой-либо​ макросов. Выбираем нужный.​​ также применяется простое​​AlexM​ VBA​: buchlotnik, меняю форматы​​ наглядный пример: с​​ числовых форматов​ слова в Excel​Формат ячеек​ (для года).​ 12 200,0.​Проценты​

​ форматов можно в​​)​​ статья была вам​ не аглицкой мове.​ ячейке получить текст,​​ Жмем «Выполнить».​​ арифметическое действие. Но​: Функция ДВССЫЛ() Возвращает​​В этих вариантах​​ на текстовый -​

​ форматированием и без​​Преобразование чисел из текстового​​ с помощью следующей​пункт​В функции ТЕКСТ коды​​Примечания:​​Дроби​ диалоговом окне​Сегодняшняя дата в формате​ полезна. Просим вас​Igor67​ который отображается в​

Коды форматов по категориям

​ в ячейке сохраняются​ форматирования.​ формата в числовой​ ссылки:​(все форматы)​​ форматов используются без​​ ​​Экспоненциальное представление​​Формат ячеек​​ ДД/ММ/ГГ, например: 14.03.12​​ уделить пару секунд​: По первой части​​ строке формул для​​Следовательно, значения в ячейках​

• ​ работать (вычислять), но​
• ​ результаты вычислений. Вроде​
• ​В файле примера приведено​
• ​Функции Excel (по категориям)​Да, вы можете использовать​
• ​.​
• ​ учета регистра, поэтому​
• ​Разделитель групп разрядов зависит​
• ​Дополнительные форматы​
• ​.​
• ​=ТЕКСТ(СЕГОДНЯ();​

​ и сообщить, помогла​ вопроса понял, погорячился,​

​ ячейки А3. Так​ «стали» числами.​ отдельной ячейке написать​Это не понятно.​ можно посмотреть и​ не вариант.​ также множество других​Функция ТЕКСТ(), английская версия​ функции ПРОПИСН, СТРОЧН​Функция​ допустимы символы «М»​ от региональных параметров.​Почему программа Excel удаляет​Нажмите клавиши​»ДДДД»​ ли она вам,​ был не прав​​ я понял суть​​Если в столбце встречаются​ цифру 1. Скопировать​​kolek291​​ текст формул​​AlexM, апостроф -​​ форматов.​​ TEXT(), преобразует число​​ и ПРОПНАЧ. Например,​текст​

​ и «м», «Д»​ Для России это​ нули в начале?​CTRL+1​)​ с помощью кнопок​​ не проинтуичил.​​ его вопроса.​ аргументы с определенным​ ячейку в буфер​​: ДВССЫЛ не работает,​​Если же надо​ это решение. Только​roman66rus​ в текст и​ формула =ПРОПИСН(«привет») возвращает​редко используется сама​

​ и «д», «Г»​ пробел, но в​В Excel обрабатываются Поиск​(на компьютере Mac —​Сегодняшний день недели, например:​ внизу страницы. Для​​По второй -​​Лузер​ числом десятичных знаков​ обмена (с помощью​​ вот файл​​ именно преобразовать формулу​

​ вот как быть,​: Доброго времени суток.​ позволяет задать формат​ результат «ПРИВЕТ».​ по себе и​ и «г».​ других странах и​​ чисел, введенных в​​+1​ понедельник​

​ удобства также приводим​ со временем Ваш​: это решается простой​ (например, 3,45), можно​ кнопки «Копировать» или​​Казанский​​ в её текст,​ если ячеек с​Возможно ли преобразовать​

​ отображения с помощью​​Да, но это займет​

​ чаще всего используется​Минда советует. ​ регионах может использоваться​ ячейки, а не​), чтобы открыть диалоговое​=ТЕКСТ(ТДАТА();​ ссылку на оригинал​

​ пост был?​ пользовательской функцией​ использовать другой макрос.​ сочетания клавиш Ctrl​

​:​ т.е. «убить» формулу,​ разными формулами очень​ текст в формулу​ специальных строк форматирования,​ несколько этапов. Сначала​ в сочетании с​Если вы предоставляете доступ​ запятая или точка.​ чисел, которые выглядят​ окно​»ЧЧ:ММ»​ (на английском языке).​kaa​User​Sub Conv() With​ + C). Выделить​kolek291​ а на её​ много? Не могу​ ?​ например, формула =ТЕКСТ(100;»0,00​ выделите одну или​ другими функциями. Предположим,​ к файлам и​Разделитель групп разрядов можно​ как текст, например​Формат ячеек​

​)​С помощью функции​: согласен​: Лузер​​ ActiveSheet.UsedRange .Replace «,»,».»​​ столбец с редактируемыми​ ​, да, ДВССЫЛ не​​ место поместить её​ сообразить как подставить​Пробовал ctrl+F3 ВЫЧИСЛИТЬ()​​ р.») вернет текстовую​​ несколько ячеек, которые​

​ что вам нужно​​ отчетам Excel пользователям​​ применять в числовых,​​ номера частей или​​.​Текущее время, например: 13:29​ТЕКСТ​

​User​​Буду вам очень​ arr = .Value​ числами. В контекстном​ работает с закрытыми​ текст, то варианты​ апостроф во все​ но выдает #ССЫЛКА​ строку 100,00 р.,​​ должны быть выполнены,​​ объединить текст и​ из разных стран,​ денежных и финансовых​ единицы СКЛАДского учета.​

​На вкладке​=ТЕКСТ(0,285;​можно изменить представление​: Аффигеть! Спасибо большое!​ благодарен, если вы​ .NumberFormat = «General»​

​ меню кнопки «Вставить»​ книгами.​ такие:​ ячейки автоматом.​ хотя у меня​ а формула =ТЕКСТ(1;»#%»)​ а затем с​

​ числовое значение, например​

​ вам может потребоваться​ форматах.​ Чтобы сохранить начальные​Число​»0,0 %»​ числа, применив к​Как все оказывается​ скажете, как эту​ .Value = arr​ нажать «Специальная вставка».​Либо с помощью​1. Установить текстовый​buchlotnik​ там и есть​ вернет текст (не​ помощью​ «отчет напечатано: 03/14/12»,​

​ предоставить им отчет​Ниже показаны примеры стандартных​ нули, отформатируйте входной​выберите нужный формат.​)​ нему форматирование с​ просто! А я​ функцию написать.​ End With End​ В открывшемся окне​

​ VBA задать формулу​ формат ячеек и​:​ ссылка на ячейку​

​ число!) 100%. Эта​клавиш CTRL + 1​ или «доход за​ на их языке.​ числовых (только с​ диапазон как текст,​Выберите пункт​Процентный формат, например: 28,5 %​

​кодами форматов​ с час копался​Sh_Alex​

​ Sub​ установить галочку напротив​ [e12].Formula = [e12].Value,​ перевычислить формулы​

​sgkorolew​ в др.документе, нужно​ функция полезна, если​откройте диалоговое окно​ неделю: $66 348,72″.​ В Microsoft Office​ разделителем групп разрядов​ прежде чем вставлять​(все форматы)​= Текст (4.34,​

​. Это полезно в​ в хелпе, пытаясь​

​Читайте так же: как​

​ «Умножить». После нажатия​

​ либо использовать макрофункцию​

​2. Вставить апостроф​, по поводу смены​ значение от туда.​ числа требуется отобразить​Формат > ячейки​ В Excel можно​ MVP у Mynda​ и десятичными знаками),​ или вводить значения.​.​​»# _км_/_км_»​​ ситуации, когда нужно​​ выудить, какое же​​ эту задачу можно​

Типичный сценарий

​ перевести число и​​ ОК текстовый формат​​ ВЫЧИСЛИТЬ.​ перед знаком «​ формата — см.​Помогите если возможно,​ в более удобном​, нажмите кнопку выравнивание​ вводить текст вручную,​ Treacy) есть отличное​ денежных и финансовых​ Выделите столбец или​Нужный код формата будет​)​ отобразить числа в​ свойство отвечает за​ решить простой пользовательской​ сумму прописью в​ преобразуется в числовой.​kolek291​=​ сообщение №4.​ может макрос ..​ формате или если​ и выберите​ но это не​ решение в этой​ форматов. В денежном​​ диапазон, в котором​​ показан в поле​Дробный формат, например: 4 1/3​ удобочитаемом виде либо​ формулу — так​ функцией. У меня​​ Excel.​​Удаление непечатаемых символов. Иногда​​: огромное спасибо. Все​​» (равно)​

​Замена автоматом -​Karataev​ требуется объединить числа​элемент Управление текстом​ отменяет назначение приложения​​ статье, которое отображается​​ формате можно добавить​ вы хотите поместить​​Тип​​=СЖПРОБЕЛЫ(ТЕКСТ(0,34;​​ объединить их с​​ нифига и не​ такое впечатление, что​Скачать примеры преобразования текста​​ числовой формат не​ получилось. ​​3. Заменить формулу​

​ выделяете нужные ячейки,​: Для преобразования текста​ с текстом или​> установите флажок​ Excel. К сожалению,​ в разных языках​ нужное обозначение денежной​ значения, а затем​

​»# ?/?»​​ текстом или символами.​​ нашел. Решил обратиться​

Вопросы и ответы

Как преобразовать числа в текст, например 123 в «сто двадцать три»?

​ и непростой пользовательской​ в число.​ распознается программой из-за​​При импорте файлов или​​ на её текст​ ctrl+H (заменить) и​ в адрес есть​ символами.​ переносить​ если вы объединяете​ . В нем​ единицы, и значения​ нажмите​

Можно ли изменить регистр текста?

​ выделите всё содержимое​))​Примечание:​ к специалистам. Правильно​ функцией эта задача​

Можно ли с помощью функции ТЕКСТ добавить новую строку (разрыв строки) в ячейке, как при нажатии клавиш ALT+ВВОД?

​Таким образом, возникающую при​ невидимых символов. Удалим​ копировании данных с​ с помощью VBA​ делаете замену » ‘ ДВССЫЛ», но она​ТЕКСТ(значение; формат)​. Затем направьте функцию​ числа, например даты,​ книги, которую можно​ нему. В финансовом​, чтобы перейти в​

​ всяком случае, я​ числовых данных ошибку​ формулы, которую введем​ возникает проблема: число​: TimSha, скрин просто​sgkorolew​​ не работает, если​​Значение.​ «завершенный​ время, денежные единицы​​ загрузить.​​ формате символ рубля​ диалоговом окне​, кроме точки с​ СЖПРОБЕЛЫ используется для​ может затруднить их​ с этим оффтопный​ не смог этого​ легко устранить. Преобразовать​​ во вспомогательный столбец.​​ преобразуется в текст.​

Даты на разных языках

​: Если в ячейку​ файл, из которого​ Численное значение или​текст​ и т. д.,​Способ отображения времени можно​ располагается в ячейке​Формат > ячейки​ запятой (;) и​ удаления начального пробела​ использование в дальнейших​ вопрос. Кто-нибудь знает​ сделать.​ текст в число​ Функция ПЕЧСИМВ удаляет​ В результате формулы​andreyglad​

См. также

​ с тектовым форматом​ нужно извлечь данные,​

​ ссылка на ячейку,​», чтобы она​

Функция ТЕКСТ() в MS EXCEL

​ изменить с помощью​ справа от значения​, а затем на​ символа @. В​ перед дробной частью.​ вычислениях. Рекомендуем сохранить​ сайт или книгу,​А вот макросом​ можно разными способами​ непечатаемые знаки. СЖПРОБЕЛЫ​ не работают, вычисления​: Подскажите пожалуйста, как​ вставить формулу, то​ закрыт.​ содержащую численное значение.​ включала функцию ASCII​ как они должны​ сочетаний символов «Ч»​ (если выбрать обозначение​

​вкладке Число​

​ примере ниже выделен​

• ​=ТЕКСТ(12200000;​​ исходное значение в​ где были бы​ это возможно. Посмотрите​
• ​ (с помощью макросов​​ – лишние пробелы.​ становятся невозможными. Как​ превратить текст в​ да — формула​=ДВССЫЛ(«‘U:\Glz\Табель 1 участок\2017\[Табель уч​Формат.​char (10)​ отображаться, и сбрасывает​ (для часов), «М»​ доллара США, то​выберите​

​ и скопирован только​»0,00E+00″​ одной ячейке, а​ простым перечислением написаны​ прикрепленный файл.​ и без них).​ Функция ЗНАЧЕН преобразует​ это быстро исправить?​

​ формулу?​ будет в виде​ № 1 2017.xls]»&СТРОЧН(ТЕКСТ(СЕГОДНЯ();»[$-419]ММММ​ Текстовая строка определенного​там, где нужно​ форматирование чисел. В​ (для минут) и​ эти символы будут​текст​ код ДД.ММ.ГГГГ.​)​ функцию ТЕКСТ использовать​ операторы VBA. Ну​С уважением, Александр.​​ Сделать это просто​​ текстовый формат в​ Сначала посмотрим, как​​Ячейки: Q1 =​​ текста. Но прежде​​ ГГГГ;@»))&»‘»&»!»&»$B2″)​​ вида (например, «Д.М.ГГГГ»​ вставить разрыв строки.​

​ этом случае функция​ «С» (для секунд).​ выровнены по левому​. Теперь Excel сохранит​Нажмите клавиши​Экспоненциальное представление, например: 1,22E+07​ в другой. Затем,​ что то типа:​Лузер​ и быстро. А​ числовой.​ исправить ошибку без​ 2; H1 =​

​ чем вставить формулу,​Michael_S​ для вывода числа​

Преобразовать текст в формулу (Формулы/Formulas)

​ Возможно, вам потребуется​​текст​
​ Кроме того, для​ краю ячеек, а​ начальное значение 0.​
​CTRL+C​=ТЕКСТ(1234567898;​ если потребуется создать​ Объект Range, свойства​: Александр, еще одного​ впоследствии с числовыми​Применение инструмента «Текст по​
​ макросов.​ 3​

​ её нужно откуда-то​​: Тоже хотел так​ в формате даты​ настроить ширину столбца​не является полезной,​ представления времени в​ значения — по правому).​Если вы уже ввели​, чтобы скопировать код​
​»[)​ другие формулы, всегда​ -. И такая​

​ шага Вам не​​ аргументами производятся все​ столбцам». Выделяем столбец​Excel помогает пользователю сразу​Текст: Q1*H1​ скопировать. А ячеек​ написать, слово в​ или «# ##0,00″​ в зависимости от​ так как она​ 12-часовом формате можно​
​ Обратите внимание на​ данные в Excel​ формата, а затем —​Дополнительный формат (номер телефона),​ ссылайтесь на исходное​
​ же фигня с​ хватило для функции.​ необходимые операции.​ с текстовыми аргументами,​ определить, значения в​

​Формула: =Q1*H1​​ с формулами на​ слово, но вспомнил,​

Преобразование формул в текст (Формулы/Formulas)

​ для вывода в​​ того, как окончательный​
​ позволяет принудительно отформатировать​ использовать символы «AM/PM».​ разницу между кодами​
​ и нули в​ кнопку​ например: (123) 456-7898​ значение, а не​

​ функциями — все​​Function TextFormula(MyFormula As​User​

​ которые нужно преобразовать​​ ячейках отформатированы как​​Ответ: 6​​ листе очень много,​ что если нужный​​ числовом формате с​

​ результат будет выровнен.​​ значения в Excel​Если не указывать символы​
​ денежных и финансовых​ начале удалены, их​Отмена​

​=ТЕКСТ(1234;​​ на результат функции​ функции, что они​ Range) As String​: Ребята, а вы​ в числа. На​
​ числа или как​Serge_007​ придется каждую формулу​ файл открыт, то​ разделителями разрядов и​В этом примере использована​ с помощью​ «AM/PM», время будет​ форматов: в финансовых​

​ можно снова добавить​​, чтобы закрыть диалоговое​​»0000000″​​ ТЕКСТ.​ делают, синтаксис.​If MyFormula.Count >​
​ не подскажете, как​ вкладке «Данные» находим​ текст. Числовые форматы​: Макросом или макрофункцией​ копировать по отдельности.​

​ и без ДВССЫЛ​​ двумя знаками после​ формула​кода форматирования​ отображаться в 24-часовом​ форматах для отделения​ с помощью функции​ окно​)​Синтаксис​2 Igor67​ 1 Then Exit​ мне из формулы​ кнопку «Текст по​ выравниваются по правому​ ВЫЧИСЛИТЬ()​

​ Простите, если я​​ можно обойтись, тем​​ запятой. Правила форматирования​​=»Сегодня: «&СИМВОЛ(10)&ТЕКСТ(СЕГОДНЯ();»ДД.ММ.ГГ»)​, например​ формате.​ символа денежной единицы​ТЕКСТ​Формат ячеек​Добавление нулей в начале,​

​ТЕКСТ​​Нет, это был​ Function​
​ сделать текстовое значение?​

​ столбцам». Откроется окно​​ краю, текстовые –​
​andreyglad​ чего-то не понимаю.​ более, что в​ см. ниже.​.​

​»мм/дд/гг»​​В функции ТЕКСТ коды​
​ от значения используется​. Для верхней ячейки​.​
​ например: 0001234​(значение; формат)​ не я. Я​If Left(MyFormula.FormulaLocal, 1)​ Иными словами в​
​ «Мастера». Нажимаем «Далее».​ по левому.​: У меня в​buchlotnik​ формуле нет переменных​Форматов для отображения чисел​Это экспоненциальное представление числа.​
​для форматирования даты.​ форматов используются без​ звездочка (*).​
​ диапазона со значениями​Теперь осталось нажать клавиши​=ТЕКСТ(123456;​Аргументы функции​ другой юзер.​
​ = » ##0° 00′ 00»»​
​ТЕКСТ​Igor67​ TextFormula = Mid(MyFormula.FormulaLocal,​

​ =А1+А2, что дает​​ обращаем внимание на​ или сбое в​

Как текст превратить в формулу? (Формулы)

​ нет. ​​sgkorolew​вообще то вся​ много (например, см.​
​ к такому виду​ что происходит при​ допустимы символы «Ч»​
​ для определенной денежной​
​=ТЕКСТ(значение;»00000″)​
​, чтобы вставить код​

​)​​описаны ниже.​: ZVI, как -​

​ 2)​​ результат например 5.​ формат данных столбца.​ Excel числовой формат​

​andreyglad​​, в вашем случае​

​ формула должна работать​​ здесь), также имеются​ числа длиной более​ попытке присоединиться к​ и «ч», «М»​ единицы, сначала нажмите​

​, где количество нулей​​ формата в функцию​Пользовательский формат (широта или​
​Имя аргумента​ то отвечал про​

​End Function​​ Мне же нужна​Последний способ подходит в​ становится текстовым, в​: Спасибо. Заработало​ возиться с форматом​
​ с закрытым файлом,​ форматы для отображения​

​ 12 цифр, если к​​ тексту и числу,​ и «м», «С»​
​ клавиши​

​ соответствует нужному числу​​ ТЕКСТ. Пример: =ТЕКСТ(B2;»​

Преобразование текста в формулу в exele

​ долгота), например: 12°​​Описание​ толковую литературу. В​kaa​ формула, которая, ссылаясь​ том случае, если​ левом верхнем углу​andreyglad​ не вариант -​ там ведь обычная​

​ дат (например, см.​​ ячейкам применен формат​ не используя функцию​ и «с», «AM/PM»​
​CTRL+1​

​ символов. Затем скопируйте​​ДД.ММ.ГГГГ​ 34′ 56»​

​значение​​ файле посмотри.​​: как=то у меня​​ на ячейку А3​ значения находятся в​ ячеек появляется зеленый​
​: . Почему при​ попробуйте схему из​ ссылка.​ здесь). Также приведено​Общий​

​текст​​ и «am/pm».​(на компьютере Mac —​

Преобразование текста в число в ячейке Excel

​ формулу и примените​»). Обязательно заключите скопированный​Примечание:​Числовое значение, которое нужно​Первую из названных​ гораздо проще получилось:​ выдавала бы результат​ одном столбце.​ треугольничек. Это знак​ изменении значений Q1​ сообщения №6 с​Откройте оба файла,​

Как преобразовать текст в число в Excel

​ много форматов .​, и числа длиннее​. В этом​Для отображения десятичных значений​+1​ ее к остальной​ код формата в​ Хотя вы можете использовать​

​ преобразовать в текст.​ купил. Теперь изучаю,​Function TextFormula(r As​ «А1+А2″ (тестом).​​ ошибки. Ошибка также​ и Н1 ответ​ заменой » Ошибка значения.» gif;base64,R0lGODdhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=» data-src=»https://img.my-excel.ru/excel-datu-perevesti-v-chislo-excel_8_1.png»>

​ 15 цифр, если выбран​ случае мы используем​ можно использовать процентные​) и выберите нужный​

1. ​ части диапазона.​ кавычки («код формата»),​ функцию текст для​формат​ читается легко, интересно,​ Range) As String​Igor67​Преобразовать числа, сохраненные как​ возникает, если перед​ макрофункции ВЫЧИСЛИТЬ() не​ » Преобразовать в число.» gif;base64,R0lGODdhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=» data-src=»https://img.my-excel.ru/excel-datu-perevesti-v-chislo-excel_9_1.png»>
2. ​ (%) форматы.​ формат, а затем​Если по какой-либо причине​ иначе в Excel​ изменения форматирования, это​Текстовая строка, определяющая формат,​ правда потом пальцы​TextFormula = «»​: Сумма прописью?​
3. ​ текст, в числа​ числом поставить апостроф.​ меняется?​TimSha​ ссылкой на В2​ и результат больше​Числовой​(​Десятичные числа можно отображать​ в раскрывающемся списке​ потребуется преобразовать текстовые​ появится сообщение об​ не единственный способ.​ который требуется применить​ путаются, когда сам​If r.Value <>​User​ можно с помощью​Способов преобразования текста в​vikttur​: Азбука — Ctrl+H​
4. ​ в файле, сохраните.​ не может быть​. Если вы вводите​&​ в виде дробей,​Обозначение​ значения обратно в​ ошибке.​ Чтобы изменить формат​ к указанному значению.​ пытаешься делать.​ «» Then TextFormula​: Не понял вопроса.​
5. ​ макроса.​ число существует несколько.​: Почему текст? Почему​ . ​roman66rus​ использован в вычислениях​ длинные цифровые строки,​) для сцепления текстовой​ используя коды форматов​выберите символ.​ числа, можно умножить​»Ячейки» > «Число»​

​ без формулы, нажмите​Самая простая функция ТЕКСТ​Илья​ = r.Formula​

​ Какая сумма? При​

Макрос «Текст – число»

​Есть набор значений, сохраненных​ Рассмотрим самые простые​ не формула?​См. скрин.​

​: Забыл совсем про​ в качестве числа.​

​ но не хотите,​ строки, пробела («»)​ вида «?/?».​После этого в разделе​ их на 1​ > «Другое» для​ клавиши​

​ означает следующее:​: а вот другой​End Function​ чем тут пропись?​ в текстовом формате:​ и удобные.​

​Возможно, есть другие​sgkorolew​ нее, благодарю.​ Чтобы отформатировать число,​ чтобы они отображались​

​ и значения с​Экспоненциальное представление — это способ​Числовые форматы​ (например:​ получения строк формата.»​CTRL + 1​=ТЕКСТ(значение, которое нужно отформатировать;​ вопрос специалистом​kaa​

​Да, только сейчас​

​Чтобы вставить макрос, на​Использовать меню кнопки «Ошибка».​

​ варианты решения.​: buchlotnik, AlexM, спасибо!​sgkorolew​ но при этом​ в таком виде,​

​= a2 & «»​ отображения значения в​слева выберите пункт​=D4*1​ />​(или _з0з_​ «код формата, который​

​как показать саму​: сорри, вот так​ обнаружил опечатку -​ вкладке «Разработчик» находим​

​ При выделении любой​andreyglad​

​В параметрах замены​: Добрый вечер!​ оставить его числом​ то сначала примените​ & B2​ виде десятичного числа​(все форматы)​) или воспользоваться двойным​В примерах ниже показано,​+ 1​ требуется применить»)​ формулу, т.е. в​ надежнее:​

Преобразование формулы в текст

​ последнее слово в​​ редактор Visual Basic.​ ячейки с ошибкой​: Извините, пожалуйста, но​ (ctrl+H) выбирается область​Подскажите, пожалуйста, как​ (с которым можно​ к соответствующим ячейкам​».​ от 1 до​и скопируйте код​ унарным оператором (—),​ как применить различные​на компьютере Mac),​

​Ниже приведены популярные примеры,​​ тексте и с​

​Function TextFormula(r As​​ моем прошлом посте​ Открывается окно редактора.​ слева появляется соответствующий​

​ я не «копирую​ поиска: формулы. Не​ преобразовать формулы в​ выполнять арифметические действия),​ формат​Вы видите, что значение​ 10, умноженного на​

​ формата вместе с​​ например:​ числовые форматы к​ а затем выберите​ которые вы можете​ подстановкой значений​
​ Range) As String​ — не «тестом»,​ Для добавления кода​ значок. Это и​

​ вниз» эту функцию.​​ знал, что так​ текст.​ щелкните ячейку правой​Текстовый​ даты, взятое из​

​ 10 в некоторой​​ обозначением денежной единицы.​=—D4​ значениям следующим способом:​ нужный формат в​ скопировать прямо в​например​TextFormula = «»​ а «текстом», естественно​

​ нажимаем F7. Вставляем​​ есть кнопка «Ошибка».​ Я лишь меняю​

​ можно.​​Т.е. чтобы в​
​ кнопкой мыши, выберите​.​ ячейки B2, не​ степени. Этот формат​

​Примечание:​​.​ открыть диалоговое окно​ диалоговом окне​ Excel, чтобы поэкспериментировать​А1: 10​If r.HasFormula Then​ :)​ следующий код:​ Если навести на​ исходные данные. ​Serge_007​
​ ячейках отображались формулы,​ команду​Минда советует. ​
​ отформатировано. В следующем​

​ часто используется для​​ Функция ТЕКСТ не поддерживает​В Excel группы разрядов​Формат ячеек​
​Формат ячеек​ самостоятельно. Обратите внимание:​
​А2: 20​ TextFormula = r.Formula​Igor67​
​Sub Conv() With ActiveSheet.UsedRange​ нее курсор, появится​Посмотрите пожалуйста мой​: Варианты:​
​ но расчетных операций​

​Формат ячеек​​Если вы предоставляете доступ​ примере показано, как​

​ краткого отображения больших​ форматирование с помощью​
​ разделяются пробелом, если​
​, выбрать пункт​>​ коды форматов заключены​
​А3: =А1+А2​

​End Function​​: Просто очень часто​ arr = .Value​
​ знак раскрывающегося меню​ файл.​
​1. Получить текст​
​ никаких не производилось.​и в диалоговом​
​ к файлам и​

​ применить нужное форматирование​​ чисел.​ цвета. Если скопировать​ код формата содержит​(все форматы)​число​ в кавычки.​А4: =10+20​Лузер​ в бухгалтерии необходимо​ .NumberFormat = «General»​ (черный треугольник). Выделяем​

​Serge_007​​ формулы с помощью​AlexM​ окне​ отчетам Excel пользователям​ с помощью функции​

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

​ число — прописью.​​ .Value = arr​

​ столбец с числами​​: Да, надо добавить​
​ макрофункции GET.FORMULA()​: перед = поставьте​Формат ячеек​ из разных стран,​ TEXT.​ дополнительных формата:​ «Формат ячеек» код​ номера (#) или​код формата​Вы можете скачать пример​Описание​

​: =10&»+»&20​ Только я бы​ Если это денежные​ End With End​ в текстовом формате.​ волатильности, например так:​2. Получить текст​ ‘ апостроф​на вкладке​ вам может потребоваться​Вот обновленная формула:​»Почтовый индекс» («00000»);​ формата, в котором​ нулями. Например, если​

​в формулу с​
​ книги со всеми​=ТЕКСТ(1234,567;​Или объясните «специалистОм»​

​ все равно проверял,​​ единицы, в файле.​ Sub​ Раскрываем меню кнопки​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВЫЧИСЛИТЬ(Лист1!B7)+ТДАТА()-ТДАТА()​
​ формулы с помощью​buchlotnik​Число​ предоставить им отчет​ячейка C2:​»Индекс + 4″ («00000-0000»);​

​ используется цвет, например​​ используется код формата​ функцией​
​ примерами текстовых функций,​»# ##0,00 ₽»​ хитрее :)​ ранге какого размера​
​ЗЫ по учету​
​Чтобы он «заработал», нужно​
​ «Ошибка». Нажимаем «Преобразовать​
​andreyglad​
​ функции Ф.ТЕКСТ() (работает​

​:​​настройте нужные параметры​
​ на их языке.​=A2&» «&ТЕКСТ(B2;»дд.мм.гггг»)​

Как сделать формулу в Excel?

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

Как создать формулу в Excel?

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

• Запускаем Эксель с рабочего стола или из меню.

• Откроется окно с множеством ячеек. Именно в них и нужно вводить формулы. Каждая ячейка имеет свой уникальный адрес, состоящий из номера столбца и буквенного обозначения строки. Он также отображается в специальном поле над таблицей, если поставить курсор на интересующую ячейку. На скрине активная ячейка подсвечена черным, ее адрес — Е5.

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

• Левой кнопкой мыши кликаем по ячейке, в которой планируется увидеть результат. В данном случае считаем прибыль для первого дня, адрес ячейки — D5.

• На клавиатуре нажимаем знак равенства «=».

• Левой кнопкой мыши кликаем по ячейке с доходами (В5). Она выделится цветом, а ее адрес появится после знака равенства.

• Курсор стоит в ячейке, куда вводим формулу. Нажимаем на клавиатуре знак минус «-» .

• Щелкаем левой кнопкой мыши по ячейке с расходами (С5).

• Нажимаем на клавиатуре Enter и смотрим на результат.

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

• Нажимаем левую клавишу мыши и держим ее, одновременно выделяя нужные ячейки (как будто растягивая формулу).

• Отпустив кнопку мыши, увидим, что формула автоматически скопировалась в ячейки, где сразу отобразились результаты вычислений.

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

Совет: иногда при работе с большим объемом данных в табличной форме становится неудобно постоянно возвращаться к началу, чтобы посмотреть «шапку». В таком случае лучше всего зафиксировать строку в Excel — данные будут прокручиваться, а заголовок таблицы останется на месте.

Примеры написания формул в Экселе

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

• через кнопку «Вставить функцию», расположенную над рабочим полем;

• обратившись ко вкладке «Формулы» в меню и найдя там «Математические» (Логические, Текстовые и т.д.).

• кликнув по «Вставить функцию» на вкладке «Формулы».

В Экселе множество разных операторов и функций — разберем подробнее самые востребованные.

СУММ — суммирование чисел

Суммировать что-то нужно практически всем — поэтому оператор СУММ используется в Excel обычно чаще других. Алгоритм действий, если вам необходимо сложить числа:

• Кликаем по ячейке, в которой планируется получить результат. Переходим на вкладку «Формулы» в меню, нажимаем «Математические». В выпадающем списке находим функцию СУММ и щелкаем по ней левой кнопкой мыши.

• В появившемся окне необходимо ввести аргументы функции. Здесь можно идти разными путями — выделять нужные ячейки или интервал либо вводить их адреса вручную. Стоит иметь в виду, что ячейки перечисляются через точку с запятой (например, А1;А3;А4), а интервал в формулах обозначается путем двоеточия (например, В4:В10).

• Щелкаем по «Ок» и видим результат суммирования. Обратите внимание, что в строке формул показывается функция и ее аргументы.

Синтаксис функции: =СУММ(число1;число2;число3;…) или =СУММ(число1:числоN).

СУММЕСЛИ — суммирование при соблюдении заданного условия

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

• На вкладке «Формулы» кликаем по «Математическим» и выбираем в списке СУММЕСЛИ.

• Выбираем диапазон для суммирования — для этого нужно кликнуть по соответствующей строке, а затем выделить ячейки с заработной платой (интервал E4:E13).

• Определяем диапазон, из которого будем брать ячейки, подходящие под условие. В нашем случае выделяем ячейки с должностями. В строке «Критерий» пишем — «продавец», то есть рассчитываем зарплату всех продавцов.

• В результате оператор СУММЕСЛИ суммирует ячейки с зарплатой только продавцов.

Конечно, условия могут быть разными, все зависит от конкретной ситуации и потребностей пользователя.

Синтаксис функции: =СУММЕСЛИ(диапазон;критерий;диапазон_суммирования).

СТЕПЕНЬ — возведение в степень

Зачастую возникает необходимость возвести число в какую-либо степень — тогда стоит воспользоваться функцией СТЕПЕНЬ:

• Щелкаем по «Математическим» формулам в соответствующей вкладке и находим СТЕПЕНЬ.

• В открывшемся окне вводим аргументы функции: число — это основание (то, что мы возводим в степень), степень — показатель.

Аргументы пишутся как вручную с клавиатуры, так и кликами по ячейкам, если они были заполнены предварительно.

Синтаксис функции: =СТЕПЕНЬ(число;степень).

СЛУЧМЕЖДУ — вывод случайного числа в интервале

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

• В «Математических формулах» выбираем СЛУЧМЕЖДУ.

• Аргументами функции являются нижняя и верхняя границы — вводим их, кликая по ячейкам.

• В результате получаем случайное число, находящееся между двумя заданными.

Синтаксис функции: =СЛУЧМЕЖДУ(нижн_граница;верхн_граница).

ВПР — поиск элемента в таблице

Функция, которая существенно экономит время, помогая в поиске данных. Относится к ссылочным операторам. Использовать ее можно в разных ситуациях — например, нужно по ФИО сотрудника найти его код:

• На вкладке «Формулы» кликаем по «Ссылкам и массивам» и выбираем в списке ВПР.

• В «Искомое значение» пишем то, по чему ищем (в нашем случае — ФИО сотрудника). В аргументе функции «Таблица» необходимо указать область поиска (выделяем всю таблицу). В «Номере столбца» обозначаем, из какого столбца нужно вернуть результат (код сотрудника). В «Интервальном просмотре» вводим «ЛОЖЬ», если требуется точное совпадение.

• Таким образом можно легко и быстро осуществлять поиск по таблицам большого объема.

Синтаксис функции: ВПР(искомое_значение,таблица, номер_столбца,интервальный_просмотр).

СРЗНАЧ — возвращение среднего значения аргументов

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

• Во вкладке «Формулы» кликаем по «Другим функциям» и выбираем «Статистические». В появившемся списке находим СРЗНАЧ.

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

• Функция вернет среднее арифметическое аргументов, которое и будет средней заработной платой в компании.

Синтаксис функции: =СРЗНАЧ(число1;число2;).

МАКС — определение наибольшего значения из набора

Необходимость быстро найти самое большое значение из какой-либо выборки возникает довольно часто. В этом деле поможет статистическая функция МАКС. Предположим, нужно узнать, какое максимальное количество посетителей было на сайте за определенный период:

• Кликаем по ячейке, куда будет выводится результат. Во вкладке «Формулы» щелкаем по «Вставить функцию», в «Категориях» выбираем «Статистические», а в появившемся списке — МАКС.

• В «Аргументах функции» следует указать интервал, из которого будет выбираться максимальное число. В нашем случае — ячейки с количеством посетителей. Кликаем по «Числу 1» и выделяем нужные ячейки (конечно, можно впечатать диапазон и с клавиатуры).

• Максимальное количество посетителей определено.

Синтаксис функции: =МАКС(число1;число2;…).

КОРРЕЛ — коэффициент корреляции

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

• Во вкладке «Формулы» щелкаем по «Вставить функцию», в «Категориях» обращаемся к «Статистическим» и находим в списке КОРРЕЛ.

• Заполняем «Аргументы функции»: в качестве «Массива 1» выделяем ячейки с количеством посетителей, в «Массив 2» вносим данные о показе рекламы.

• В результате функция КОРРЕЛ возвращает значение коэффициента корреляции, который показывает наличие или отсутствие зависимости друг от друга двух величин. В рассматриваемом примере он равен 0,7261, а значит, наблюдается достаточно тесная зависимость между количеством гостей сайта и показами рекламы.

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

Синтаксис функции: =КОРРЕЛ(массив1;массив2).

ДНИ — количество дней между двумя датами

Функция, с помощью которой в Excel можно быстро узнать количество дней между двумя известными датами. Например, работник уходит в отпуск — есть две даты, определяющие продолжительность отдыха. Нужно вычислить, сколько дней длится отпуск:

• Во вкладке «Формулы» кликаем по кнопке «Дата и время» и выбираем в списке функцию ДНИ.

• В поле «Кон_дата» вводим дату окончания отпуска, кликая по соответствующей ячейке, а в «Нач_дата» — дату его начала.

• В итоге получаем продолжительность отпуска в днях.

Синтаксис функции: =ДНИ(кон_дата;нач_дата).

ЕСЛИ — выполнение условия

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

• Во вкладке «Формулы» кликаем по «Логическим» и находим ЕСЛИ.

• Заполняем «Аргументы функции». В поле «Лог_выражение» необходимо написать условие — в нашем случае выручка от продаж (В5) должна быть больше 40000 рублей. В «Значение_если_истина» пишем то, что будет выводится в ячейке, если условие выполняется. Таким образом, если выручка превышает 40000 рублей, «План выполнен». В «Значение_если_ложь» указываем «План не выполнен» — эта фраза появится в ячейке, если выручка продавца меньше 40000 рублей.

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

Синтаксис функции: =ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь).

СЦЕПИТЬ — объединение текстовых строк

Формула относится к текстовым и направлена на сцепку в одно целое нескольких строк. Часто используется в Excel для того, чтобы объединить две и более ячеек. Например, фамилии, имена и отчества людей записаны по разным ячейкам, а возникла потребность сделать сводную колонку. Действуем:

• Обращаемся ко вкладке «Формулы» и кликаем по «Текстовым». Находим в списке СЦЕПИТЬ.

• В «Аргументах функции» последовательно указываем ячейки, которые нужно объединить. Можно либо кликать по ним, либо вводить буквенно-цифровое обозначение вручную.

• Появившийся результат, конечно, далек от идеала — между словами отсутствуют пробелы.

• Решить этот вопрос не так сложно — некоторые советуют просто добавить пробел после текста в каждой ячейке, однако, если информации много, заморачиваться с дописыванием лишних символов совсем не хочется. Лучше пойти другим путем — скорректировать формулу. Для этого в синтаксисе функции требуется добавить пробелы в кавычках — » » .

• Пробелы появились, проблем больше нет. Растягиваем формулу на другие ячейки.

Синтаксис функции: =СЦЕПИТЬ(текст1;текст2;…).

ЛЕВСИМВ — возвращает заданное количество символов

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

• В «Формулах» кликаем по «Текстовым» и обращаемся к функции ЛЕВСИМВ.

• В поле с «Текстом» вводим нужную ячейку, в «Количестве_знаков» пишем желаемую длину тайтла.

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

Синтаксис функции: =ЛЕВСИМВ(текст, количество_знаков).

Подводим итоги

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