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

Как быстро извлечь часть даты из ячейки в Excel?

Как вытащить число или часть текста из текстовой строки в Excel

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

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

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

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

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

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

Читайте так же:
Как быстро открыть все файлы Excel из папки?

ватащить текст

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

Взять текстовую строку и извлечь код — непростая задача.

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

Этого можно добиться, извлекая код или часть текста с помощью функций LEFT и RIGHT. Обе эти функции возвращают указанное количество символов от начала или конца строки соответственно.

Текст является обязательным аргументом. Текстовая строка, содержащая символы, которые необходимо извлечь.

Количество_знаков — необязательный аргумент. Количество символов, извлекаемых функцией ЛЕВСИМВ (ПРАВСИМВ).

«Количество_знаков» должно быть больше нуля или равно ему. Если «количество_знаков» превышает длину текста, функция ЛЕВСИМВ (ПРАВСИМВ) возвращает весь текст. Если значение «количество_знаков» опущено, оно считается равным 1.

Количество символов в коде легко определить, поэтому мы можем получить необходимые символы.

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

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

=ПСТР(текст; начальная_позиция; количество_знаков)

Текст является обязательным аргументом. Текстовая строка, содержащая символы, которые необходимо извлечь.

Initial_position — обязательный аргумент. В тексте извлекается первый символ. Первому символу в тексте присваивается позиция 1, и так далее.

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

Это происходит, если код находится в том же месте, что и в начале строки. Он всегда начинается с «Поступление товаров и услуг XX». «.

Наш признак «ХХ» — код филиала начинается с 29 знака и имеет 2 знака в своем составе.

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

В нашем случае формула выглядит следующим образом

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

Первый символ – первая буква в наименовании филиала, второй символ – это буква Ф (филиал) и далее следует пять нулей «00000». Причем меняется только первый символ — первая буква наименования филиала.

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

=Поиск(поиск_текста; текст_к_поиску; [start_item])

Searchable_text — обязательный аргумент. Текст, который вы хотите найти.

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

Start_position — необязательный аргумент. Начинает поиск с символа из аргумента просмотренный_текст.

Функция ПОИСК не чувствительна к регистру. Если вы хотите быть чувствительным к регистру, используйте функцию ПОИСК.

В аргументе seek_text можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому символу, а звездочка — любой последовательности символов. При поиске вопросительного знака или звездочки введите тильду ().

Рядом с изменяющимся первым символом ставится знак вопроса (?). В этом случае мы можем записать формулу для выделения кода ветви следующим образом:

Эта формула определяет начальную позицию кода филиала в имени документа, а затем возвращает два символа кода с найденной позиции.

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

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Функция ДАТА — как использовать формулу ДАТА в Excel

Функция ДАТА возвращает число, представляющее дату определенного дня, месяца и года. Функция дата используется, как правило, для построения даты из составных частей, таких как день, месяц и год. Минимальное и максимальное значение дат, которое может принять Excel — 1 января 1900 года и 31 декабря 9999 года соответственно.

Читайте так же:
Как быстро закрасить всю неиспользуемую область в Excel серым?

Синтаксис функции ДАТА

Три части образуют ДАТУ:

ДАТА (год, месяц, день)

Функция ДАТА аргументы

Year — это первый аргумент, который указывает год даты, которая будет построена. Максимальное значение, которое может быть указано, — 9999.

Месяц — это второй аргумент, который определяет месяц, который будет отображаться в нашей дате. Обратите внимание, что значения месяца не ограничиваются от 1 до 12, а могут принимать любые значения, даже отрицательные. Например, если вы введете формулу =DATE(2013;13;1), Excel вернет дату, которая находится через 13 месяцев от 1 января 2013 года, то есть 1/1/2014.

Day — это часть функции date, которая устанавливает день желаемой даты. Аргумент Date может принимать любое значение (не только 1, 2 или 3). Если в Excel ввести формулу =DATE(2013;01;35), то она вернет дату, отстоящую на 35 дней от 1 января 2013 года. 4/02/2013. Для создания прошлых дат можно также использовать отрицательные значения.

Вот пример использования функции DATE. В данном случае у нас есть три поля для года, месяца и дня. Необходимо объединить все три значения, чтобы получить дату. Для вычисления даты введите формулу =DATE(A2,B2,C2). Здесь результатом будет дата 19 июня 2013 года.

Формат даты в Excel

Когда вы представляете данные, содержащие даты, убедитесь, что они отформатированы правильно. Существует несколько форматов дат, которые используются по-разному в каждом отделе, компании или стране. На мой взгляд, формат ДД-МММ-ГГГГ устраняет двусмысленность.

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

формат дат

Возможные ошибки формул ДАТА

Функция date может вернуть следующие ошибки:

Читайте так же:
Как быстро создать поле со списком в Excel?

Ошибка #Число! в формуле DATE возникает, когда введенная дата находится вне допустимого диапазона (от 0/1/1900 до 12/31/9999). Например, если вы хотите ввести год 2013, но по ошибке ввели 20013, скажем =DATE(20013;6;19), Excel выдаст ошибку.

Ошибка #IMAGE! в формуле DATE возвращается, если один из аргументов введен неправильно (введен текст вместо числового значения). Например, вы ввели в качестве аргумента месяца «Июнь» вместо 6 (порядковый номер месяца — июнь) и ввели следующую формулу =DATE(2013; June; 19). В этом случае Excel возвращает ошибку #IMAGE!

Вам также могут быть интересны следующие статьи

6 комментариев

Можно ли создать формулу даты, не используя информацию из других ячеек (=DATE(A2;B2;C2)), но используя числа, не ссылающиеся на другие ячейки (=DATE(2014;08;26))?

Или мне нужны данные из других клеток?

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

А как преобразовать в формат гггг-мм-дд уже внесенные даты рождения (20 тыс шт) в формате дд.мм.гггг ? На установку формата ячеек, в т.ч. и с установкой фолрмата системного времени через часы) не реагирует. Информация нужна срочно.
28/05/2015

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