Функция len в excel

Функция MID + LEN в Excel для извлечения букв + число с несогласованным рисунком

1 George [2017-11-20 20:11:00]

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

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

Вот что я хотел бы сделать. Напишите функцию, которая гласит: «Когда» CS «(не чувствительный к регистру) найден, извлеките все последовательные целые числа, которые следуют до тех пор, пока не будет найдено целое число, а затем остановитесь».

Имеет ли это смысл? Есть лучший способ сделать это? Моя текущая формула фактически пропускает некоторые из 8-значных номеров CS, поэтому я думаю, что я должен начать с нуля. Вот что я имею для большего контекста.

function excel excel-formula

3 ответа

Попробуйте эту небольшую пользовательскую функцию:

Пользовательские функции (UDF) очень просты в установке и использовании:

ALT-F11 открывает окно VBE
ALT-I ALT-M открывает новый модуль
вставьте содержимое и закройте окно VBE

Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel позже 2003 года, вы должны сохранить файл как.xlsm, а не.xlsx

Чтобы удалить UDF:

вывести окно VBE, как указано выше
очистить код
закрыть окно VBE

Чтобы использовать UDF из Excel:

Чтобы узнать больше о макросах в целом, см.

и для специфики UDF, см.

Для этого необходимо активировать макросы!

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

Формула представляет собой формулу Array и должна быть подтверждена удерживанием ctrl + shift при ударе enter

seq — это * Именованная формула **

Если вас интересует функция, определяемая пользователем (UDF), здесь используется тот, который использует

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

Таким образом, действуют оба следующих условия:

Регулярный модуль

Это должно работать, если вас интересует формула:

Немного запутанный, но в основном он видит, может ли он превратить 9-й символ после «CS» в число. Если нет, верните символы «CS» +9, иначе «CS» +10.

Функции VBA для работы с текстом

ASC ()— эта функция позволяет вернуть числовой код для переданного символа. Например, ASC( «D» ) вернет 68. Эту функцию удобно использовать для того, чтобы определить следующую или предыдущую букву. Обычно она используется вместе с функцией Chr(), которая производит обратную операцию — возвращает символ по переданному его числовому коду.Варианты этой функции — AscB() и AscW():
- AscB () — возвращает только первый байт числового кода для символа.
- AscW () — возвращает код для символа в кодировке Unicode
Chr () — возвращает символ по его числовому коду. Может использоваться в паре с функцией Asc(), но чаще всего её применяют, когда нужно вывести служебный символ (например кавычки — » ), т.к. кавычки просто так в VBA-коде не ввести(нужно ставить двойные). Я обычно именно эту функцию и использую.
Dim sWord As String sWord = Chr(34) & «Слово в кавычках» & Chr(34)
Есть варианты этой функции — ChrB() и ChrW(). Работают аналогично таким же вариантам для функции Asc().
InStr () и InStrRev ()— одна из самых популярных функций. Позволяет обнаружить в теле строковой переменной символ или последовательность символов и вернуть их позицию. Если последовательность не обнаружена, то возвращается 0.
Dim sStr As String sStr = «w» If InStr(1, «Hello, World!», sStr, vbTextCompare) > 0 Then MsgBox «Искомое слово присутствует!» Else MsgBox «Искомое слово отсутствует!» End If
Разница функций в том, что InStr() ищет указанное слово от начала строки, а InStrRev() с конца строки
Left (), Right (), Mid ()— возможность взять указанное вами количество символов из существующей строковой переменной слева, справа или из середины соответственно.
Dim sStr As String sStr = «Hello, World!» MsgBox Mid(sStr, 1, 5)
Dim sStr As String sStr = «Hello, World!» MsgBox Replace(sStr, «Hello», «Bay»)
Dim sStr As String sStr = «Hello, World!» MsgBox StrConv(«Hello, World!», vbUpperCase)
В качестве второго параметра параметра могут применяться константы:
- vbUpperCase: Преобразует все текстовые символы в ВЕРХНИЙ РЕГИСТР
- vbLowerCase: Преобразует все текстовые символы в нижний регистр
- vbProperCase: Переводит первый символ каждого слова в Верхний Регистр
- *vbWide: Преобразует символы строки из однобайтовых в двухбайтовые
- *vbNarrow: Преобразует символы строки из двухбайтовых в однобайтовые
- **vbKatakana: Преобразует символы Hiragana в символы Katakana
- **vbHiragana: Преобразует символы Katakana в символы Hiragana
- ***vbUnicode: Преобразует строку в Юникод с помощью кодовой страницы системы по умолчанию
- ***vbFromUnicode: Преобразует строку из Юникод в кодовую страницу системы по умолчанию
* применимо для локализацией Дальнего востока
** применимо только для Японии
*** не поддерживается операционными системами под управлением Macintosh

StrReverse () — «перевернуть» строку, разместив ее символы в обратном порядке. Функция работает только начиная от Excel 2000 и выше. Пример использования функции, а так же иные методы переворачивания слова можно посмотреть в этой статье: Как перевернуть слово?

Tab () — еще одна функция, которая используется для форматирования вывода на консоль. Размножает символы табуляции в том количестве, в котором вы укажете. Если никакое количество не указано, просто вставляет символ табуляции. Для вставки символа табуляции в строковое значение можно также использовать константу vbTab.

String () — позволяет получить строку из указанного количества символов (которые опять-таки указываются Вами). Обычно используются для форматирования вывода совместно с функцией Len().

БЛОГ

Только качественные посты

Практический справочник функций Microsoft Excel с примерами их использования

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

Отсюда вытекает вопрос: Сколько нужно знать функций Excel, чтобы решать практически любую задачу в Excel?

Могу с уверенностью, опираясь на свой 17 летний профессиональный опыт работы в Excel, сказать, что достаточно освоить всего около 100 функций…

Представляю Вам ТОП-50 самых главных функций в Microsoft Excel с примерами их использования

– изучив данные Excel функции, у Вас будет достаточно теоретических знаний, чтобы решать практически любую задачу в Excel

( Для перехода к примерам нажмите на название функции. Все примеры — это ссылки на лучшие статьи уважаемых специалистов по Excel и наших партнеров)

1. СУММ / СРЗНАЧ / СЧЁТ / МАКС / МИН (SUM / AVERAGE / COUNT / MAX / MIN) — [Базовые формулы Excel]
2. ВПР (VLOOKUP) — [Ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце]
3. ИНДЕКС (INDEX) — [По индексу получает значение из ссылки или массива]
4. ПОИСКПОЗ (MATCH) — [Ищет значения в ссылке или массиве]
5. СУММПРОИЗВ (SUMPRODUCT) — [Вычисляет сумму произведений соответствующих элементов массивов (позволяет работать с массивами без формул массива)]
6. АГРЕГАТ / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (AGGREGATE / SUBTOTALS) — [Возвращает общий итог или промежуточный итог в списке или базе данных с учетом фильтров или без учета фильтров]
7. ЕСЛИ (IF) — [Выполняет проверку условия]
8. И / ИЛИ / НЕ (AND / OR / NOT) — [Логические условия, как правило для функции ЕСЛИ]
9. ЕСЛИОШИБКА (IFERROR) — [Если формула возвращает ошибку то что]
10. СУММЕСЛИМН (SUMIFS) — [Суммирует ячейки, удовлетворяющие заданным критериям. Допускается указывать более одного условия]
11. СРЗНАЧЕСЛИМН (AVERAGEIFS) — [Возвращает среднее арифметическое значение всех ячеек, которые соответствуют нескольким условиям]
12. СЧЁТЕСЛИМН (COUNTIFS) — [Подсчитывает количество ячеек, которые соответствуют нескольким условиям]
13. МИНЕСЛИ / МАКСЕСЛИ (MINIFS / MAXIFS) — [Возвращает минимальное/максимальное значение всех ячеек, которые соответствуют нескольким условиям]
14. НАИБОЛЬШИЙ / НАИМЕНЬШИЙ (LARGE / SMALL) — [Возвращает k-ое наибольшее/наименьшее значение в множестве данных]
15. ДВССЫЛ (INDIRECT) — [Определяет ссылку, заданную текстовым значением]
16. ВЫБОР (CHOOSE) — [Выбирает значение из списка значений по индексу]
17. ПРОСМОТР (LOOKUP) — [Ищет значения в массиве]
18. СМЕЩ (OFFSET) — [Определяет смещение ссылки относительно заданной ссылки]
19. СТРОКА / СТОЛБЕЦ (ROW / COLUMN) — [Возвращает номер строки/столбца, на который указывает ссылка]
20. ЧИСЛСТОЛБ / ЧСТРОК (COLUMNS / ROWS) — [Возвращает количество столбцов/строк в ссылке]
21. ОКРУГЛ / ОКРУГЛТ / ОКРУГЛВНИЗ / ОКРУГЛВВЕРХ (ROUND / MROUND / ROUNDDOWN / ROUNDUP) — [Округляет число до указанного количества десятичных разрядов]
22. СЛЧИС / СЛУЧМЕЖДУ / РАНГ (RAND / RANDBETWEEN / RANK) — [Возвращает случайное число]
23. Ч (N) — [Возвращает значение, преобразованное в число]
24. ЧАСТОТА (FREQUENCY) — [Находит распределение частот в виде вертикального массива]
25. СЦЕПИТЬ / СЦЕП / ОБЪЕДИНИТЬ / & (CONCATENATE / CONCAT / TEXTJOIN / &) — [Объединения двух или нескольких текстовых строк в одну]
26. ПСТР (MID) — [Выдает определенное число знаков из строки текста, начиная с указанной позиции]
27. ЛЕВСИМВ / ПРАВСИМВ (LEFT / RIGHT) — [Возвращает заданное количество символов текстовой строки слева / права]
28. ДЛСТР (LEN) — [Определяет количество знаков в текстовой строке]
29. НАЙТИ / ПОИСК (FIND / SEARCH) — [Поиск текста в ячейке с учетом / без учета регистр]
30. ПОДСТАВИТЬ / ЗАМЕНИТЬ (SUBSTITUTE / REPLACE) — [Заменяет в текстовой строке старый текст новым]
31. СТРОЧН / ПРОПИСН / ПРОПНАЧ (LOWER / UPPER) — [Преобразует все буквы текста в строчные/прописные/ или первую букву в каждом слове текста в прописную]
32. ГИПЕРССЫЛКА (HYPERLINK) — [Создает ссылку, открывающую документ, находящийся на жестком диске, сервере сети или в Интернете]
33. СЖПРОБЕЛЫ (TRIM) — [Удаляет из текста все пробелы, за исключением одиночных пробелов между словами]
34. ПЕЧСИМВ (CLEAN) — [Удаляет все непечатаемые знаки из текста]
35. СОВПАД (EXACT) — [Проверяет идентичность двух текстов]
36. СИМВОЛ / ПОВТОР (CHAR / REPT) — [Возвращает знак с заданным кодом/Повторяет текст заданное число раз]
37. СЕГОДНЯ / ТДАТА (TODAY / NOW) — [Возвращает текущую дату в числовом формате / Возвращает текущую дату и время в числовом формате]
38. МЕСЯЦ / ГОД (MONTH / YEAR) — [Вычисляет год / месяц от заданной даты]
39. НОМНЕДЕЛИ (WEEKNUM) — [Преобразует дату в числовом формате в число, которое указывает, на какую неделю года приходится дата]
40. ДАТАЗНАЧ (DATEVALUE) — [Преобразует дату из текстового формата в числовой]
41. РАЗНДАТ (DATEDIF) — [Вычисляет количество дней, месяцев или лет между двумя датами]
42. РАБДЕНЬ (WORKDAY) — [Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней]
43. ЯЧЕЙКА (CELL) — [Возвращает сведения о формате, расположении или содержимом ячейки]
44. ТРАНСП (TRANSPOSE) — [Выдает транспонированный массив]
45. ПРЕОБР (CONVERT) — [Преобразует число из одной системы мер в другую]
46. ПРЕДСКАЗ (FORECAST) — [Вычисляет или предсказывает будущее значение по существующим значениям линейным трендом]
47. ТИП.ОШИБКИ (ERROR.TYPE) — [Возвращает числовой код, соответствующий типу ошибки]
48. ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA) — [Возвращает данные, хранящиеся в сводной таблице]
49. БДСУММ (DSUM) — [Суммирует числа в поле (столбце) записей списка или базы данных, которые удовлетворяют заданным условиям]
50. В качестве бонуса рекомендую изучить Пользовательские форматы в Excel.

После освоения данных функций, следующим этапом рекомендую осваивать инструменты Бизнес- аналитики Business Intelligence (BI)

В Excel к инструментам бизнес-аналитики уровня Self-Service BI относятся бесплатные надстройки «Power»:

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

Ну и если Вы со временем поймете, что возможностей Excel для решения ваших аналитических задач недостаточно, то вам пора переходить к изучению промышленных решений уровня Business Intelligence (BI)

Построение графика в Excel и использование функции ЛИНЕЙН

Рассмотрим результаты эксперимента, приведенные в исследованном выше примере.

Исследуем характер зависимости в три этапа:

— Построим график зависимости.

— Построим линию тренда (в данном случае это прямая ).

— Получим числовые характеристики коэффициентов этого уравнения.

Решение

Построение графика зависимости.

1. Выделим интервал А1:В25.

2. Вызовем Мастер диаграмм, нажав соответствующую кнопку на панели инструментов.

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

4. На 1 шаге в диалоговом окне Мастера диаграмм интервал А1:В25 должен быть указан, если это не так укажите. Нажмите Шаг>.

5. На 2 шаге выберите тип диаграммы XY-точечная.Нажмите Шаг>.

6. На 3 шаге выберите первый тип автоформата. Нажмите Шаг>

7. На 4 шаге укажите следующие параметры:

8. Отвести 1 столбец для данных по оси Х; отвести 1 строку для текста легенды. Нажмите Шаг>.

9. На 5 шаге в окне «Название диаграммы: » введите заголовок «Линейная аппроксимация»; в окне «Категорий [X]:» введите «x»; в окне «Значений [Y]:» введите «y». Нажмите Закончить.

Построение линии тренда

Для построения линии тренда выполним следующую последовательность действий:

Дважды щелкнем по диаграмме. Диаграмма активизируется.

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

Вставляем линию тренда, воспользуемся меню Вставка – Линия тренда.

Появиться диалоговое окно «Линия тренда» выберем на вкладке «Тип» (Рис.2) линейный тип и перейдем к вкладке «Параметры».

На вкладке «Параметры» (Рис.3) потребуем показывать уравнение тренда на диаграмме и показывать значение , поставив их в соответствующие клетки. Нажмем кнопку ОК.

На диаграмме появится линия тренда с соответствующим уравнением. Также изменится легенда. При желании текстовое поле с уравнением и значением , а также название координат x и y, можно оттащить в более удобное место, как это сделано на Рис 4.

Для построения квадратичной аппроксимации на четвертом шаге в диалоговом окне «Линия тренда» выберем на вкладке «Тип» (Рис.2) полиномиальный тип степень 2. Результат представлен на рис.5.

Для построения экспоненциальной аппроксимации на четвертом шаге в диалоговом окне «Линия тренда» выберем на вкладке «Тип» (Рис.2) экспоненциальный тип. Результат представлен на рис.6.

Сравнивая результаты, полученные при помощи функции ЛИНЕЙН видим что они полностью совпадают с вычислениями, проведенными выше. Это указывает на то, что вычисления верны.

Примечание: Полученное при построении линии тренда значение коэффициента детерминированности для экспоненциальной зависимости не совпадает с истинным значением (это значение было сосчитано вручную выше) поскольку при вычислении коэффициента детерминированности с помощью функции ЛИНЕЙН используются не истинные значения , а преобразованные значения с дальнейшей линеаризацией.

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

Для построения числовых характеристик необходимо создать табличную формулу, которая будет занимать 5 строк и 2 столбца. Этот интервал может располагаться в произвольном месте на рабочем листе. В этот интервал требуется ввести функцию ЛИНЕЙН. Для этого выполняем следующую последовательность действий:

— Выделите область A65:B69.

— Вызовите Мастер функций.

— Выберите функцию Линейн.

— Определим аргументы функции.

— В качестве изв_знач_уукажите В1:В25.

— В качестве изв_знач_хукажите А1:А25.

— Третье поле Константаоставьте пустым.

— В четвертом поле статнаберите истина.

— Нажмите кнопку Закончить.

— Установите курсор в строку формул.

— Нажмите комбинацию клавиш Ctrl+Shift+Enter, это обеспечит ввод табличной формулы!

В результате должны заполниться все ячейки интервала A65:B69(см. табл.9).

Поясним назначение некоторых величин, расположенных в табл.9.

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

A69 — коэффициент детерминированности.

A70 — F-наблюдаемое значение.

B68 — число степеней свободы.

A69 — регрессионная сумма квадратов.

B69 — остаточная сумма квадратов.

Рассмотрим назначение функции ЛИНЕЙН.

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

или ,

где зависимое значение y является функцией независимого значения x. Значения m — это коэффициенты, соответствующие каждой независимой переменной x, а b — это постоянная. Заметим, что y, x и m могут быть векторами.

Функция ЛИНЕЙН возвращает массив . ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.

Синтаксис

ЛИНЕЙН(известные_значения_y;известные_значения_x;конст; статистика)

Известные_значения_y — это множество значений y, которые уже известны для соотношения .

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

Известные_значения_x — это множество значений x, которые уже известны для соотношения .

— Массив известные_значения_x может содержать одно или несколько множеств переменных.

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

— Если используется более одной переменной, то известные_значения_y должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец).

Конст— это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

— Если констимеет значение ИСТИНА или опущена, то b вычисляется обычным образом.

— Если констимеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются так, чтобы выполнялось соотношение y = mx .

Статистика— это логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии.

— Если статистикаимеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику, так что возвращаемый массив будет иметь вид:

. (17)

— Если статистика имеет значение ЛОЖЬ или опущена, то функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.

Дополнительная регрессионная статистика приведена в табл. 10

Excel функция Len

У меня есть проблема в excel, и я не понял, как это сделать правильно. во всяком случае, я приведу вам пример, чтобы объяснить это правильно.

проблема в том, что я хотел видеть Maar4 как Maar04, а не как Maar4.

Я уже проверил formatcell, но до сих пор не нашел его.

это код, который я написал:

редактировать: проблема решена. вы, ребята, потрясающие. спасибо

4 Ответов

Я не уверен, почему вы используете UPPER() , если вы ожидаете Maar04 . Если вы хотите получить Marr04 , вы бы использовали:

Если вы хотите получить MAAR04 , то вы должны использовать верхний регистр:

Если вы совершенно уверены, что длина никогда не будет идти выше 99 вы можете попробовать этот хак:

Попробуйте использовать функцию TEXT для принудительного ввода 2 цифр

Для этого нужно использовать REPT().

Ваша формула MIGHT выглядит примерно так;