Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Как в excel при протягивании менять дату по порядку

Поскольку каждому значению даты соответствует определенное число (см. статью Как дата и время хранятся в EXCEL), процедуры создания последовательностей, описанные в статье Числовые последовательности, можно применять и к датам. Однако существуют некоторые особые обстоятельства.

Последовательность 01.01.09, 01.02.09, 01.03.09 (первые дни месяцев) можно сформировать формулой =ДАТАМЕС(B2;СТРОКА(A1)) , в ячейке B2 должна находиться дата — первый элемент последовательности ( 01.01.09 ).

Используя правую кнопку мыши, можно создать такую же последовательность. Ячейка A2 должна быть заполнена значением 01.01.09. Выделите ячейку A2 . Удерживая правую кнопку мыши, скопируйте значение из ячейки A2 в ячейки ниже. Отпустив правую кнопку мыши, вы увидите контекстное меню, в котором выберите пункт Заполнить по месяцам.

Изменив формат ячеек, содержащих последовательность 01.01.09, 01.02.09, 01.03.09, на МММ (см. статью Настраиваемый формат даты), мы получим последовательность Jan, Feb, Mar, ……

Такую же последовательность можно ввести с помощью списка автозаполнения Кнопка Office / Параметры Excel / Основные / Основы Excel / Списки изменений (введите jan , а затем скопируйте вниз с помощью маркера заполнения).

В ячейках не будет дат, вместо них — текстовые значения.

То же самое относится к последовательности дней недели, например, пн, вт, ср и т.д.

Последовательность кварталов Q1, Q2 можно сформировать, используя идеи из статьи Последовательности текстов .

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

Последовательность первых месяцев кварталов Jan, Apr, Jul, Oct, Jan, . можно создать, введя первые два элемента последовательности ( Jan, Apr ) в две ячейки, а затем (предварительно выделив их) скопировав их вниз с помощью курсора заливки. Ячейки будут содержать текстовые значения. Чтобы ячейки содержали даты, используйте формулу =DATAMES($G$16;(ROCK(A2)-ROCK($A$1))*3) Предполагая, что последовательность начинается в ячейке G16, формулу следует ввести в ячейку G17 (см. пример файла).

Временная последовательность 09:00, 10:00, 11:00. может быть сформирована с помощью маркера заполнения . Пусть в ячейку A2 введено значение 09 : 00 . Выделите ячейку A2 . Значение из A2 следует скопировать в ячейки ниже. Будет сформирована последовательность.

Легко сгенерировать 15-минутные временные интервалы ( 09:00, 09:15, 09:30, .). Формула имеет вид =B15+1/24/60*15 (при условии, что последовательность начинается в ячейке B15, формула вводится в B16). Формула возвращает результат в формате даты.

Читайте так же:
Как использовать проверку данных, чтобы установить процент в ячейках Excel?

Другая формула =ТЕКСТ(B15+1/24/60*15;"чч:мм") вернет результат в текстовом формате.

СОВЕТ: О текстовых последовательностях вида first, second, . 1), 2), 3), . можно прочитать в статье Текстовые последовательности. Для числовых последовательностей вида 1, 2, 3, . 1, 3, 5, 7, . I, II, III, IV, . можно найти в статье Числовые последовательности.

Как протянуть дату в Ексель?

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

Как в Excel сделать дату по порядку?

Сортировка по дате

  1. Перетащите указатель вниз по столбцу, чтобы выделить нужные даты.
  2. На вкладке Главная нажмите стрелку на кнопке Сортировка и фильтр и выберите Сортировать старые к новым или Сортировать новые к старым.

Как сделать в Экселе чтобы дата не менялась?

Если вам нужно только несколько чисел, вы можете запретить Excel изменять их в даты, введя :

  1. Пробел перед вводом числа. …
  2. Апостроф (‘) перед вводом числа, например ’11-53 или ‘1/47. …
  3. Ноль и пробел перед вводом дроби, например 1/2 или 3/4, чтобы они не менялись, например, на 2-янв или 4 мар.

Как изменить дату на месяц в Excel?

Готовые формулы будут иметь следующий вид:

  1. Прибавить месяцы к дате: =ДАТА(ГОД(A2);МЕСЯЦ(A2)+2;ДЕНЬ(A2)) =DATE(YEAR(A2),MONTH(A2)+2,DAY(A2))
  2. Вычесть месяцы из даты: =ДАТА(ГОД(A2);МЕСЯЦ(A2)-2;ДЕНЬ(A2)) =DATE(YEAR(A2),MONTH(A2)-2,DAY(A2))

Как сделать месяц из даты в Excel?

Имя месяца в EXCEL

  1. С помощью формулы =ТЕКСТ(B6;»ММММ») можно вывести полное название месяца с заглавной буквы в именительном падеже, Сентябрь . …
  2. Формула =ТЕКСТ(B6;»МММ») выведет сокращенное название месяца (3 буквы).
  3. Особый формат =ТЕКСТ(B6;»[$-FC19] ММММ») выведет полное название месяца с учетом склонения, т.

Как отключить Автозамену на дату в Excel?

Автоматический переход от номера к дате в Excel 2003

  1. Нажмите на круглую кнопку меню Excel 2007 (в левом верхнем углу программы).
  2. Нажмите на «Параметры Excel». …
  3. Выберите пункт меню Правописание и, справа, нажмите на кнопку Параметры автозамены:
  4. В появившемся окне снимите отметку с чекбокса «Заменять при вводе» и нажмите на ОК:

Как сохранить дату в текст Excel?

Для отображения даты в нужном нам формате используем функцию ТЕКСТ() : =»Cегодня «&ТЕКСТ(A1;»дд. ММ. гг») . Получим желаемое — «Сегодня 02.10.10».

Читайте так же:
Как найти значение и вернуть ячейку выше или ниже в Excel?

Почему в Экселе выходит дата?

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

Как в Excel сделать фильтр по дате?

Фильтрация по дате в Excel

  1. Выделите любую ячейку в наборе данных.
  2. На вкладке Данные нажмите кнопку Фильтр. В заголовках столбцов появляются стрелки.
  3. Щелкните стрелку в столбце Дата.
  4. Нажмите Выбрать все, чтобы отменить выбор всех элементов. …
  5. Нажмите OK.

Как выделить выходные дни в Excel?

Как выделить цветом только выходные по дате в Excel

  1. Выделите исходных диапазон ячеек (в данном примере это A2:A17) и выбреете инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». …
  2. В появившемся окне выберите опцию: «Использовать формулу для определения форматируемых ячеек».

Как сделать уведомление в Excel?

Откройте приложение Office, например Word или Excel. Нажмите кнопку «профиль». Это первый левый верхний угол, в котором находятся инициалы. В нижней части появившегося диалогового окна выберите пункт Push-уведомления.

Как перевести месяц в число?

Метод 1: преобразовать число в название месяца с помощью формулы. Чаевые: Если вы хотите преобразовать число в сокращение названия месяца, вы можете использовать эту формулу = ТЕКСТ (ДАТА (2000; A1,1; XNUMX); «ммм»).

Как из даты сделать месяц и год?

Извлечь только месяц и год из числа с помощью формул

  1. Введите формулу: = ТЕКСТ (A2; «ммм-гггг») в пустую ячейку помимо ваших данных, например, C2, см. …
  2. Затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и из столбца даты были извлечены только месяц и год, см. …
  3. Ноты:

Как посчитать месяца в Excel?

Если в ячейке В2 содержится сегодняшняя дата, а в А2 –дата начала отсчета, то формула (см. файл примера ): =РАЗНДАТ(A2;B2;»m»)&»мес.» рассчитает сколько полных месяцев прошло с конкретной даты. Типичный результат будет выглядеть так: 25 мес.

Использования функции преобразования текста в число в макросах Excel.

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

Читайте так же:
Как извлечь актуальные адреса из гиперссылок в Excel?

Для решения денной проблемы в VBA можно использовать функцию VAL(), которая переводит в численный формат данных. VAL — это функция конвертирующая текстовые значения аргумента в целые числовые или числовые значения с плавающей запятой. Следует отметить что функция Val при преобразовании десятичных дробей в число может распознать только точку, то есть, если вы напишете десятичную дробь через запятую, то функция распознает только первую (целую)часть дроби, остальной текст после запятой функция не воспримет. Например: VAL(«10,8»)= 10

Рассмотрим пример кода с данной функцией (VAL) и без нее:

форма для заполнения exccel VBA

Имеется форма ввода данных, в которой два поля «TextBox1» и «TextBox1» содержат числа, которые сравниваются нажатием кнопки «Сравнить» (кнопка 1) и складываются нажатием кнопки «Добавить» (кнопка 2).

Это код кнопки сравнения «Сравнить» (CommandButton1):

  1. Private Sub CommandButton1_Click()
  2. Dim i, b As String
  3. i = TextBox1.Value
  4. b = TextBox2.Value
  5. If i < b = True Then MsgBox («Второе число больше «)
  6. If i > b = True Then MsgBox («Первое число больше «)
  7. End Sub

После нажатия кнопки «Сумма» (CommandButton2) должен быть применен этот код:

  1. Private Sub CommandButton2_Click()
  2. Dim i, b, d As String
  3. i = UserForm1.TextBox1.Value
  4. b = UserForm1.TextBox2.Value
  5. d = i + b
  6. TextBox3.Value = d End Sub

Сравнение значений будет выполняться верно, но расчеты суммы будут неверными:сравнение чисел макрос VBAСумма чисел макрос VBA

Применим функцию VAL () в этих кодах:

Первый код (сравнивает два числа)

  1. Private Sub CommandButton1_Click()
  2. Dim i, b As String
  3. i = Val(UserForm1.TextBox1.Value)
  4. b = Val(UserForm1.TextBox2.Value)
  5. If i < b = True Then MsgBox («Второе число больше «)
  6. If i > b = True Then MsgBox («Первое число больше «)
  7. End Sub

2-ой код (сумма двух цифр)

  1. Private Sub CommandButton2_Click()
  2. Dim i, b, d As String
  3. i = Val(UserForm1.TextBox1.Value)
  4. b = Val(UserForm1.TextBox2.Value)
  5. d = i + b
  6. TextBox3.Value = d End Sub

Верная сумма в VBA

С помощью этого кода кнопки на форме UserForm1 работают правильно, как показано на скриншоте ниже.

Как преобразовать текст в число? Текстовый формат в Excel

Для ячеек текстового формата значения ячеек отображаются именно в том виде, в котором они введены. Excel рассматривает эти значения как строки, то есть как текстовые значения, независимо от того, содержат ли они текст или числа. Excel рассматривает 1 и «1» как разные значения. Числовые значения также можно отображать как текст, используя текстовый формат. Числа в числовом формате участвуют в вычислениях, а числа в текстовом формате — нет! Из-за этого вычисления могут стать неточными. Каким образом вы различаете эти числа?

Читайте так же:
Как запустить макрос на основе значения ячейки в Excel?

Как отличить настоящее число от числа, отформатированного как текст?

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

контроль ошибок в Excel

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

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

1) Если вы введете числовое значение в ячейку с текстовым форматом, а затем измените формат ячейки на числовой, число останется текстовым и не будет включено в расчеты;

Если контроль ошибок отключен в настройках Excel, индикатор ошибок не появляется, а числа, отформатированные как текст, становятся невидимыми, что может привести к ошибкам в вычислениях;

3) Если включена проверка ошибок и отключено правило проверки «Числа, отформатированные как текст или с апострофом впереди», индикатор ошибки не отображается, а числа, отформатированные как текст, становятся невидимыми, что также может привести к ошибкам вычислений;

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

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

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

Читайте так же:
Как изменить цвет значка условного форматирования в Excel?

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

Чтобы преобразовать числа, отформатированные как текст, в реальные числа, необходимо выделить ячейку (подвести к ней рамку разметки таблицы), затем щелкнуть левой кнопкой мыши на значке информационного сообщения индикатора ошибок и выбрать «Преобразовать в число».

как преобразовать строку в число стандартными средствами Excel

Кроме того, вы можете нажать F2 на клавиатуре, а затем нажать Enter в выделенной ячейке. Это помогает, но не всегда! Вы можете изменить формат ячеек на числовой и вручную ввести значения. Также можно исправить сразу несколько ячеек. В некоторых случаях можно использовать умножение на единицу или поиск/замену непечатаемых символов.

Лучший способ преобразования текста в числа в больших таблицах или поиска таких ошибок в больших наборах данных — использовать надстройку.

Надстройка для поиска и преобразования чисел, отформатированных как текст в настоящие числа

Дополнение — это файл с программным кодом, написанным на встроенном языке программирования приложений Office. После установки надстройки через меню Excel (Install Add-Ins) появится новая панель инструментов или кнопка на вкладке Add-Ins в Excel.

makros-preobrazovaniya-teksta-v-chisla

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

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector