Excel строка под строкой

Посмотреть видео — вставлять пустую строку после каждой строки в Excel

Людям, работающим с большими наборами данных, часто нужны простые вещи, такие как вставка / удаление строк или столбцов.

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

Вставлять пустую строку после каждой другой строки

В этом руководстве я покажу вам несколько действительно простых способов вставить пустую строку после каждой строки в существующем наборе данных (или каждой n-й строки).

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

Использование вспомогательного столбца и функции сортировки

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

Ниже приведены шаги по вставке пустых строк между существующими строками:

1. Вставьте пустой столбец слева от набора данных. Для этого щелкните правой кнопкой мыши заголовок самого левого столбца и нажмите «Вставить».
2. Введите текст «HelperColumn» в A1 (вы можете использовать любой текст, который хотите).
3. Введите 1 в ячейку A2 и 2 в ячейку A3.
4. Выделите обе ячейки и поместите курсор в нижний правый угол выделения. Когда курсор изменится на значок плюса, дважды щелкните по нему. Это заполнит весь столбец увеличивающимися числами.
5. Перейдите к последней заполненной ячейке в вспомогательном столбце, а затем выберите ячейку под ней.
6. Введите 1 в эту ячейку и 2 в ячейку под ней.
7. Выделите обе ячейки и поместите курсор в нижний правый угол выделения.
8. Когда курсор изменится на значок плюса, щелкните и перетащите его вниз. Это заполнит ряд чисел (как мы получили на шаге 3). Убедитесь, что у вас больше цифр, чем в наборе данных. Например, если в наборе данных 10 записей, убедитесь, что на этом шаге заполнено не менее 10 ячеек. После этого ваш набор данных будет выглядеть, как показано ниже.
9. Выберите весь набор данных (включая все ячейки вспомогательного столбца).
10. Перейдите на вкладку «Данные».
11. Нажмите на опцию сортировки
12. В диалоговом окне Сортировка используйте следующие параметры:
    • Сортировать по: Помощник
    • Сортировка: значение ячейки
    • Порядок: от наименьшего к наибольшему
13. Щелкните ОК. Это даст вам набор данных, как показано ниже.
14. Удалите вспомогательный столбец.

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

На самом деле это не вставка пустой строки. Этот метод сортировки просто переупорядочивает данные, помещая пустые строки из-под набора данных между строками в наборе данных.

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

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

Ниже приведены шаги для этого:

1. Вставьте пустой столбец слева от набора данных. Для этого щелкните правой кнопкой мыши заголовок самого левого столбца и нажмите «Вставить».
2. Введите текст «HelperColumn» в A1 (вы можете использовать любой текст, который хотите).
3. Введите 1 в ячейку A2 и 2 в ячейку A3.
4. Выделите обе ячейки и поместите курсор в нижний правый угол выделения. Когда курсор изменится на значок плюса, дважды щелкните по нему. Это заполнит весь столбец увеличивающимися числами.
5. Перейдите к последней заполненной ячейке в вспомогательном столбце, а затем выберите ячейку под ней.
6. Введите 2 в эту ячейку и 4 в ячейку под ней. Мы используем числа, кратные 2, поскольку нам нужна одна пустая строка после каждых двух строк.
7. Выделите обе ячейки и поместите курсор в нижний правый угол выделения.
8. Когда курсор изменится на значок плюса, щелкните и перетащите его вниз. Это заполнит ряд чисел (как мы получили на шаге 3). Убедитесь, что вы получили число больше, чем есть в наборе данных. Например, если в наборе данных 10 записей, убедитесь, что вы набрали хотя бы до числа 10.
9. Выберите весь набор данных (включая все ячейки вспомогательного столбца).
10. Перейдите на вкладку «Данные».
11. Нажмите на опцию сортировки
12. В диалоговом окне Сортировка используйте следующие параметры:
    • Сортировать по: Помощник
    • Сортировка: значение ячейки
    • Порядок: от наименьшего к наибольшему
13. Щелкните ОК. Это даст вам окончательный набор данных, как показано ниже (с пустой строкой после каждой второй строки набора данных).
14. Удалите вспомогательный столбец.

Точно так же, если вы хотите вставить пустую строку после каждой третьей строки, используйте цифры 3, 6, 9 и так далее на шаге 5.

Использование простого кода VBA

Хотя для вставки альтернативных пустых строк в Excel требуется множество обходных путей, с VBA это совсем несложно.

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

Ниже приведен код VBA, который вставляет пустую строку после каждой строки в наборе данных:

Sub InsertAlternateRows () 'Этот код будет вставлять строку после каждой строки в выделении' Этот код был создан Sumit Bansal с сайта trumpexcel.com Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng. CompleteRow.Count For i = 1 To CountRow ActiveCell.Offset (1, 0) .EntireRow.Insert ActiveCell.Offset (2, 0). Select Next i End Sub

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

Вот шаги, чтобы разместить этот код VBA в редакторе VB в Excel:

1. Скопируйте приведенный выше код
2. Перейдите на вкладку «Разработчик» и выберите вариант «Visual Basic». Это откроет редактор VB. Вы также можете использовать сочетание клавиш ALT + F11
3. В редакторе VB щелкните правой кнопкой мыши любой объект в Project Explorer.
4. Наведите курсор на параметр «Вставить» и нажмите «Модуль». Это вставит новый модуль
5. В окне кода модуля вставьте приведенный выше код.

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

Вот шаги, чтобы использовать код для вставки пустых строк после каждой строки:

1. Выберите весь набор данных (кроме строки заголовка)
2. Перейдите на вкладку «Разработчик» (если у вас нет вкладки «Разработчик», нажмите здесь, чтобы узнать, как ее получить)
3. Нажмите на опцию «Макросы».
4. В диалоговом окне «Макрос» выберите макрос — InsertAlternateRows.
5. Нажмите «Выполнить»

Вот и все!

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

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

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

Если вы хотите вставить пустую строку после каждой второй строки, вы можете использовать следующий код:

Sub InsertBlankRowAfterEvery2ndRow () 'Этот код будет вставлять строку после каждой второй строки в выделении' Этот код был создан Sumit Bansal из trumpexcel.com Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng .EntireRow.Count For i = 1 To CountRow / 2 ActiveCell.Offset (2, 0) .EntireRow.Insert ActiveCell.Offset (3, 0). Select Next i End Sub

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

Вы поможете развитию сайта, поделившись страницей с друзьями

Вставить пустые строки в Excel через одну.

​Смотрите также​​myActiveColumn = ActiveCell.Column​ ​ совпадают.​ ​myLastRow = Cells(Rows.Count,​​Rows(i).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​​И, наконец, чтобы ввести​ обращениями к диапазону.​ = i +​ «после», а то​ ks, k1 As​s = s​Есть файл с​ выделено. Порядок столбцов​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ОСТАТ(СТРОКА();3);»»;ИНДЕКС(C$3:C$11;СТРОКА()/3))​kreg​ его, а затем​ставить пустые строки в​
​Рассмотрим несколько вариантов,​
​myLastRow = Cells(Rows.Count,​ ​If tblMy(i, 1)​​ ActiveCell.Column).End(xlUp).Row​
​End If​ эту формулу во​
​ Можно добавить в​ 2 Loop End​ часто бывает, что​ Long​ & «,» &​ промеченными группами строк:​ вставки, так же​ASUS​: А как сделать​
​ на вкладке​Excel через одну.​как​ myActiveColumn).End(xlUp).Row​ <> tblMy(i -​’Берём в VBA-таблицу​metka:​ все выделенные (пустые)​
​ цикл строку DoEvents​ ​ Sub ‘Удаляем Sub​​ вопрос далеко не​
​Dim x As​ i & «:»​Столбец 1 Столбец​ зависит от порядка​: Привет всем.Есть таблица​ так что-бы строки​
​Главная​Нам нужно вставить​вставить пустые строки в​’Берём в VBA-таблицу​ 1, 1) Then​
​ «tblMy» данные из​Next i​ ячейки нажимаем​ и пойти пить​ DeleteRows() Dim i​ полностью соответствует реальному​ Long​ & i​ 2​
​ их выделения. Например,​
​ со строками идущей​ ​ добавлялись не во​
​нажмите кнопку​ пустые строки в​Excel через одну​ «tblMy» данные из​’Вставка двух пустых​ двух столбцов.​
​Application.ScreenUpdating = True​Ctrl + Enter​ чай, либо пройти​
​ As Long: i​ положению вещей…​With Application​
​Next​Какое-то слово 1​ через одну и​ подряд. Как добавить​ всем документе, а​
​Вставить​ ​ несколько столбцов таблицы,​​,​
​ двух столбцов.​ строк между группами.​tblMy() = Cells(1,​End Sub​вместо обычного Enter.​ по ссылке, которую​
​ = 200 ‘200​
​anddrei55​
​.ScreenUpdating = False​​s = Mid(s,​ ​Какое-то слово 1​
​ т.п.​ формулой между этими​ только в выбранном​и выберите пункт​ на всю ширину​две строки, ячейки,​tblMy() = Cells(1,​Rows(i).Resize(2).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​ ActiveCell.Column).Resize(myLastRow, 2).Value​SergeyKorotun​
​ И все! Просто​ дал mtts54 выше​ — количество проверяемых​: Файл-пример прикрепил, смотреть​
​.Calculation = xlCalculationManual​ 2, 99999)​Какое-то слово 1​Теперь добавим в прайс-лист​ строками один пустую​ диапазоне.​​Удалить столбцы с листа​ таблицы, т.д. Сначала​ т.д. Бывает, в​ myActiveColumn).Resize(myLastRow, 2).Value​’Делаем границу между​For i =​: Работает, но ожидал​ и красиво.​ в посте #14,​ строк Do While​ по вкладкам.​nRow = Cells(Rows.Count,​Range(s).Insert​
​Какое-то слово 2​ заголовок и новую​ строку. Приложу файл​Hugo​.​ напишем формулу как​ большой заполненной таблице,​’Двигаемся до третьей​ двумя вставленными​ UBound(tblMy, 1) To​ другую реализацию, за​
​В качестве завершающего мазка​ — там варианты​ i > 0​Z​ 1).End(xlUp).Row​End Sub​Какое-то слово 2​ позицию товара «Товар​ чтобы была понятно.​: А так правильней​Можно также щелкнуть правой​ во втором варианте.​ нужно вставить пустые​ строки, т.к. в​’пустыми строками.​ 2 Step -1​
​ один проход. Недостаток​ я советовал бы​ оптимизированных макросов, рассчитанных​ If IsEmpty(Cells(i, 1))​: А по-простому -​’Range(Cells(2, 20), Cells(Cells(Rows.Count,​Ярослав​Какое-то слово 2​ новинка». Для этого​ Заранее спасибо за​ будет (и на​

excel-office.ru

Как вставлять и удалять ячейки, строки и столбцы

​ кнопкой мыши в​ Затем, скопируем эту​ строки между каждой​ первой строке​

​With Rows(i).Borders(xlEdgeBottom)​​’Если данные в​ в том, что​ заменить все созданные​ на большие объёмы​ Then Cells(i, 1).EntireRow.Delete​ отсортировать, выделить да​ 1).End(xlUp).Row + 1,​

Вставка и удаление столбца

1. ​: Sub Макрос3()​Какое-то слово 2​ вставим две новых​​ предоставленный ответ.​​ 2 умножать не​​ верхней части столбца​​ формулу вправо на​​ строкой таблицы. Вручную​​’находится заголовок и​

2. ​.LineStyle = xlDash​ первом столбце в​ если добавится еще​​ формулы на значения,​​ данных.​​ i = i​​ грохнуть «пустоту» -​​ 20))​​’​

   ​Какое-то слово 3​ строки одновременно.​_Boroda_​ надо, да и​​ и выбрать команду​​ нужное количество столбцов.​​ это сделать сложно,​​ вторую строку​

Вставка и удаление строки

1. ​.Weight = xlMedium​ текущей строке и​ одна подгруппа по​​ ибо при сортировке​​anddrei55​​ — 1 Loop​​ «Ctrl+-«?..​​kv = InputBox(«Введите​​’ Макрос3 Макрос​

2. ​Какое-то слово 3​Выделите несмежный диапазон двух​: Читайте Правила форума.​​ можно iLastRow привлечь):​​Вставить​​Например, в нашей​​ долго. Но, Excel,​​’не нужно сравнивать​​End With​

   ​ строке,​ третьему столбцу, макрос​ или добавлении/удалении строк​: но я там​​ End Sub​​abricos29​​ количество строк для​​’ Макрос записан​

Вставка ячейки

1. ​В первом столбце​ ячеек A1;A4(обратите внимание​ Пункты 4 и​For i =​​или​​ таблице два столбца.​

2. ​ опять, дает нам​​ с первой.​​’Делаем заголовок у​’которая выше, не​ придется не немного​

support.office.com

Добавление пустых строк между заполненных

​ корректность формул может​​ увидел код только​
​anddrei55​: Я не так​ вставки между строками»,​
​ 13.09.2008 (zyaa)​ — текст, во​ вместо символа «:»​ 5q в особенности.​ 100 To 2​Удалить​ Мы копируем формулу​
​ возможность быстро вставить​For i =​ нижней группы.​ совпадают.​

​.​​ в ячейку D1.​ нужное количество строк​ UBound(tblMy, 1) To​

​Cells(i + 1,​​If tblMy(i, 1)​ третий блок, в​ все ячейки в​ строк, а быстрого​ огромное выручили, этого​

​ задачу, нашёл такое​​ks = InputBox(«Введите​ пустых строк вниз​

​ номер группы. Нужно​​ — это значит,​

​Создавая разного рода новые​​Cells(i, 1).EntireRow.Insert​Чтобы вставить строку, выделите​Теперь выделяем ячейки​ в нужном месте.​ 3 Step -1​ myActiveColumn).Value = «Шапка​

​Next i​​ ее, а затем​ C1 и D1​Первый вариант.​
​’Если данные в​
​ группы»​ 1, 1) Then​

​ возрастет.​​ и тут же​Irregular Expression​Jack Famous​ на форуме :​
​ 1)​

​ ячейки​​ пустых строки после​ диапазона, для убедительности​ прайсы, нельзя заранее​roka​

​ на вкладке​​ и копируем (протягиваем)​Вставить пустые строки в​ первом столбце в​

planetaexcel.ru

Вставить заданное количество пустых ячеек через строку (Иное)

​’Если данные во​​Rows(i).Resize(2).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​Совсем не знаю​ вставьте обратно с​: anddrei55, значит, придётся​: anddrei55, а мой​ Sub УдалениеСтрок() Dim​k1 = InputBox(«Введите​’ Сочетание клавиш:​ каждой группы. Как​ введите A1;A4 в​ предвидеть количество необходимых​: еще как вариант,​Главная​ их вниз до​Excel через одну​ текущей строке и​ втором столбце в​

​With Rows(i).Borders(xlEdgeBottom)​​ VBА, но из​ помощью​ создать отдельную тему​ вариант чем не​
​ r As Long,​ первую строку», ,​​ Ctrl+я​

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

​.​​ строке,​ текущей строке и​.LineStyle = xlDash​ вашего кода вижу​Специальной вставки (Paste Special)​.​ подошёл?​

​ Rng As Range​​ 1)​’​ максимально простым путем?​ нажмите Enter). Как​ Использование программы Excel​

​ место и сделать​​Вставить​ так.​

​У нас такая​​’которая выше, не​ строке,​.Weight = xlMedium​

​ что логические операции​​в контекстом меню,​
​А в качестве​

​mtts54​​ For r =​kv1 = Val(kv)​k = 10​OLEGOFF​ выделять несмежные диапазоны​ – это в​ActiveCell.Resize(i).EntireRow.Insert​и выберите пункт​Таблицу Excel можно​

​ таблица.​​ совпадают.​’которая выше, не​End With​
​ поддерживаются.​

excelworld.ru

Как вставить строку или столбец в Excel между строками и столбцами

​ выбрав параметр​ идеи, в каком​: ,​ 1 To ActiveSheet.UsedRange.Row​ks1 = Val(ks)​ ‘ кол-во вставок​: Sortafreel,попробуйте такой макрос.​ вы уже знаете​ значительной степени создание​где i необходимое​Вставить строки на лист​ настроить так, что​

​Если таблица не большая,​If tblMy(i, 1)​ совпадают.​’Если данные во​

​На каком то​Значения (Values)​ направлении двигаться, могу​нарисовал серию великолепных​ — 1 +​sh = kv1​ пустых строк​Выделяете столбец и​ из предыдущих уроков.​ и настройка таблиц,​ количество строк​.​ будут автоматически сделаны​ то можно сделать​

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

​ <> tblMy(i -​ElseIf tblMy(i, 2)​ втором столбце в​

​ несуществующем языке полупрограмму-полуалгоритм​. Так будет совсем​ предложить работать не​ кодов, работающих с​ ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r))​

1. ​ + ks1​For i =​ три раза жмете​Теперь снова используйте инструмент​ в процессе которой​Igor67​Чтобы удалить строку, выделите​ разделительные линии между​ так. Выделяем, при​
2. ​ 1, 1) Then​ <> tblMy(i -​ текущей строке и​ вижу так:​ хорошо.​

​ с листом и​ большой скоростью и​ = 0 Then​

​For i =​

Вставка нескольких столбцов между столбцами одновременно

​ 1 To k​ на кнопку​ «Главная»-«Вставка»-«Вставить строки на​ требуется вставка и​: А я бы​ ее, а затем​ строками, например, по​ нажатой клавише «Ctrl»,​’Вставка трёх пустых​ 1, 2) Then​ строке,​200?’200px’:»+(this.scrollHeight+5)+’px’);»>go bottom //стать на​SergeyKorotun​

​ строками, а с​ с разными условиями​ If Rng Is​ ks To nRow​m = 1​

​Сергей1985​ лист». На рисунке​ удаление различных элементов.​ это понял (для​ на вкладке​ датам, по ценам,​ все строки, под​ строк между группами.​Rows(i).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​’которая выше, не​ последнюю строку​

Как вставить строку в Excel между строками?

​: присоединил​ массивом, т.е. загружаете​ удаления строк,​ Nothing Then Set​ Step sh​For j =​

​: Имеется таблица из​ видно как вставить​Сначала рассмотрим способы вставки​ диапазона) как вставка​Главная​ т.д. Подробнее о​ которыми нужно вставить​Rows(i).Resize(3).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​End If​ совпадают.​st_1=A1 // присвоить​Скрипт​ свою таблицу с​

​в т.ч пустых,​ Rng = Rows(r)​If kv >​ 1 To m​ двадцати строк. Необходимо​ пустую строку в​

​ строк и столбцов​ ячеек:))​нажмите кнопку​ таком способе визуализации​ пустую строку (каждую​’Делаем границу между​Next i​ElseIf tblMy(i, 2)​

​ значение ячейки колонки​:​ лишними строками в​ по значению в​ Else Set Rng​

​ 1 Then​ActiveCell.Offset(j, 0).Select​ вставить по две​

Удаление строк и столбцов

​ Excel между строками.​ листа при создании​Cells(i, 1).EntireRow.Insert Shift:=xlDown​Вставить​ данных в таблице,​ строку отдельно, не​

​ двумя вставленными​Application.ScreenUpdating = True​ <> tblMy(i -​ А1 последней строки​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Procedure_1()​

​ массив, очищаете диапазон,​ ячейке и с​ = Union(Rng, Rows(r))​For x =​Selection.EntireRow.Insert​ пустых строки под​Несложно догадаться о втором​ таблиц.​

​я​и выберите пункт​ читайте в статье​ все вместе).​’пустыми строками.​End Sub​ 1, 2) Then​st_2=A2 // присвоить​Dim tblA() As​

​ обрабатываете массив до​ поддержкой * (любые​ End If Next​ 1 To kv​ActiveCell.Offset(j, 0).Select​

​ каждой существующей (то​ способе. Нужно выделить​Обратите внимание, в данном​: Ребята, а если​Удалить строки с листа​ «Разделительная линия строк​Когда выделим все​With Rows(i).Borders(xlEdgeBottom)​SergeyKorotun​Rows(i).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​ значение ячейки колонки​ Variant​ нужного вида и​

exceltable.com

Добавить пустые строки после каждой группы строк

​ символы).​​ r If Not​
​ Step 1​Selection.EntireRow.Insert​
​ есть должно получиться​ заголовки строк 1​
​ уроке указываются горячие​
​ нужно вставлять по​
​.​
​ в таблице Excel»​
​ нужные строки, отпускаем​
​.LineStyle = xlDash​
​: Немного не так.​
​End If​
​ А2 последней строки​
​Dim myLastRow As​ пишем его обратно​См., например:​ Rng Is Nothing​Cells(i + 1,​Next j​ не 20, а​ и 3. Кликнуть​ клавиши для добавления​

​ 45 строк, помогите​​Можно также щелкнуть правой​
​ здесь.​ клавишу «Ctrl». Нажимаем​.Weight = xlMedium​

CyberForum.ru

Вставка строки через строку

​ Вставляться должно не​​Next i​do while not​ Long​ в лист. Количество​Jack Famous​ Then Rng.Delete End​ 1).EntireRow.Insert​ActiveCell.Offset(1, 0).Select​ 60 строк таблицы,​ правой кнопкой по​ или удаления строк​ неумехе​ кнопкой мыши выделенную​

​Можно вставить в​​ на выделенную строку​End With​
​ «шапка группы», а​
​Application.ScreenUpdating = True​
​ bof() // цикл​

​Dim i As​​ строк макроса увеличится​: mtts54, благодарю​

​ Sub​​Next x​Next i​

​anddrei55​​mtts54​End If​End Sub​ первая строка, а​ строк и выбрать​ надо использовать после​: Как сделать чтоб​

​ опцию «Вставить».​​ выделения целой строки​
​ вставлялось по 2​
​Вставить​ формулы, написать любые​ функцию «Вставить» -​
​’Копируем заголовок из​ в которой подписаны​
​: Завтра девки мне​skip -1// подняться​’———————————————————————————————​ выполнения для 10000​
​ процесс? Если добавлять​
​ запрос: «удалить строки​.Calculation = xlCalculationAutomatic​
​: Мне тоже самое​
​ третья были бы​

​Чтобы добавить строку или​​ или столбца. Чтобы​
​ пустые строчки?​
​или​
​ данные. Затем, скрыть​ «Строку». Получится так.​
​ первой строки Excel-листа.​ наименования столбцов. В​ скажут спасибо, а​ на одну строку​
​’ГРУППИРОВКА ПО ПЕРВОМУ​ строк упадёт до​
​ 10000 пустых строк,​
​ по условию» или​.ScreenUpdating = True​ нужно сделать, только​
​ пустыми) Спасибо​ столбец в Excel​
​ выделить строку на​
​Lunatic​Удалить​
​ эти столбцы, строки​
​Добавить пустые столбцы в​
​’Копировать нужно, т.к.​
​ прикрепленном файле на​
​ я вам +​
​ вверх​
​ СТОЛБЦУ.​
​ нескольких секунд или​

​SergeyKorotun​​ в текущей строке​ ActiveCell.Column).End(xlUp).Row​ 90% времени пожирается​Как ускорить процесс​
​: anddrei55, проверяйтеКод из​ vbInformation, «Вставка строк»​ строку после диапазона​

planetaexcel.ru

Удаление пустых строк после каждой заполненной

​ Dim lr&, i&,​​Нашел на форуме​
​ до n (где,​Guest​В процессе работы с​ прайс, в котором​
​ пустых строк в​
​.​ можно установить ссылку​ меню выбираем функцию​ втором столбце в​ только в первый​ ними вставлять еще​здесь после текущей​
​ ActiveCell.Column).Resize(myLastRow, 1).Value​ работы с данными​ As Range Dim​ colDel%, colFull% colDel​ тему, как добавить​ n последняя не​

​: или a.offset(1,0).rows.insert не​​ Excel, удалять строки​
​ недостает нумерации позиций:​ диапазон через каждую​В окне​
​ на конкретную ячейку​ «Вставить столбец».​ текущей строке и​ столбец, участвующий в​ и первую строку,​
​ строки вставляем две​For i =​

​ (фильтрации, сортировки, подведения​​ d As Range​ = Application.InputBox(«Укажите ячейку​ пустую строку после​ пустая строка). Если​ помню точно​

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

​ строки с разделителем​​ UBound(tblA, 1) To​ итогов и т.д.)​ Dim k As​ СТОЛБЦА, по которому​ каждой заполненной.​ ячейка А(k) объединена​Влад​ приходится не реже​

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

​ другом листе. Например,​​ столбец добавлялся автоматически,​’которая выше, не​ и на строку​ группы была своя​

​ из пунктирной линии​​ 2 Step -1​ нужен непрерывный список,​ Integer Dim p​ определять пустые ячейки:»,​Вот код:​ с ячейкой А(k+1),​: Отличный способ надолго​ чем вставлять. Поэтому​ номеров позиций прайс-листа,​ Большая просьба ,​ или ячейку для​ у нас таблица​ читайте в статье​ совпадают.​ с шапкой. Но​ шапка​skip -1//​If tblA(i, 1)​ т.е. таблица без​ As Boolean p​ «Выбрать ОДНУ ячейку»,​Sub InsertRows() Dim​ то смотрим объединена​

​ зациклить макрос:)​​ стоит попрактиковаться.​ можно воспользоваться одним​ только понятным языком.​ вставки.​

​ со списком заказов​​ «Как добавить столбец​ElseIf tblMy(i, 2)​ параметр цикла можно​Скрипт​skip -1//возврат на​ <> tblA(i -​ разрывов (пустых строк​ = True Application.ScreenUpdating​ , Type:=8).Column colFull​ i As Long​ ли она со​Jom​Для наглядного примера удалим​ из двух способов:​ если вообще возможно​Beldiver​ от разных заказчиков.​ в Excel автоматически».​ <> tblMy(i -​ оставить и 1,​:​ текущую строку, в​ 1, 1) Then​ и ячеек -​ = False Application.Calculation​ = Application.InputBox(«Укажите ячейку​ For i =​ следующей итп, если​: +1. пока до​ из нашего прайс-листа​Перейдите курсором и активируйте​ , то без​: Вопрос, такого характера.​ Чтобы найти контактные​Если нужно вставить​ 1, 2) Then​

​ лишнюю первую шапку​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Procedure_1()​ VBA не надо,​Rows(i).Resize(2).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​ по возможности). На​ = xlCalculationManual Do​ СТОЛБЦА, по которому​
​ 2 To 200​ нет то заполняем​ строки 65535 не​ нумерацию позиций товара​ ячейку A1. Потом​ всяких там макросов.​Как в exele​ данные по конкретному​ несколько строк или​Rows(i).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​ можно удалить вручную.​Dim tblMy() As​ так как обрабатывается​With Rows(i).Borders(xlEdgeBottom)​ практике же часто​ While p p​ определять последнюю строку:»,​ Step 2 Cells(i,​ ее номером и​ дойдет.​ и столбец единиц​ перейдите на закладку​ очень нужно для​ добавить пустые строки​

​ заказчику, достаточно нажать​​ столбцов не во​End If​Скрипт​

​ Variant​​ массив​.LineStyle = xlDash​ мы имеем как​

​ = False Set​​ «Выбрать ОДНУ ячейку»,​
​ 1).EntireRow.Insert Next i​ вставляем после нее​как вариант.​ измерения – одновременно.​ «Главная» раздел инструментов​
​ работы !​ между заполнеными?​ на ссылку. Excel​ всей таблице, то​Next i​
​: SergeyKorotun, на Excel-листе​

​Dim myLastRow As​​endif​

​.Weight = xlMedium​​ раз таблицы с​ d = Nothing​ , Type:=8).Column lr​ End Sub​ пустую строку (ну​
​yfirst =20​Выделяем несмежный диапазон ячеек​ «Ячейки» кликните по​
​Serge_007​То есть, есть​ перейдет в другую​ можно использовать другие​Application.ScreenUpdating = True​ в первой строке​ Long​if st_1=a1and st_2<>a2​End With​ пропущенными пустыми ячейками​ Intersect(Selection, ActiveSheet.UsedRange).Select For​ = Cells(Rows.Count, colFull).End(xlUp).Row​А как прописать​ или строки в​ylast =60​ A1;D1 и выбираем​ инструменту «Вставить» из​: Без макросов вставить​ список с 100​ таблицу на другом​ способы. Смотрите об​End Sub​ есть заголовок.​Dim myActiveColumn As​ // перехода через​End If​ — например после​ Each ch In​ Set gr =​ макрос на обратное​ другой части вставить…)​for y= yfirst​ «Главная»-«Удалить»-«Удалить столбцы с​ выпадающего списка выберите​ пустые строки в​ строками, как автоматически​ листе именно на​ этом статью «Как​
​SergeyKorotun​Есть два варианта​ Long​ группу нет, ачерез​Next i​ копирования результатов сводных​ Selection If ch.Value​ Nothing For i​

​ действие, чтоб удалить​​наверное это сложно,​ to ylast step​ листа». Контекстным меню​ опцию «Вставить столбцы​ исходные данные -​ после каждой строчки​ строку этого заказчика.​ добавить строку, столбец​:​ вставки этого заголовка​Dim i As​ подгруппу есть​’———————————————————————————————​ таблиц или выгрузок​ = 0 Then​

​ = lr To​​ пустые строки после​ раз мне никто​ 3​ так же можно​ на лист».​

​ только вручную​​ добавить пустую? Чтоб​Можно применить такие​​ в Excel».​
​Алекс​ в другие места​ Long​здесь нужно добавить​’ГРУППИРОВКА ПО ВТОРОМУ​ в Excel из​ If d Is​ 1 Step -1​ заполненных? Скрыть через​ еще не ответил​Rows(y + 1).Insert​ удалять, если выделить​Щелкните правой кнопкой мышки​Формулами можно в​ получилось в итоге​ ссылки в списках​Второй вариант.​: Добрый день!​ листа:​Application.ScreenUpdating = False​ одну пустую строку​ СТОЛБЦУ.​ внешних программ. Таким​ Nothing Then Set​ If Len(Cells(i, colDel))​

planetaexcel.ru

Заполнение пустых ячеек

​ фильтр не вариант.​ :)​ Shift:=xlDown​ заголовки A1и D1,​ по заголовку столбца​ соседнем столбце:​ 200 строк, заполненная-пустая-заполненная-пустая?​ по классам, ссылки​Вставить пустые ячейки в​Необходим аналогичный макрос​с сохранением форматирования. Под​’Берём номер столбца,​skip -1 //возврат​’Т.к. количество строк​ образом, возникает необходимость​ d = ch​ = 0 Then​Irregular Expression​…но я все​next y​ а не ячейки.​ A. Из появившегося​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЦЕЛОЕ(СТРОКА()/3)=СТРОКА()/3;ИНДЕКС(C$3:C$11;СТРОКА()/3);»»)​

​Excel через одну.​ — добавление пустой​

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

​ k = 1​​ If gr Is​​: anddrei55,​​ таки надеюсь это​​Jom​Удаление строк происходит аналогичным​​ контекстного меню выберите​ ​_Boroda_​ ​ в ворде проделать(потом​​ родителей, списки товаров,​

​Таблица, как в​ строки после изменения​ шрифта, размер шрифта,​ ячейка,​ в VBA не​ провести некоторые действия.​ таблицы значениями из​

​ Else Set d​ Nothing Then Set​1) этот код​ победить )​​: хотя вру. вышепредложенный​​ способом, только нужно​ опцию «Вставить»​: В общем случае​

​ в ексель загоню),каким​ разделов, сотрудников по​ первом варианте. Нам​ значения в столбце​ заливка ячеек и​’в переменную. Т.к.​ надо, так как​myLastRow = Cells(Rows.Count,​ верхних ячеек, то​ = Union(d, ch)​ gr = Cells(i,​ не проверяет, заполнена​Igor67​​ код с offset​​ выбирать в соответствующее​Теперь можно заполнить новый​​В ячейке I1​​ другим редактором?​ отделам, т.д. Как​

planetaexcel.ru

Как вставить пустые строки после изменения значений в столбц (Форматирование)

​ нужно вставить пустые​​ и суммирование по​

​ т.д.;​​ этот номер столбца​​ обрабатывается массив​
​ ActiveCell.Column).End(xlUp).Row​ бишь...​
​ k = k​ colDel) Else Set​
​ строка или нет;​: Как то Pavel55​
​ вообще не рабочий.​
​ меню инструмента. А​
​ столбец номерами позиций​ количество пустых строк,​
​Pavel55​ сделать такие гиперссылки,​
​ ячейки между заполненными​ всем столбцам в​вставка только текста заголовка.​
​'в коде нужен​endif​
​'Берём в VBA-таблицу​  из  ​ + 1 End​
​ gr = Application.Union(gr,​2) для оформления​ выкладывал макрос, вот​
​ даже если изменить​
​ в контекстном меню​
​ прайса.​
​ которые нужно добавить​
​: Может так?​
​ смотрите в статье​
​ ячейками в столбце​
​ этой пустой строке,​
​Второй вариант будет​ несколько раз и​
​// если оба​ "tblA" данные из​ сделать ​
​ If End If​ Cells(i, colDel)) End​
​ кода есть кнопка​ он с небольшой​ его немного, он​
​ – без изменений.​​200?'200px':''+(this.scrollHeight+5)+'px');">=ЕСЛИ(ОСТАТ((СТРОКА()-СТРОКА(G$3));(I$1+1))=0;ИНДЕКС(C$3:C$11;(СТРОКА()-СТРОКА(G$3))/(I$1+1)+1);"")​
​Sub InsertRows()​ "Гиперссылка в Excel​ А.​
​ подскажите как добиться​ быстрее работать, если​
​ удобнее указывать​ условия не выполняются,​ второго столбца.​Это можно реализовать, буквально,​ If k =​
​ If End If​ — воспользуйтесь ей​
​ доработкой...​
​ только до 20​
​ Только нужно их​В нашем прайсе все​Lunatic​
​Dim i As​ на другой лист".​В ячейке С1​
​ этого Вашими предложенными​
​ макросу нужно работать​
​'переменную вместо "ActiveCell.Column",​
​ то ничего не​
​tblA() = Cells(1,​
​ в несколько движений.​

​ 1000 Then d.EntireRow.Delete​​ Next i If​ скорее, пока модераторы​Вводите сколько строк​ строки работать будет.​ соответственно выделять по​ еще не достает​: ух ты !!!​ Long​Вставляйте и удаляйте строки,​ пишем такую формулу.​ макросами?​ с большим количеством​
​’т.к. можно что-нибудь​ делается​ ActiveCell.Column + 1).Resize(myLastRow,​Выделяем диапазон ячеек в​ k = 0​
​ Not gr Is​ не видят;​ надо вставить и​
​genyaa​ номерам строк.​
​ двух столбцов: количество​ работает! спасибо, Serge​For i =​
​ столбцы и ячейки​ =ЕСЛИ(ОСТАТ(СТРОКА();2);"";ИНДЕКС(A:A;СТРОКА()/2))​lexkaz​
​ данных.​ забыть сделать,​st_1=A1// присвоить значение​ 1).Value​
​ первом столбце, который​ p = True​ Nothing Then gr.EntireRow.Delete​
​3) EntireRow.Delete не​ ОК!!!​: попробуйте так:​Чтобы удалить строку или​
​ и единицы измерения​ 007, а у​
​ 2 To 200​ для оптимального размещения​Копируем формулу вниз​: Люди добрые помогите​
​Какой вариант должен​
​'если код будет​ ячейки колонки А1​For i =​ надо заполнить (в​ Exit For End​
​ End Submtts54, я​
​ пробовали?​Guest​Sub AddForEach2Rows()​ столбец в Excel​
​ (шт. кг. л.​ нас с позволения​
​ Step 2​ данных на листе.​ по столбцу. Получится​ плиз​ делать макрос?​
​ меняться и данные​
​ текущей строки​ UBound(tblA, 1) To​ нашем примере, это​ If Next ch​
​ ничего не нашёл…​Sanja​: Добрый день!​
​Dim a As​ используйте горячие клавиши​ упак.). Чтобы одновременно​ сказать IT отдел​
​Cells(i, 1).EntireRow.Insert​

​Примечание:​​ так.​мне нужно тоже​SergeyKorotun​ будут браться не​
​st_2=A2 // //​
​ 2 Step -1​ A1:A12).​
​ Loop If Not​ (см. скрин)​
​: И при удалении​приведенный здесь макрос​
​ Range​
​ CTRL+«минус» предварительно выделив​ добавить два столбца,​
​ так ничего и​Next i​ В Microsoft Excel установлены​
​Какими способами быстро скопировать​ самое только вместо​
​: с форматированием​ те.​ присвоить значение ячейки​
​'Если текущая или​Нажимаем клавишу​ (d Is Nothing)​Irregular Expression​
​ цикл нужно организовывать​ я сделала, чтобы​
​Set a =​ их.​ выделите диапазон из​
​ не ответил )))​
​End Sub​
​ следующие ограничения на​
​ формулу по столбцу,​
​ линии надо вставить​
​Скрипт​myActiveColumn = ActiveCell.Column​ колонки А2 текущей​ вышестоящая ячейка пустые.​
​F5​ Then d.EntireRow.Delete End​
​: anddrei55, вот как-то​ с последней по​ он работал не​
​ ActiveSheet.UsedRange​
​Примечание. Вставка новых столбцов​
​ двух ячеек C1:D1.​
​Lunatic​
​Guest​

​ количество строк и​​ смотрите в статье​ последнее значение​:​myLastRow = Cells(Rows.Count,​

​ строки​​If IsEmpty(tblA(i, 1))​и затем кнопку​ If Application.Calculation =​ так, проверка по​ начальную строку с​ от последней строки​For i =​ и строк на​ Далее используйте тот​

​: а вот Сашино​​: Я извеняюсь за​​ столбцов: 16 384 столбца​
​ «Копирование в Excel»​я знаю что​
​200?'200px':''+(this.scrollHeight+5)+'px');">Sub Procedure_1()​ myActiveColumn).End(xlUp).Row​
​end do​ = True Or​
​Выделить (Special)​ xlCalculationAutomatic Application.ScreenUpdating =​
​ пустой первой ячейке,​
​ шагом -1​ а наоборот от​ a.Rows.Count To 1​
​ самом деле является​ же инструмент на​
​ решение у меня​ неграмотность. Но можно​ в ширину и​
​ тут.​
​ тут просто вместо​Dim tblMy() As​
​'Берём в VBA-таблицу​Скрипт​ IsEmpty(tblA(i - 1,​и в появившемся​
​ True End Sub​
​ если надо по​anddrei55​
​ первой к последней.​ Step -1​ заменой. Ведь количество​
​ главной закладке «Вставить»-«Вставить​ почему-то не работает.​
​ по подробней.​ 1 048 576 строк в​Вставить пустые строки в​
​ линии написать оператор​ Variant​ "tblMy" данные из​: SergeyKorotun, да, вы​
​ 1)) = True​ окне выбираем​
​Но при таком​ пустой целой строке​: Как в таком​
​Но макрос недорабатывает.​a.Rows(i + 1).EntireRow.Insert​
​ строк 1 048​
​ столбцы на лист».​ всё так же​
​Guest​
​ высоту.​
​Excel через две, три​
​ с последним значением​
​Dim myLastRow As​
​ двух столбцов.​ правы, есть другой​
​ Then​Выделить​ случае нужно выделять​
​ - скажите:​ случае прописать проверку​ Из-за вставки строк​a.Rows(i + 1).EntireRow.Insert​
​ 576 и колонок​Или выделите два заголовка​
​ и выстаивается​: Да просто вставьте​Чтобы вставить столбец, выделите​
​, т.д.​
​ но не знаю​
​ Long​
​tblMy() = Cells(1,​
​ способ решения вашей​

​’Переходим к следующей​​пустые ячейки​ столбец, что не​’Добавляем Sub InsertRows()​ заполненных строк и​ общий диапазон ведь​Next​ 16 384 не​ столбца C и​Lunatic​ этот код в​ его, а затем​Используем формулу как​ как …​Dim myActiveColumn As​ myActiveColumn).Resize(myLastRow, 2).Value​ задачи:​ ячейке.​(Blanks)​ очень удобно. Можно​ Dim i As​ чтоб после заполненых​ увеличивается, но макрос​End Sub​

​ меняется. Просто последние,​​ D, щелкните правой​: в вашем -​ пустой модуль и​
​ на вкладке​ во втором варианте,​БОЛЬШОЕ СПАСИБО !!!​ Long​

• ​For i =​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Procedure_1()​GoTo metka​:​ забыть выделить столбец​
• ​ Long: i =​

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

​Главная​​ только вместо цифры​

​Хоть вопрос и​​Dim i As​​ UBound(tblMy, 1) To​
​Dim tblMy() As​End If​
​Не снимая выделения вводим​ и тогда код​
​ 1 Do While​И в обратном​
​ сначала диапазоне, не​: или так:​
​ факт следует учитывать​
​ выберите опцию «Вставить».​Lunatic​ выполнение.​
​нажмите кнопку​
​ «2» напишем цифру​
​ похожий, но все-таки​ Long​
​ 2 Step -1​ Variant​'Если данные в​
​ в первую ячейку​ сработает не так​
​ i < 200​ направлении, проверку и​ доходит до конца.​
​Sub insrows()​ при заполнении листа​
​Примечание. Столбцы всегда добавляются​: ааа! тоже классная​
​Pavel55​Вставить​ «3». Формула получится​
​ немного другой. А​Application.ScreenUpdating = False​'Если данные в​Dim myLastRow As​
​ текущей ячейке и​ знак равно и​
​ как надо.​ '200 - количество​ удаление пустых строк?​
​ Что делать?​Dim i As​
​ данными более чем​
​ в левую сторону.​ формула. спасибо. всё​
​: Посмотрите приложенный файл​
​и выберите пункт​
​ такая. =ЕСЛИ(ОСТАТ(СТРОКА();3);"";ИНДЕКС(A:A;СТРОКА()/3))​
​ посему отсылаю Вас​
​'Берём номер столбца,​
​ первом столбце в​ Long​
​ вышестоящей ячейке разные.​ щелкаем по предыдущей​
​Irregular Expression​ проверяемых строк If​
​Jack Famous​Sub InsertRows()​
​ Long, s As​ на 50%-80%.​
​ Количество новых колонок​ сохранила .​ (слева)​Вставить столбцы на лист​
​Получится такая таблица.​ к пункту 5q​
​ где находится активная​ текущей строке и​Dim i As​
​If tblA(i, 1)​
​ ячейке (т.е. создаем​
​: anddrei55, ест-но, т.к.​
​ Not IsEmpty(Cells(i, 1))​
​: anddrei55, для начала​

​Dim i, i1​​ String​

​Sortafreel​​ появляется столько, сколько​
​AlexM​Guest​.​Третий вариант.​ Правил форума, а​ ячейка,​ строке,​ Long​ <> tblA(i -​ ссылку на предыдущую​

​ каждая строка удаляется/вставляется​​ Then Cells(i +​ файл-пример прикрепите с​
​ As Long, nRow​For i =​: День добрый.​ было их предварительно​
​: Еще можно так​: Спасибо за помощь!!!!!​Чтобы удалить столбец, выделите​В​ тему закрываю.​’в переменную.​
​’которая выше, не​
​Application.ScreenUpdating = False​ 1, 1) Then​ ячейку, другими словами):​ отдельно с двумя-тремя​ 1, 1).EntireRow.Insert i​ листами «до» и​ As Long, kv,​

excelworld.ru

​ 1 To 9​

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

Следующие функции находят и возвращают части текстовых строк или составляют большие строки из небольших: НАЙТИ (FIND), ПОИСК (SEARCH), ПРАВСИМВ (RIGHT), ЛЕВСИМВ (LEFT), ПСТР (MID), ПОДСТАВИТЬ (SUBSTITUTE), ПОВТОР (REPT), ЗАМЕНИТЬ (REPLACE), СЦЕПИТЬ (CONCATENATE).

Функции НАЙТИ и ПОИСК

Функции НАЙТИ (FIND) и ПОИСК (SEARCH) используются для определения позиции одной текстовой строки в другой. Обе функции возвращают номер символа, с которого начинается первое вхождение искомой строки. Эти две функции работают одинаково за исключением того, что функция НАЙТИ учитывает регистр букв, а функция ПОИСК допускает использование символов шаблона. Функции имеют следующий синтаксис:

=НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция)
=ПОИСК(искомый_текст;просматриваемый_текст;нач_позиция)

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

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

Например, чтобы определить позицию буквы «ж» в строке «Гаражные ворота», надо использовать формулу:

Эта формула возвращает 5.

Если точная последовательность символов искомого текста неизвестна, можно использовать функцию ПОИСК и включить в строку искомый_текст символы шаблона: знак вопроса (?) и звездочку (*). Знак вопроса соответствует одному произвольно набранному символу, а звездочка заменяет любую последовательность символов в указанной позиции. Например, чтобы найти позицию имен Анатолий, Алексей, Акакий в тексте, находящемся в ячейке А1, надо использовать формулу:

Функции ПРАВСИМВ и ЛЕВСИМВ

Функция ПРАВСИМВ (RIGHT) возвращает крайние правые символы строки аргумента, в то время как функция ЛЕВСИМВ (LEFT) возвращает первые (левые) символы. Синтаксис:

=ПРАВСИМВ(текст;количество_символов)
=ЛЕВСИМВ(текст;количество_символов)

Аргумент количество_символов задает число символов, извлекаемых из аргумента текст. Эти функции учитывают пробелы и поэтому, если аргумент текст содержит пробелы в начале или конце строки, в аргументах функций следует использовать функцию СЖПРОБЕЛЫ.

Аргумент количестов_символов должен быть больше или равен нулю. Если этот аргумент опускается, Excel считает его равным 1. Если количество_символов больше числа символов в аргументе текст, то возвращается весь аргумент.

Функция ПСТР

Функция ПСТР (MID) возвращает заданное число символов из строки текста, начиная с указанной позиции. Эта функция имеет следующий синтаксис:

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

Аргумент текст — это текстовая строка, содержащая извлекаемые символы, нач_позиция — это позиция первого символа, извлекаемого из текста (относительно начала строки), а количество_символов — это число извлекаемых символов.

Функции ЗАМЕНИТЬ и ПОДСТАВИТЬ

Эти две функции заменяют символы в тексте. Функция ЗАМЕНИТЬ (REPLACE) замещает часть текстовой строки другой текстовой строкой и имеет синтаксис:

=ЗАМЕНИТЬ(старый_текст;нач_позиция;количество_символов;новый_текст)

Аргумент старый_текст — это текстовая строка, а которой надо заменить символы. Следующие два аргумента задают символы, которые нужно заменить (относительно начала строки). Аргумент новый_текст задает вставляемую текстовую строку.

Например, ячейка А2 содержит текст «Вася Иванов». Чтобы поместить этот же текст в ячейку А3, заменив имя, надо в ячейку А3 вставить следующую функцию:

В функции ПОДСТАВИТЬ (SUBSTITUTE) начальная позиция и число заменяемых символов не задаются, а явно указывается замещаемый текст. Функция ПОДСТАВИТЬ имеет следующий синтаксис:

=ПОДСТАВИТЬ(текст;старый_текст;новый_текст;номер_вхождения)

Аргумент номер_вхождения является необязательным. Он предписывает Excel заменить только заданное вхождение строки старый_текст.

Например, ячейка А1 содержит текст «Ноль меньше восьми». Надо заменить слово «ноль» на «нуль».

Число 1 в этой формуле указывает, что надо изменить только первое «о» в строке ячейки А1. Если аргумент номер_вхождения опущен, Excel заменяет все вхождения строки старый_текст на строку новый_текст.

Функция ПОВТОР

Функция ПОВТОР (REPT) позволяет заполнить ячейку строкой символов, повторенной заданное количество раз. Синтаксис:

=ПОВТОР(текст;число_повторений)

Аргумент текст — это размноженная строка символов, заключенная в кавычки. Аргумент число_повторений указывает, сколько раз нужно повторить текст. Если аргумент число_повторений равен 0, функция ПОВТОР оставляет ячейку пустой, а если он не является целым числом, эта функция отбрасывает десятичные знаки после запятой.

Функция СЦЕПИТЬ

Функция СЦЕПИТЬ (CONCATENATE) является эквивалентом текстового оператора & и используется для объединения строк. Синтаксис:

=СЦЕПИТЬ(текст1;текст2;. )

В функции можно использовать до 30 аргументов.

Например, ячейка А5 содержит текст «первое полугодие», следующая формула возвращает текст «Всего за первое полугодие»:

=СЦЕПИТЬ(«Всего за «;А5)

В начало страницы

В начало страницы

Текстовые функции Excel

ФИО, номера банковских карт, адреса клиентов или сотрудников, комментарии и многое другое –все это является строками, с которыми многие сталкиваются, работая с приложением Excel. Поэтому полезно уметь обрабатывать информацию подобного типа. В данной статье будут рассмотрены текстовые функции в Excel, но не все, а те, которые, по мнению office-menu.ru, самые полезные и интересные:

Список всех текстовых функций Вы можете найти на вкладке «Формулы» => выпадающий список «Текстовые»:

Функция ЛЕВСИМВ

Возвращает подстроку из текста в порядке слева направо в заданном количестве символов.

Синтаксис: =ЛЕВСИМВ(текст; [количество_знаков])

• текст – строка либо ссылка на ячейку, содержащую текст, из которого необходимо вернуть подстроку;
• количество_знаков – необязательный аргумент. Целое число, указывающее, какое количество символов необходимо вернуть из текста. По умолчанию принимает значение 1.

Пример использования:

Формула: =ЛЕВСИМВ(«Произвольный текст»;8) – возвращенное значение «Произвол».

Функция ПРАВСИМВ

Данная функция аналогична функции «ЛЕВСИМВ», за исключением того, что знаки возвращаются с конца строки.

Пример использования:

Формула: =ПРАВСИМВ(«произвольный текст»;5) – возвращенное значение «текст».

Функция ДЛСТР

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

Синтаксис: =ДЛСТР(текст)

Пример использования:

Функция НАЙТИ

Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».

Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; [нач_позиция])

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

Пример использования:

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

Функция ЗАМЕНИТЬ

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

Синтаксис: ЗАМЕНИТЬ(старый_текст; начальная_позиция; количество_знаков; новый_текст)

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

Пример использования:

Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

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

• Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
• В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(«старый»;A1);ДЛСТР(«старый»);»новый»)

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

Функция ПОДСТАВИТЬ

Данная функция заменяет в тексте вхождения указанной подстроки на новый текст, чем схожа с функцией «ЗАМЕНИТЬ», но между ними имеется принципиальное отличие. Если функция «ЗАМЕНИТЬ» меняет текст, указанный посимвольно вручную, то функция «ПОДСТАВИТЬ» автоматически находит вхождения указанной строки и меняет их.

Синтаксис: ПОДСТАВИТЬ(текст; старый_текст; новый_текст; [номер_вхождения])

• текст – строка или ссылка на ячейку, содержащую текст;
• старый_текст – подстрока из первого аргумента, которую необходимо заменить;
• новый_текст – строка для подмены старого текста;
• номер_вхождения – необязательный аргумент. Принимает целое число, указывающее порядковый номер вхождения старый_текст, которое подлежит замене, все остальные вхождения затронуты не будут. Если оставить аргумент пустым, то будут заменены все вхождения.

Пример использования:

Строка в ячейке A1 содержит текст, в котором имеются 2 подстроки «старый». Нам необходимо подставить на место первого вхождения строку «новый». В результате часть текста «…старый-старый…», заменяется на «…новый-старый…».

Если ли бы последний аргумент был опущен, то результатом бы стала строка «строка, содержащая новый-новый текст».

Функция ПСТР

ПСТР возвращает из указанной строки часть текста в заданном количестве символов, начиная с указанного символа.

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

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

Пример использования:

Из текста, находящегося в ячейке A1 необходимо вернуть последние 2 слова, которые имеют общую длину 12 символов. Первый символ возвращаемой фразы имеет порядковый номер 12.

Аргумент количество_знаков может превышать допустимо возможную длину возвращаемых символов. Т.е. если в рассмотренном примере вместо количество_знаков = 12, было бы указано значение 15, то результат не изменился, и функция так же вернула строку «функции ПСТР».

Для удобства использования данной функции ее аргументы можно подменить функциями «НАЙТИ» и «ДЛСТР», как это было сделано в примере с функцией «ЗАМЕНИТЬ».

Функция СЖПРОБЕЛЫ

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

Синтаксис: =СЖПРОБЕЛЫ(текст)

Пример использования:

=СЖПРОБЕЛЫ( » Текст с лишними пробелами между словами и по краям « )

Результатом выполнения функции будет строка: «Текст с лишними пробелами между словами и по краям» .

Функция СЦЕПИТЬ

С помощью функции «СЦЕПИТЬ» можно объединить несколько строк между собой. Максимальное количество строк для объединения – 255.

Синтаксис: =СЦЕПИТЬ(текст1; [текст2]; …)

Функция должна содержать не менее одного аргумента

Пример использования:

Функция возвратит строку: «Слово1 Слово2».

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

Вместо использования данной функции можно применять знак амперсанда «&». Он так же объединяет строки. Например: «=»Слово1″&» «&«Слово2″».

Канал в Telegram

Работа со строками в Excel. Текстовые функции Excel

Часто в Excel приходится тем или иным образом обрабатывать текстовые строки. Вручную такие операции проделывать очень сложно когда кол-во строк составляет не одну сотню. Для удобства в Excel реализован не плохой набор функций для работы со строковым набором данных. В этой статье я коротко опишу необходимые функции для работы со строками категории «Текстовые» и некоторые рассмотрим на примерах.

Функции категории «Текстовые»

Итак, рассмотрим основные и полезные функции категории «Текстовые», с остальными можно ознакомиться самостоятельно.

• БАТТЕКСТ (Значение) – функция преобразующая число в текстовый тип;
• ДЛСТР (Значение) – вспомогательная функция, очень полезна при работе со строками. Возвращает длину строки, т.е. кол-во символов содержащихся в строке;
• ЗАМЕНИТЬ (Старый текст, Начальная позиция, число знаков, новый текст) – заменяет указанное кол-во знаков с определенной позиции в старом тексте на новый;
• ЗНАЧЕН (Текст) – преобразует текст в число;
• ЛЕВСИМВ (Строка, Кол-во знаков) – очень полезная функция, возвращает указанное кол-во символов, начиная с первого символа;
• ПРАВСИМВ (Строка, Кол-во знаков) – аналог функции ЛЕВСИМВ, с той лишь разницей, что возврат символов с последнего символа строки;
• НАЙТИ (текст для поиска, текст в котором ищем, начальная позиция) – функция возвращает позицию, с которой начинается вхождение искомого текста. Регистр символов учитывается. Если необходимо не различать регистр символов, воспользуйтесь функцией ПОИСК. Возвращается позиция только первого вхождения в строке!
• ПОДСТАВИТЬ (текст, старый текст, новый текст, позиция) – интересная функция, на первый взгляд похожа на функцию ЗАМЕНИТЬ, но функция ПОДСТАВИТЬ способна заменить на новую подстроку все вхождения в строке, если опущен аргумент «позиция»;
• ПСТР (Текст, Начальная позиция, Кол-во знаков) – функция похожа на ЛЕВСИМВ, но способна возвратить символы с указанной позиции:
• СЦЕПИТЬ (Текст1, Текст 2 …. Текст 30) – функция позволяет соединить до 30-ти строк. Так же, можно воспользоваться символом «&», выглядеть будет так «=”Текст1” & ”Текст2” & ”Текст3”»;

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

Пример 1
Дан набор строк:

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

Извлечем в столбец В номера накладных. Для этого найдем так называемый ключевой символ или слово. В нашем примере видно, что перед каждым номером накладной стоит «№», а длина номера накладной 6 символов. Воспользуемся функциями НАЙТИ и ПСТР. Пишем в ячейку B2 следующую формулу :

= ПСТР (A2; НАЙТИ («№»;A2)+1;6)

Разберем формулу. Из строки А2 с позиции следующей после найденного знака «№», мы извлекаем 6 символов номера.

Теперь извлечем дату. Тут все просто. Дата расположена в конце строки и занимает 8 символов. Формула для С2 следующая:

= ПРАВСИМВ (A2;8)

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

= ЗНАЧЕН ( ПРАВСИМВ (A2;8))

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

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

= ЗНАЧЕН ( СЦЕПИТЬ («01»; ПРАВСИМВ (A2;6))) или = ЗНАЧЕН («01″& ПРАВСИМВ (A2;6))

Задайте формат ячеке «ММММ ГГГГ». Результат:

Пример 2
В строке «Пример работы со строками в Excel» необходимо все пробелы заменить на знак «_», так же перед словом «Excel» добавить «MS».

Формула будет следующая:

=ПОДСТАВИТЬ(ЗАМЕНИТЬ(A1;ПОИСК(«excel»;A1);0;»MS «);» «;»_»)

Для того, чтоб понять данную формулу, разбейте ее на три столбца. Начните с ПОИСК, последней будет ПОДСТАВИТЬ.

Все. Если есть вопросы, задавайте, не стесняйтесь

Текстовые функции Excel в примерах

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

Для объединения содержимого ячеек в Excel, наряду с оператором конкатенации, можно использовать текстовую функцию СЦЕПИТЬ. Она последовательно объединяет значения указанных ячеек в одной строке.

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

Текстовая функция ПРОПИСН делает все буквы прописными, т.е. преобразует их в верхний регистр. Так же, как и СТРОЧН, не заменяет знаки, не являющиеся буквами.

Текстовая функция ПРОПНАЧ делает прописной первую букву каждого слова, а все остальные преобразует в строчные.

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

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

ЛЕВСИМВ и ПРАВСИМВ

Текстовые функции ЛЕВСИМВ и ПРАВСИМВ возвращают заданное количество символов, начиная с начала или с конца строки. Пробел считается за символ.

Текстовая функция ПСТР возвращает заданное количество символов, начиная с указанной позиции. Пробел считается за символ.

Функция СОВПАД позволяет сравнить две текстовые строки в Excel. Если они в точности совпадают, то возвращается значение ИСТИНА, в противном случае – ЛОЖЬ. Данная текстовая функция учитывает регистр, но игнорирует различие в форматировании.

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

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

В случаях, когда наличие лишнего пробела в конце или начале строки сложно отследить, данная функция становится просто незаменимой. На рисунке ниже видно, что содержимое ячеек А1 и B1 абсолютно одинаково, но это не так. В ячейке А1 мы намеренно поставили лишний пробел в конце слова Excel. В итоге функция СОВПАД возвратила нам значение ЛОЖЬ.

Применив функцию СЖПРОБЕЛЫ к значению ячейки А1, мы удалим из него все лишние пробелы и получим корректный результат:

Функцию СЖПРОБЕЛЫ полезно применять к данным, которые импортируются в рабочие листы Excel из внешних источников. Такие данные очень часто содержат лишние пробелы и различные непечатаемые символы. Чтобы удалить все непечатаемые символы из текста, необходимо воспользоваться функцией ПЕЧСИМВ.

Функция ПОВТОР повторяет текстовую строку указанное количество раз. Строка задается как первый аргумент функции, а количество повторов как второй.

Текстовая функция НАЙТИ находит вхождение одной строки в другую и возвращает положение первого символа искомой фразы относительно начала текста.

Данная функция чувствительна к регистру…

… и может начинать просмотр текста с указанной позиции. На рисунке ниже формула начинает просмотр с четвертого символа, т.е. c буквы “r“. Но даже в этом случае положение символа считается относительно начала просматриваемого текста.

Текстовая функция ПОИСК очень похожа на функцию НАЙТИ, основное их различие заключается в том, что ПОИСК не чувствительна к регистру.

ПОДСТАВИТЬ

Заменяет определенный текст или символ на требуемое значение. В Excel текстовую функцию ПОДСТАВИТЬ применяют, когда заранее известно какой текст необходимо заменить, а не его местоположение.

Приведенная ниже формула заменяет все вхождения слова “Excel” на “Word”:

Заменяет только первое вхождение слова “Excel”:

Удаляет все пробелы из текстовой строки:

Заменяет символы, расположенные в заранее известном месте строки, на требуемое значение. В Excel текстовую функцию ЗАМЕНИТЬ применяют, когда известно где располагается текст, при этом сам он не важен.

Формула в примере ниже заменяет 4 символа, расположенные, начиная с седьмой позиции, на значение “2013”. Применительно к нашему примеру, формула заменит “2010” на “2013”.

Заменяет первые пять символов текстовой строки, т.е. слово “Excel”, на “Word”.

Вот и все! Мы познакомились с 15-ю текстовыми функциями Microsoft Excel и посмотрели их действие на простых примерах. Надеюсь, что данный урок пришелся Вам как раз кстати, и Вы получили от него хотя бы малость полезной информации. Всего доброго и успехов в изучении Excel!

Функции 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

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

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

Как в Excel сделать разделение строки на подстроки

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

Также возможно использование макросов для достижения этой цели. Для этого в VBA существует специальная функция – Split. Она разделяет строку по разделителям, в качестве которого может выступать как определенный символ, так и сразу несколько. Синтаксис функции включает три аргумента, из которых обязательным является только один.

1. Expression. Это строка, которую нужно разбить на подстроки.
2. Delimiter. Разделитель. Этот аргумент необязательный. Если в нем не указывать никаких значений, то по умолчанию будет в качестве разделителя приниматься пробел.
3. Limit. То количество подстрок, на которое входная должна быть разделена. Этот аргумент также не обязательно указывать. В этом случае в качестве значения по умолчанию будет установлено -1.
4. Compare. С помощью этого аргумента функции передается тип сравнения – двоичный или текстовый. Простыми словами, в первом случае (если тип сравнения установлен на 0), функция учитывает регистр букв при сравнении. В случае же текстового сравнения регистр букв не учитывается.

Значение, которое эта функция вернет – массив, в котором перечислены подстроки, число которых задается параметром limit. Как могло заинтересовать наблюдательного читателя, если поставить значение -1, то функцией будут возвращены все подстроки. И теперь давайте приведем несколько примеров, как работает эта VBA функция.

Sub Test1()

Dim a() As String

a = Split(«vremya ne zhdet»)

MsgBox a(0) & vbNewLine & a(1) & vbNewLine & a(2)

End Sub

Эта функция показывает оповещение, которое выдает три подстроки «vremya ne zhdet». В этом случае стоят настройки по умолчанию. Если записать такой код, то оповещение покажет строку «vremya ne-zhdet» из исходной строки той же самой, только используется вместо пробела дефис.

Sub Test2()

Dim a() As String

a = Split(«vremya-ne-zhdet»,»-«, 2)

MsgBox a(0) & vbNewLine & a(1)

End Sub

Здесь используются значения аргумента Delimiter в –, а Limit – 2. Таким образом, всего возможно была разбивка на три части, но поскольку мы указали только две, то видим, итоговый результат тоже являет собой одну подстроку «vremya» и одну подстроку «ne-zhdet». Видим, что все на самом деле невероятно просто.

Текстовые функции в Эксель

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

1. БАТТЕКСТ(Значение). Функция, необходимая для превращения ячейки числового формата в текстовый. Ее полезно использовать, если формула требует текстового значения, в то время как в ячейке число представлено в виде цифрового. С помощью данной функции можно конвертировать данные из одного типа в другой.
2. ДЛСТР(Значение). Эта функция позволяет определить длину строки и то, сколько символов находится в ней. Возвращает число, соответствующее количеству знаков, которые записаны в этой строке.
3. ЗАМЕНИТЬ(Старый текст, Начальная позиция, число знаков, новый текст). С помощью этой функции можно заменить один текст на другой, в качестве ориентира используя определенное количество знаков, начиная с позиции, которая указана пользователем.
4. ЗНАЧЕН(Текст). Эта функция совершает противоположную первому оператору операцию – значение текстового формата превращает в числовой.
5. ЛЕВСИМВ(Строка, Количество знаков). С помощью этой функции можно получить заданное пользователем количество символов строки, указанной человеком. При этом в учет берутся те знаки, которые располагаются слева.
6. ПРАВСИМВ(Строка, Количество знаков). Принцип работы этой функции аналогичный, только с ее помощью можно вернуть определенное количество знаков справа. То есть, узнать, какой будет часть строки, начиная с самого последнего символа.
7. НАЙТИ(текст для поиска, текст, в котором ищем, начальная позиция). С помощью этой функции можно получить позицию, на которой находится текст, заданный пользователем. Этот оператор можно использовать, только если регистр для нас важен. Если же нет разницы, какие буквы использовать: большие или маленькие, то есть аналогичная функция – ПОИСК. Также следует отметить, что эта функция будет возвращать исключительно первое вхождение, все последующие уже не берутся в учет. Для этого существуют другие функции.
8. ПОДСТАВИТЬ (текст, старый текст, новый текст, позиция). Это очень интересная функция. В чем-то она схожа на оператор ЗАМЕНИТЬ, но имеет более широкий функционал. Если пользователь не указал последний аргумент, то замена осуществляется всех вхождений в тексте. Таким образом, это позволяет автоматизировать опции Excel «Заменить все».
9. ПОДСТРОКА(Текст, разделитель, номер). С помощью этой функции можно получить строку, которая была разделена с помощью разделителя.
10. ПСТР (Текст, Начальная позиция, Количество знаков). Это одна из самых главных функций, которую мы сегодня будем разбирать очень подробно. Она в чем-то имеет схожий принцип на ЛЕВСИМВ, только дает возможность начать поиск подстроки не с самого начала, а с определенной позиции.
11. СЦЕПИТЬ (Текст1, Текст2…). Это функция, позволяющая объединить несколько строк. Является некой заменой оператору &. Максимальное количество строк, которые можно соединить между собой – 30.

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

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

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

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

А в этом примере мы попробуем разбить номер телефона на несколько частей.

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

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

Синтаксис функции ПСТР в Excel

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

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

1. Текст. Текстовая строка, из которой мы будем получать «обрезанный» вариант. Кроме результата работы функции и ссылки на ячейку, в качестве параметра этого аргумента может также выступать и непосредственно текстовая константа. Но на практике ее использование лучше всего подходит для тренировки. В реальной жизни в этом нет необходимости, поскольку всегда можно вручную вставить нужный фрагмент текста в любую ячейку.
2. Начальная позиция. Отсчет знаков для этого аргумента начинается с самого первого символа строки слева. Эта функция отличается от некоторых других тем, что отсчет символов осуществляется с числа 1, а не нуля.
3. Число знаков. Здесь записывается итоговое количество знаков, которые нам надо отсчитать с начальной позиции. Минимальное значение – 1. Чисто гипотетически есть возможность указать в качестве значения этого аргумента 0, но в этом случае в качестве результата будет получена пустая строка.

В своем самом общем виде формула выглядит так: =ПСТР(текст; начальная_позиция; число_знаков)

Есть еще один вариант этой формулы: ПСТРБ, которая позволяет работать с мультибайтовыми строками. Но в нашем языке таких нет, поэтому достаточно просто знать о том, что такая формула есть. Возможных результата после работы этой формулы два:

1. Ошибка. Если аргументы функции были неправильно указаны, то появляется ошибка #ЗНАЧ!. Типичные причины появления этой ошибки – нулевая позиция начала или отрицательное значение в аргументе «Число знаков».
2. Строка. Если все параметры были указаны правильно, мы получаем итоговую текстовую строку.

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

1. Параметр «Начальная позиция» не может быть больше, чем общая величина строки. Иначе в качестве результата функции будет выдана строка с нулевой длиной (то есть, пустая).
2. Если, даже несмотря на то, что значение «начальная позиция» меньше итоговой длины строки, сумма значений «начальная позиция» и «число знаков» больше, чем общее количество знаков этого текста, то функцией возвращаются оставшиеся знаки, начиная с той позиции, которая указана. Таким образом, можно указать в качестве числа знаков заведомо большое число, чтобы функция вернула те символы, которые расположены справа до самого конца строки.
3. Ошибка #ЗНАЧ! возникает в следующих ситуациях: если начальная позиция меньше единицы, число знаков или число байтов (для функции ПСТРБ) отрицательное.

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

Подстрока из строки в Эксель при помощи функции ПСТР

Давайте рассмотрим небольшой пример того, как можно выделить отдельные знаки из строки с помощью функции ПСТР.  Приведем очень простую ситуацию. Предположим у нас в ячейке B14 записана простая строка, состоящая из последовательности чисел от 1 до 0. Предположим, нам надо из строки 1234567890 получить тройку. В таком случае формула должна быть такой: =ПСТР(B14;3;1).

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

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

Функция ПСТР (MID) – подробное описание

Итак, мы поняли, что ПСТР – это функция. которая используется для того, чтобы достать из одного фрагмента текста какой-то определенный. Но она несколько отличается от функции «Найти и заменить», которая может реализовываться как через меню Excel, так и посредством формул. Она возвращает строку, начинающуюся с определенного символа.

Синтаксис предельно прост:

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

Давайте более подробно поймем, какие аргументы используются в этой функции:

1. Текст. Содержимым ячейки могут выступать значения в разных форматах как ссылки, так и текстовая строка. Впрочем, может использоваться и ячейка любого другого формата. Например, можно таким способом достать месяц из даты. Для этого нужно в поле «Текст» указать ссылку на ячейку с датой. Важно убедиться, что она была предварительно конвертирована в текстовый формат.
2. Первоначальная позиция. Это числовое значение того символа, с какого начинается извлечение подстроки.
3. Число знаков. Это количество знаков, которые необходимо достать из строки. Если же нужно извлечь дату, то она всегда имеет размер в 10 символов.

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

Функция ПСТРБ (новая функция)

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

В чем разница между ПСТР и ПСТРБ?

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

Функция ПСТР на английском

Если человек пользуется англоязычной версией Excel, ему необходимо знать, как записывается эта функция на английском языке. За то, чтобы вытащить определенное количество знаков из строки, отвечает функция MID . Соответственно, если необходимо ориентироваться на количество байтов, то используется формула с MIDB.

Функция ПСТР и VBA в Excel

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

Range(“A2”).Value = Mid(Range(“A1”), 17, 10)

Распространенные ошибки в использовании функции ПСТР

При использовании функции ПСТР возможны ошибки, если неправильно соблюдать синтаксис или нарушить некоторые основополагающие правила:

1. Нельзя писать в качестве аргумента этой функции отрицательное значение. В таком случае будет выдана ошибка #ЗНАЧ.
2. Важно следить, чтобы начальная позиция не была большим числом, чем длина искомой строки. В ином случае будет выдано пустое значение.

Как вырезать часть текста ячейки в Эксель

А теперь давайте начнем обзор практических примеров, как возможно применение функции ПСТР .

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

Сама таблица выглядит следующим образом.

1

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

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

После этого получаем такой результат.

2

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

=ПСТР(A2:A8;НАЙТИ(” “;A2:A8)+1;100)

Здесь мы снова видим, что использовали функцию НАЙТИ , с помощью какой в этом примере ищем первоначальную позицию, содержащую пробел. Также мы добавили единицу к содержимому аргумента, чтобы перенести взор программы на первый символ марки товара. Чтобы упростить задачу поиска последнего символа мы просто решили написать число 100, которое гарантированно превышает длину строки.

Так можете делать и вы.

После того, как программа выполнит все необходимые расчеты, получаем следующую таблицу.

3

Как рассчитать возраст по дате рождения в Эксель

Теперь приведем еще один пример, с каким сотрудники, часто использующие Эксель, сталкиваются довольно часто. Перед нами есть база данных, содержащая три колонки: Фамилия, имя, отчество, а также дата рождения. И перед нами стоит задача определить, сколько лет человеку в данный момент, с использованием ПСТР.

Вот так выглядит таблица, с которой мы будем работать.

4

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

Эта функция сначала определяет ту часть строчки, которая содержится до символа пробела. Это делается с помощью функции НАЙТИ аналогично приведенным выше примерам. Далее с использованием функции РАЗНДАТ мы определяем количество полных лет. В нашем случае мы автоматически отсекаем тот участок дней, который остался до следующего дня рождения в этом году. Именно поэтому данная функция сможет указать всегда правильный возраст.

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

4

Несколько особенностей использования ПСТР в Excel

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

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

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

1. Текст. Это обязательный аргумент, который нужно передавать функции. Представляет собой или ссылку на ячейку, или непосредственно строку, из которой нужно извлекать требуемую информацию. Важно обратить внимание, что в последнем случае ее нужно облачать в кавычки. Независимо от формата строки, из нее будет доставаться определенная информация. Какая именно – задается следующими аргументами.
2. Начальная позиция. Этот аргумент также обязательный. Его задача – задать стартовую точку отсчета. Являет собой обязательно целое число, которое относится к положительным числам. То бишь, минимальное значение – 1. Если оказывается, что в аргумент была передана дробная часть, она отсекается.
3. Число знаков. И этот аргумент является обязательным. Таким образом, все аргументы, используемые в этой функции, необходимо использовать. Здесь есть такой нюанс. Если оказывается, что было в него передано число, которое больше длины строки, то возвращается вся строка.

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

Напоминаем, что синтаксис следующий:

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

Как говорится, повторение – мать учения. Поэтому давайте подведем небольшие промежуточные итоги:

1. Если на месте стартовой позиции задавать значение, которое по размеру больше исходной строки, то после всех операций, выполняемых функцией ПСТР вернется пустое значение.
2. Если применять единицу в качестве исходной позиции, а количество знаков указать такое, которое будет больше строки или равняться ей, то в качестве итога будет выведено все содержимое этой строки. Таким образом, можно использовать эту функцию в роли альтернативы, пусть и не такой удобной, ссылки на ячейку. В Excel ситуации бывают разные, поэтому иногда приходится выкручиваться из любой ситуации, в том числе, и такими причудливыми способами.
3. Будет возвращена ошибка #ЗНАЧ!, если использовать отрицательное значение в качестве начальной позиции. То же касается ситуации, если аргумент с числом знаков задается отрицательным значением. Важно запомнить навсегда, использовать нулевое или отрицательное значение в этом аргументе нельзя.

Примеры использования функции ПСТР

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

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

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

Сама таблица выглядит следующим образом:

5

В этом случае нужно применить формулу массива, а именно такую.

6

Какие аргументы использовались в этом случае?

1. А2:А10. Здесь перечисляется набор ячеек, представление дат в которых выполнено в текстовой форме. Из них и будет доставаться день.
2. 1 – это число, обозначающее первоначальную позицию, с которой будет осуществляться извлечение.
3. 2 – это последняя позиция.

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

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

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

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

В результате, у нас получается следующая таблица.

7

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

Таким образом, возможностей у функции ПСТР огромное количество. Ее можно использовать для обработки огромных массивов информации. А поскольку каждый год количество данных, которые нужно эффективно анализировать, постоянно увеличивается, необходимо искать качественные способы автоматизации. И хотя некоторые считают, что Эксель несколько устарел, в своей нише альтернатив этой программе нет. Хотя бы потому, что зная несколько формул, можно добиваться почти любого функционала электронных таблиц. Больших успехов вам и легкости в освоении этого ремесла.

Функция ПОИСК() в EXCEL

history 12 апреля 2013 г.

Группы статей
• Подстановочные знаки (*, ?)
• Функции и Средства EXCEL учитывающие подстановочные знаки (*, ?)

Синтаксис функции

ПОИСК ( искомый_текст ; просматриваемая_строка ;[нач_позиция])

Искомый_текст — текст, который требуется найти.

Просматриваемая_строка — текст, в которой ищется Искомый_текст .

Нач_позиция — позиция знака в просматриваемой_строке, с которой должен начинаться поиск. Если аргумент нач_позиция опущен, то предполагается значение 1.

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

Если искомый_текст не найден, возвращается значение ошибки #ЗНАЧ!

Функция ПОИСК() не учитывает РЕгиСТР букв. Для поиска с учетом регистра следует воспользоваться функцией НАЙТИ() .

Примеры

Формула =ПОИСК(«к»;»Первый канал») вернет 8, т.к. буква к находится на 8-й позиции слева.

Пусть в ячейке А2 введена строка Первый канал — лучший . Формула =ПОИСК(СИМВОЛ(32);A2) вернет 7, т.к. символ пробела (код 32) находится на 7-й позиции.

Формула =ПОИСК(«#. #»;»Артикул #123# ID») будет искать в строке » Артикул #123# ID » последовательность из 5 символов, которая начинается и заканчивается на знак #.

Чтобы найти позицию второго вхождения буквы «а» в строке «мама мыла раму» используйте формулу =ПОИСК(«а»;»мама мыла раму»;ПОИСК(«а»;»мама мыла раму»)+1). Чтобы определить есть ли третье вхождение буквы «м» в строке «мама мыла раму» используйте формулу =ЕСЛИ(ДЛСТР(ПОДСТАВИТЬ(«мама мыла раму»;»м»;»»;3))=ДЛСТР(«мама мыла раму»);»Нет третьего вхождения м»;»Есть третье вхождение м»)

Формула =ПОИСК(«клад?»;»докладная») вернет 3, т.е. в слове «докладная» содержится слово из 5 букв, первые 4 из которых клад (начиная с третьей буквы слова докладная ).

Функция НАЙТИ() vs ПОИСК()

Функция НАЙТИ() учитывает РЕгиСТР букв и не допускает использование подстановочных знаков. Для поиска без учета регистра, а также для поиска с использованием подстановочных знаков пользуйтесь функцией ПОИСК() .

Связь с функциями ЛЕВСИМВ() , ПРАВСИМВ() и ПСТР()

Функция ПОИСК() может быть использована совместно с функциями ЛЕВСИМВ() , ПРАВСИМВ() и ПСТР() .

Например, в ячейке А2 содержится фамилия и имя «Иванов Иван», то формула =ЛЕВСИМВ(A2;ПОИСК(СИМВОЛ(32);A2)-1) извлечет фамилию, а =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(СИМВОЛ(32);A2)) — имя. Если между именем и фамилией содержится более одного пробела, то для работоспособности вышеупомянутых формул используйте функцию СЖПРОБЕЛЫ() .

Логические операции в Excel

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

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

Операторы сравнения

Каждое выражение содержит операторы сравнения. Они бывают следующими:

1. = – значение 1 равно значению 2.
2. > – значение 1 больше значения 2.
3. >= значение 1 или идентично значению 2, или больше него.
4. <> значение 1 или больше значения 2 или меньше него.

Как следствие, Excel выдает один из двух возможных результатов: истина (1) или ложь (2).

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

Функция «Истина»

Для использования этой функции не нужно указывать никаких аргументов, и она всегда возвращает «Истина» (что соответствует цифре 1 двоичной системы счисления).

Пример формулы – =ИСТИНА() .

Функция «Ложь»

Функция, полностью аналогичная предыдущей, только возвращаемый ею результат – «Ложь». Самая легкая формула, где можно использовать эту функцию, следующая =ЛОЖЬ().

Функция «И»

Задачей этой формулы является возвращение значения «Истина» при соответствии каждого из аргументов определенному значению или определенному критерию, которые описаны выше. Если вдруг возникает несоответствие одного из критериев требуемому, то возвращается значение «Ложь».

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

Синтаксис этой функции такой:

= И(Логическое значение1; [Логическое значение 2];…)

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

Функция «Или»

Проверяет несколько значений на предмет соответствия определенному критерию. Если какой-то из них соответствует, то функция возвращает истинное значение (1). Максимальное количество аргументов в данной ситуации также равняется 255, и необходимо обязательно указывать один параметр функции.

Если говорить про функцию ИЛИ , то в случае с ней таблица истинности будет следующей.

Синтаксис формулы следующий:

=ИЛИ(Логическое значение 1; [Логическое значение 2];…)

Точно так же, как в предыдущем и последующем случае, каждый аргумент должен быть отделенным от другого с помощью точки с запятой. Если обратиться к приведенному выше примеру, то там каждый параметр возвращает «Истина», поэтому если необходимо при обращении к этому диапазону применять функцию «ИЛИ», то формула вернет «Истина» до тех пор, пока один из параметров не будет соответствовать определенному критерию.

Функция «Не»

Ею возвращаются те значения, которые являются противоположными по отношению к изначально заданному. То есть, при передаче в качестве параметра функции значения «Истина», вернется «Ложь». Если же соответствия не обнаружено, то «Истина».

То, какой результат будет выдан, зависит от того, какой исходный аргумент принимается функцией. Если, например, применять функцию «И» вместе с функцией «НЕ», то таблица будет следующей.

При использовании же функции «Или» в сочетании с функцией «Не», то таблица обретет такой вид.

Синтаксис этой функции очень прост: =НЕ(принимаемое логическое значение).

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

Если говорить конкретно про эту функцию, то ее синтаксис будет несколько сложнее.

=ЕСЛИ(Логическое_выражение;[Значение_если_истина];[Значение_если_ложь])

Давайте более детально рассмотрим пример, который был приведен на скриншоте выше. Здесь в качестве первого параметра выступает функция ИСТИНА , которая проверяется программой. По результатам такого чекинга возвращается второй аргумент. Третий же опускается.

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

Например, есть несколько кредитных карт, имеющих номера, начинающиеся первыми четырьмя цифрами, характеризующими платежную систему, обслуживающую карту. То есть, варианта два – Visa и Mastercard. Для проверки типа карты необходимо использовать такую формулу с применением двух вложенных ЕСЛИ .

=ЕСЛИ(ЛЕВСИМВ(A2;4)=”1111″; “Visa”;ЕСЛИ(ЛЕВСИМВ(A2;4)=”2222″;”Master Card”;”карта не определена”))

Если вы не знаете, что означает функция ЛЕВСИМВ , то она записывает в ячейку часть находящегося слева строки текста. Пользователь во втором аргументе этой функции задает количество символов, которые Excel должен выделить слева. Она используется для того, чтобы проверить, начинаются ли первые четыре знака номера кредитной карты с числа 1111. Если результат оказывается истинным, то возвращается «Visa». Если условие оказывается ложным, то используется функция ЕСЛИ .

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

Функция ЕСЛИОШИБКА

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

Эта формула имеет следующий синтаксис:

=ЕСЛИОШИБКА(значение;значение_если_ошибка)

Как можно использовать функцию?

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

Как могут использоваться логические функции на практике

Задача 1

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

Изначально нужно сформировать такую таблицу.

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

Логическое выражение, содержащееся в первом аргументе функции, составлено с использованием операторов > и =. Простыми словами, изначально критерий следующий: при значении ячейки, большем или равном 8, выполняется формула, поставленная во втором аргументе. Выражаясь терминологическим языком, если первое условие оказывается истинным, то выполняется второй аргумент. Если же ложным – третий.

Сложность этой задачи может быть увеличена. Предположим, что перед нами стоит задача использовать логическую функцию И. В этом случае условие обретет следующий вид: в случае хранения продукции в течение большего, чем 8 месяцев срока, то его цену необходимо сбросить в два раза. Если же он находится на реализации больше 5 месяцев, то необходимо сбрасывать в 1,5 раза.

В этом случае необходимо в поле ввода формулы ввести такую строку.

=ЕСЛИ(И(C2>=8);B2/2;ЕСЛИ(И(C2>=5);B2/1,5;B2))

Функция ЕСЛИ допускает использование текстовых строк в аргументах, если это требуется.

Задача 2

Предположим, после того, как был товар уценен, он стал стоить меньше 300 рублей, то его необходимо списать. Это же нужно сделать, если он пролежал, не продаваясь в течение 10 месяцев. В этой ситуации допустим любой из этих вариантов, поэтому логично использовать функцию ИЛИ и ЕСЛИ . В результате, получится следующая строка.

=ЕСЛИ(ИЛИ(D2 =10);”списан”;””)

Если при записи условия использовался логический оператор ИЛИ , то его необходимо расшифровывать следующим образом. Если в ячейке C2 находится число 10 или больше или если в ячейке D2 находится значение меньшее, чем 300, то необходимо выдать в соответствующей ячейке значение «списан».

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

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

Задача 3

Предположим, есть несколько учеников, которые перед поступлением в гимназию сдают несколько экзаменов. В качестве проходного балла стоит оценка в 12. А чтобы поступить, обязательно необходимо, чтобы стояло хотя бы 4 балла по математике. В результате, Excel должен составить отчет о поступлении.

Для начала необходимо построить следующую таблицу.

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

Нам необходимо ввести следующую формулу.

=ЕСЛИ(И(B3>=4;СУММ(B3:D3)>=$B$1);”принят”;”нет”)

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

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

Задача 4

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

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

Чтобы решить эту задачу, необходимо использовать такую формулу.

В приведенном в первом аргументе выражении мы использовали функцию СРЗНАЧ , определяющую среднее арифметическое определенного набора данных. В нашем случае это диапазон D2:D7.

Задача 5

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

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

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

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

Функция СРЗНАЧЕСЛИ позволяет сравнить значение ячейки B9 с теми значениями, которые располагаются в диапазоне B2:B7, где перечисляются номера магазинов. Если данные совпадают, то формула подсчитывает среднее арифметическое диапазона C2:C7.

Выводы

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

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

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

Пример функция ПСТР для разделения текста на части в Excel

Функция ПСТР в Excel предназначена для выделения подстроки из строки текста, переданной в качестве первого аргумента, и возвращает требуемое количество символов начиная с заданной позиции.

Примеры использования функции ПСТР в Excel

Один символ в языках с однобайтовой кодировкой соответствует 1 байту. При работе с такими языками результаты функций ПСТР и ПСТРБ (возвращает подстроку из строки на основе количества заданных байт) не отличаются. Если на компьютере используется двухбайтовый язык, каждый символ при использовании ПСТРБ будет считаться за два. Двухбайтовыми языками являются корейский, японский и китайский.

Как разделить текст на несколько ячеек по столбцам в Excel?

Пример 1. В столбце таблицы содержатся даты, записанные в виде текстовых строк. Записать отдельно в соседних столбцах номер дня, месяца и год, выделенные из представленных дат.

Вид исходной таблицы данных:

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

• A2:A10 – диапазон ячеек с текстовым представлением дат, из которых будут выделены номера дней;
• 1 – номер начальной позиции символа извлекаемой подстроки (первый символ в исходной строке);
• 2 – номер последней позиции символа извлекаемой подстроки.

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

Вид заполненной таблицы данных:

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

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

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

Вид таблицы данных:

Для заполнения столбца «Наименование» используем следующую формулу:

Функция НАЙТИ возвращает номер позиции символа пробела « » в просматриваемой строке, который принимается в качестве аргумента число_знаков функции ПСТР. В результате расчетов получим:

Для заполнения столбца «Марка» используем следующую формулу массива:

Функция НАЙТИ возвращает позицию символа пробела. К полученному числу прибавляется единица для нахождения позиции первого символа названия марки продукта. Итоговое значение используется в качестве аргумента начальная_позиция функции ПСТР. Для упрощения, вместо поиска номера последней позиции (например, с помощью функции ДЛСТР) указано число 100, которое в данном примере гарантированно превышает количество знаков в изначальной строке.

В результате расчетов получим:

Как посчитать возраст по дате рождения в Excel?

Пример 3. В таблице содержатся данные о сотрудниках в столбцах ФИО и дата рождения. Создать столбец, в котором будет отображаться фамилия сотрудника и его возраст в формате «Иванов – 27».

Вид исходной таблицы:

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

Функция ПСТР возвращает часть строки до символа пробела, позиция которого определяется функцией НАЙТИ. Для нахождения возраста сотрудника используется функция РАЗНДАТ, полученное значение которой усекается до ближайшего меньшего целого, чтобы получить число полных лет. Функция ТЕКСТ преобразует полученное значение в текстовую строку.

Для соединения (конкатенации) полученных строк используются символы «&». В результате вычислений получим:

Особенности использования функции ПСТР в Excel

Функция имеет следующую синтаксическую запись:

• текст – обязательный для заполнения аргумент, принимающий ссылку на ячейку с текстом или текстовую строку, заключенную в кавычки, из которой будет извлечена подстрока определенной длины начиная с указанной позиции первого символа;
• начальная_позиция – обязательный аргумент, принимающий целые числа из диапазона от 1 до N, где N – длина строки, из которой требуется извлечь подстроку заданного размера. Начальная позиция символа в строке соответствует числу 1. Если данный аргумент принимает дробное число из диапазона допустимых значений, дробная часть будет усечена;
• число_знаков – обязательный аргумент, принимающий значение из диапазона неотрицательных чисел, которое характеризует длину в символах возвращаемой подстроки. Если в качестве этого аргумента передано число 0 (нуль), функция ПСТР вернет пустую строку. Если аргумент задан числом, превышающим количество символов в строке, будет возвращена вся часть строки начиная с указанной вторым аргументом позиции. В дробных числах, используемых в качестве данного аргумента, дробная часть усекается.

Функция ПСТРБ имеет схожий синтаксис:

Она отличается единственным аргументом:

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

1. Функция ПСТР вернет пустую строку, если в качестве аргумента начальная_позиция было передано число, превышающее количество символов в исходной строке.
2. Если в качестве аргумента начальная_позиция было передано значение 1, а аргумент число_знаков определен числом, которое равно или больше общему числу знаков в исходной строке, функция ПСТР вернет всю строку целиком.
3. Если аргумент начальная_позиция был указан числом из диапазона отрицательных чисел или 0 (нулем), функция ПСТР вернет код ошибки #ЗНАЧ!.
4. Если аргумент число_знаков задан отрицательным числом, результатом выполнения функции ПСТР будет код ошибки #ЗНАЧ!.

Функция ПСТР для разделения текста на части в Excel – пример использования

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

Функция ПСТР (MID) – подробное описание

Итак, мы поняли, что ПСТР – это функция. которая используется для того, чтобы достать из одного фрагмента текста какой-то определенный. Но она несколько отличается от функции «Найти и заменить», которая может реализовываться как через меню Excel, так и посредством формул. Она возвращает строку, начинающуюся с определенного символа.

Синтаксис предельно прост:

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

Давайте более подробно поймем, какие аргументы используются в этой функции:

1. Текст. Содержимым ячейки могут выступать значения в разных форматах как ссылки, так и текстовая строка. Впрочем, может использоваться и ячейка любого другого формата. Например, можно таким способом достать месяц из даты. Для этого нужно в поле «Текст» указать ссылку на ячейку с датой. Важно убедиться, что она была предварительно конвертирована в текстовый формат.
2. Первоначальная позиция. Это числовое значение того символа, с какого начинается извлечение подстроки.
3. Число знаков. Это количество знаков, которые необходимо достать из строки. Если же нужно извлечь дату, то она всегда имеет размер в 10 символов.

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

Функция ПСТРБ (новая функция)

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

В чем разница между ПСТР и ПСТРБ?

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

Функция ПСТР на английском

Если человек пользуется англоязычной версией Excel, ему необходимо знать, как записывается эта функция на английском языке. За то, чтобы вытащить определенное количество знаков из строки, отвечает функция MID . Соответственно, если необходимо ориентироваться на количество байтов, то используется формула с MIDB.

Функция ПСТР и VBA в Excel

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

Range(“A2”).Value = Mid(Range(“A1”), 17, 10)

Распространенные ошибки в использовании функции ПСТР

При использовании функции ПСТР возможны ошибки, если неправильно соблюдать синтаксис или нарушить некоторые основополагающие правила:

1. Нельзя писать в качестве аргумента этой функции отрицательное значение. В таком случае будет выдана ошибка #ЗНАЧ.
2. Важно следить, чтобы начальная позиция не была большим числом, чем длина искомой строки. В ином случае будет выдано пустое значение.

Как вырезать часть текста ячейки в Эксель

А теперь давайте начнем обзор практических примеров, как возможно применение функции ПСТР .

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

Сама таблица выглядит следующим образом.

1

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

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

После этого получаем такой результат.

2

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

=ПСТР(A2:A8;НАЙТИ(” “;A2:A8)+1;100)

Здесь мы снова видим, что использовали функцию НАЙТИ , с помощью какой в этом примере ищем первоначальную позицию, содержащую пробел. Также мы добавили единицу к содержимому аргумента, чтобы перенести взор программы на первый символ марки товара. Чтобы упростить задачу поиска последнего символа мы просто решили написать число 100, которое гарантированно превышает длину строки.

Так можете делать и вы.

После того, как программа выполнит все необходимые расчеты, получаем следующую таблицу.

3

Как рассчитать возраст по дате рождения в Эксель

Теперь приведем еще один пример, с каким сотрудники, часто использующие Эксель, сталкиваются довольно часто. Перед нами есть база данных, содержащая три колонки: Фамилия, имя, отчество, а также дата рождения. И перед нами стоит задача определить, сколько лет человеку в данный момент, с использованием ПСТР.

Вот так выглядит таблица, с которой мы будем работать.

4

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

Эта функция сначала определяет ту часть строчки, которая содержится до символа пробела. Это делается с помощью функции НАЙТИ аналогично приведенным выше примерам. Далее с использованием функции РАЗНДАТ мы определяем количество полных лет. В нашем случае мы автоматически отсекаем тот участок дней, который остался до следующего дня рождения в этом году. Именно поэтому данная функция сможет указать всегда правильный возраст.

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

4

Несколько особенностей использования ПСТР в Excel

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

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

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

1. Текст. Это обязательный аргумент, который нужно передавать функции. Представляет собой или ссылку на ячейку, или непосредственно строку, из которой нужно извлекать требуемую информацию. Важно обратить внимание, что в последнем случае ее нужно облачать в кавычки. Независимо от формата строки, из нее будет доставаться определенная информация. Какая именно – задается следующими аргументами.
2. Начальная позиция. Этот аргумент также обязательный. Его задача – задать стартовую точку отсчета. Являет собой обязательно целое число, которое относится к положительным числам. То бишь, минимальное значение – 1. Если оказывается, что в аргумент была передана дробная часть, она отсекается.
3. Число знаков. И этот аргумент является обязательным. Таким образом, все аргументы, используемые в этой функции, необходимо использовать. Здесь есть такой нюанс. Если оказывается, что было в него передано число, которое больше длины строки, то возвращается вся строка.

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

Напоминаем, что синтаксис следующий:

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

Как говорится, повторение – мать учения. Поэтому давайте подведем небольшие промежуточные итоги:

1. Если на месте стартовой позиции задавать значение, которое по размеру больше исходной строки, то после всех операций, выполняемых функцией ПСТР вернется пустое значение.
2. Если применять единицу в качестве исходной позиции, а количество знаков указать такое, которое будет больше строки или равняться ей, то в качестве итога будет выведено все содержимое этой строки. Таким образом, можно использовать эту функцию в роли альтернативы, пусть и не такой удобной, ссылки на ячейку. В Excel ситуации бывают разные, поэтому иногда приходится выкручиваться из любой ситуации, в том числе, и такими причудливыми способами.
3. Будет возвращена ошибка #ЗНАЧ!, если использовать отрицательное значение в качестве начальной позиции. То же касается ситуации, если аргумент с числом знаков задается отрицательным значением. Важно запомнить навсегда, использовать нулевое или отрицательное значение в этом аргументе нельзя.

Примеры использования функции ПСТР

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

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

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

Сама таблица выглядит следующим образом:

5

В этом случае нужно применить формулу массива, а именно такую.

6

Какие аргументы использовались в этом случае?

1. А2:А10. Здесь перечисляется набор ячеек, представление дат в которых выполнено в текстовой форме. Из них и будет доставаться день.
2. 1 – это число, обозначающее первоначальную позицию, с которой будет осуществляться извлечение.
3. 2 – это последняя позиция.

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

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

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

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

В результате, у нас получается следующая таблица.

7

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

Таким образом, возможностей у функции ПСТР огромное количество. Ее можно использовать для обработки огромных массивов информации. А поскольку каждый год количество данных, которые нужно эффективно анализировать, постоянно увеличивается, необходимо искать качественные способы автоматизации. И хотя некоторые считают, что Эксель несколько устарел, в своей нише альтернатив этой программе нет. Хотя бы потому, что зная несколько формул, можно добиваться почти любого функционала электронных таблиц. Больших успехов вам и легкости в освоении этого ремесла.

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

Добавить новую строку стандартными средствами Excel можно следующим образом: правой кнопкой мыши кликнуть на номере строки, над которой нужно вставить новую строку и выбрать в контекстном меню пункт «Вставить»

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

Вставить пустую строку можно также и через кнопки на панели инструментов. Для этого выделите строку, над которой нужно вставить новую пустую строку. После этого зайдите на вкладку «Главная», в группу «Ячейки» и раскройте меню кнопки «Вставить», выберите пункт «Вставить строки на лист».

Добавление нескольких пустых строк в таблицу Excel

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

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

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

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

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

2. вставка заданного количества пустых строк как выше, так и ниже строк с искомым значением;

3. вставка разного количества пустых строк в зависимости от значения, указанного в заданном столбце;

4. вставка новых строк ниже каждой второй, третьей, пятой, n-ной строки выбранного диапазона (шаг задается пользователем);

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

6. вставлять пустые строки между заполненными (после каждой заполненной ячейки в столбце с заданным номером);

7. выбор диапазонов для вставки строк, предусмотрено несколько режимов:

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

б) используемые диапазоны – отличается от предыдущего режима тем, что обработка данных производится в используемом диапазоне каждого листа рабочей книги;

в) диапазон листа – диапазон, который выберет пользователь;

г) диапазоны листов – это одноименные диапазоны на всех листа рабочей книги.

*Внимательно выбирайте диапазон для получения желаемого результата!

8. выбор необходимых условий для значений ячеек.

Для числовых значений: равно/не равно, больше/не больше, меньше/не меньше.

Для текстовых значений: совпадает/не совпадает, содержит/не содержит, начинается/не начинается, заканчивается/не заканчивается.

Программа выполняет проверку всех ячеек заданного диапазона и при выполнении выбранного условия вставляет нужное пользователю количество пустых строк над/под строкой (на выбор), содержащей ячейку с заданным значением. На выбор пользователя представлено множество условий для значений ячеек, с помощью которых можно определить места для вставки новых строк, а также предоставлен выбор диапазонов для обработки данных. Для большей гибкости поиск ячеек с нужными значениями можно осуществлять раздельно, как по текстовым значениям ячеек, так и по числовым. Предусмотрен также поиск как пустых, так и непустых ячеек. Этот макрос позволяет также добавлять новые строки в заданном количестве в каждую n-ую строку выбранного диапазона. Если, к примеру, вставить по две пустые строки ниже каждой второй строки диапазона А2:А24, т ополучим следующий результат:

Вставка разного количества пустых строк

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

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

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

Удаление пустых строк

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

Вставить пустые строки в Excel через одну.

Как вставлять и удалять ячейки, строки и столбцы

​ «Как скрыть столбцы​ =ЕСЛИ(ОСТАТ(СТРОКА();2);»»;ИНДЕКС(A:A;СТРОКА()/2))​ строки между каждой​End Sub​

​Для наглядного примера удалим​​ же инструмент на​Создавая разного рода новые​ затем Insert ->​ за квадратик выделенного​AlexM​: ааа! тоже классная​ вставить 30 срок​

Вставка и удаление столбца

​ и не нужен​ с формулами, т.д.​ строки и далее​​ столбца, перед которым​​Чтобы вставить строку, выделите​​ в Excel».​​Копируем формулу вниз​​ строкой таблицы. Вручную​​Guest​

​ из нашего прайс-листа​ главной закладке «Вставить»-«Вставить​ таблицы, отчеты и​​ Module при этом​​ диапазона A2:I3 на​​: Еще вариант. см​​ формула. спасибо. всё​​ между заполненными? Формулой​​ по моему -​

​ Как это сделать,​ так же, как​ нам нужно вставить​ ее, а затем​​В ячейку Excel​​ по столбцу. Получится​​ это сделать сложно,​​: Я извеняюсь за​

Вставка и удаление строки

​ нумерацию позиций товара​ столбцы на лист».​ прайсы, нельзя заранее​​ откроется окошко вставки​​ Лист3?​​ файл​​ сохранила .​​ не очень удобно..вот​​ суть в том​

​ смотрите в статье​ вставить столбец.​ новый столбец. Появится​​ на вкладке​​ можно установить ссылку​​ так.​​ долго. Но, Excel,​​ неграмотность. Но можно​​ и столбец единиц​

​Или выделите два заголовка​ предвидеть количество необходимых​ кода.​коллеги, помогите пожалуйста.​​andreyZ​​AlexM​​ этот макрос при​​ что есть столбец​

Вставка ячейки

​ «Как скрыть столбцы​Этим способом также​ на букве адреса​Главная​​ на конкретную ячейку​​Какими способами быстро скопировать​

​ опять, дает нам​​ по подробней.​​ измерения – одновременно.​ столбца C и​ строк и столбцов.​

Как добавить строку, столбец в Excel.

Как добавить пустые строки между имеющимися?

​ мы отмечаем 4,5,6​​ появился новый столбец.​Вставить​ таблицу на другом​ «2» напишем цифру​ то можно сделать​: Спасибо за помощь. ​ выбирать в соответствующее​ зависит от порядка​ таблиц.​ Type:=8)​: сделал сортировку по​ написали и вот​ предоставленный ответ.​Помогите пожалуйста!​: Копируйте в ворд​ Excel».​ строки.​
​Обратите внимание!​и выберите пункт​ листе именно на​ «3». Формула получится​ так. Выделяем, при​kreg​ меню инструмента. А​

​ их выделения. Например,​​Обратите внимание, в данном​

​rs = rngD.Item(1).Row​​ последнему столбцу от​Задачу усложняет то,​_Boroda_​Lunatic​ как неформатированный текст​vveco​Нажимаем «вставить». Получилось так.​

​Адреса столбцов изменились.Теперь​​Удалить строки с листа​ строку этого заказчика.​
​ такая. =ЕСЛИ(ОСТАТ(СТРОКА();3);»»;ИНДЕКС(A:A;СТРОКА()/3))​
​ нажатой клавише «Ctrl»,​: А как сделать​
​ в контекстном меню​ через одну и​
​ уроке указываются горячие​rf = rngD.Item(rngD.Count).Row​ мин к макс​
​ что штрих-код формируется​
​: Читайте Правила форума.​

​: Всем привет. Подскажите​​ и заменяйте знак​: Вопрос в том,​Новые строки встали на​ новый столбец стал​.​Можно применить такие​Получится такая таблица.​ все строки, под​ так что-бы строки​ – без изменений.​ т.п.​ клавиши для добавления​Application.ScreenUpdating = False​ и вышла ошибка​ шрифтом «Free 3​

​ Пункты 4 и​​ чайнику, как можно​ абзаца на разрыв​ что значения из​ место старых строк,​ «В», а бывший​Можно также щелкнуть правой​

​ ссылки в списках​​Третий вариант.​ которыми нужно вставить​ добавлялись не во​ Только нужно их​Теперь добавим в прайс-лист​ или удаления строк​
​For i =​Russel​ of 9″, а​ 5q в особенности.​ решить следующую задачу.​ строки и знак​ сформированного в экселе​ а старые сместились​ столбец «В» (с​

​ кнопкой мыши выделенную​ по классам, ссылки​
​В​
​ пустую строку (каждую​
​ всем документе, а​
​ соответственно выделять по​ заголовок и новую​
​ и столбцов. Их​
​ rs To rf​
​: Я не вижу​
​ Сер. номер и​
​Тема закрыта​
​ Есть диапазон строк​
​ абзаца.​

​ столбца будут использоваться​ вниз, изменив свой​ цифрами 2) стал​

​ строку и выбрать​​ – на контакты​
​ставить пустые строки в​
​ строку отдельно, не​
​ только в выбранном​
​ номерам строк.​ позицию товара «Товар​

​ надо использовать после​​ * 2 -​ картинки с радикала.​ Инв. номер надо​andreyZ​ (все заполнены). Надо​Казанский​ в текстовом формате​ адрес.​ – «С».​ команду​
​ родителей, списки товаров,​
​Excel через одну.​ все вместе).​
​ диапазоне.​Чтобы удалить строку или​ новинка». Для этого​
​ выделения целой строки​
​ rs – 1​
​ Грузите сюда.​
​ писать любым текстовым​

Вставить заданное количество пустых ячеек через строку (Иное)

​: Всем привет! Есть​​ вставить заданное количество​: Каким образом данные​ в другой программе.​Добавить пустые строки в​Второй способ.​Вставить​ разделов, сотрудников по​Нам нужно вставить​Когда выделим все​Hugo​ столбец в Excel​ вставим две новых​ или столбца. Чтобы​ Step 2​andreyZ​ шрифтом​ табличка в приложеном​

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

​ нужные строки, отпускаем​​: А так правильней​
​ используйте горячие клавиши​ строки одновременно.​ выделить строку на​
​Rows(i + 1).Insert​

​:​​AlexM​ файле лист1, нужно​ диапазон через каждую​ в другую программу​ нужно чтобы значения​ две строки​– это вызвать​

​Удалить​​ сделать такие гиперссылки,​ несколько столбцов таблицы,​ клавишу «Ctrl». Нажимаем​ будет (и на​ CTRL+«минус» предварительно выделив​

​Выделите несмежный диапазон двух​​ которой стоит курсор​ Shift:=xlDown​

​andreyZ​​: Вы потянули за​ чтобы было так,​ заполненную строку, т.е.​

​ – через сохранение​​ были разделены пустой​
​, т.д.​

​ контекстное меню на​​.​ смотрите в статье​ на всю ширину​ на выделенную строку​ 2 умножать не​ их.​ ячеек A1;A4(обратите внимание​ нажмите комбинацию горячих​Next i​

​: блин, а как​​ квадратик выделенного диапазона​ как показано на​ сдвигать ячейки вниз.​
​ в txt или​

Вставить пустую строку через одну заполненую строку (Формулы/Formulas)

​ строкой.. Т.е. вручную​​Например, нам нужно​ любой ячейке столбца,​Выделите одну или несколько​ «Гиперссылка в Excel​ таблицы, т.д. Сначала​ правой мышкой. Выбираем​ надо, да и​Примечание. Вставка новых столбцов​ вместо символа «:»​ клавиш: SHIFT+ПРОБЕЛ. Горячие​Application.ScreenUpdating = True​ сюда то вставить?​ A2:I3 на Лист3?​​ листе2, то есть​ Большая просьба ,​ копированием – вставкой?​ вставлять пустые строки​​ в заполненной таблице,​

​ слева от которого​​ ячеек. Щелкните правой​

​ на другой лист».​​ напишем формулу как​ из контекстного меню​

​ можно iLastRow привлечь):​​ и строк на​ указан символ «;»​ клавиши для выделения​End Sub​
​ О_о​andreyZ​ вставить пустую строку​ только понятным языком.​
​Перевод строки в​ в экселе -​ между строками вставить​
​ будем вставлять новый​ кнопкой мыши и​
​Вставляйте и удаляйте строки,​ во втором варианте.​ функцию «Вставить» -​
​For i =​ самом деле является​ – это значит,​
​ столбца: CTRL+ПРОБЕЛ.​

​ после каждой заполненой​​ если вообще возможно​ ячейке вводится с​ не вариант :),​ пустые строки по​ столбец. Также выбираем​
​ выберите команду​​ столбцы и ячейки​ Затем, скопируем эту​ «Строку». Получится так.​ 100 To 2​
​ заменой. Ведь количество​ выделить 2 несмежных​

​Допустим у нас есть​Вставляем код.​: Через «Прикрепить файл».​ квадратик I3.​ и вставить в​ , то без​ помощью Alt+Enter. Но​ пока делаю это​

​ всей таблице, т.д.​​ «вставить» во втором​Вставить​ для оптимального размещения​

​ формулу вправо на​​Добавить пустые столбцы в​ Step -1​
​ строк 1 048​ диапазона, для убедительности​ прайс, в котором​Переходим в окно​
​Картинку я уже​теперь получается как​ ячейку столбца I​ всяких там макросов.​ и при сохранении​
​ в Notepad++ добавляя​ Как это сделать,​ разделе появившегося окна.​.​

​ данных на листе.​​ нужное количество столбцов.​Excel через один​

​Cells(i, 1).EntireRow.Insert​​ 576 и колонок​ введите A1;A4 в​ недостает нумерации позиций:​

​ Excel​ посмотрел. Вас смущают​

​ надо, но как​​ значения из столбца​ очень нужно для​ в txt, и​

​ пустую строку, что​​ смотрите в статье​
​ И здесь уже​

​В окне​​Примечание:​​Например, в нашей​​.​Next i​ 16 384 не​ поле имя и​Чтобы вставить столбец между​Запускаем макрос Сервис​
​ ошибки в строках​ решить проблему с​ G и H.​

​ работы !​​ при копировании Excel​ тоже не айс..​ «Вставить пустые строки​ выбираем – «столбец»​Вставка​ В Microsoft Excel установлены​ таблице два столбца.​

​Выделяем столбцы с​​roka​ меняется. Просто последние,​ нажмите Enter). Как​ столбцами для заполнения​

​ -> Макрос ->​​ 3,5,7,9? Покажите формулу,​ разными шрифтами?​ Надеюсь объяснил понятно​

​Serge_007​​ добавляет кавычки к​

​Самое простое решение​​ в Excel через​ -> «ОК». Про​выберите строку, столбец​

​ следующие ограничения на​​ Мы копируем формулу​
​ нажатой клавишей «Ctrl»​: еще как вариант,​ заменяют предыдущие… Данный​ выделять несмежные диапазоны​ номеров позиций прайс-листа,​

​ Макросы, выбираем макрос​​ которая в них.​upd. с шрифтами​В excele полный​: Без макросов вставить​ содержимому таких ячеек.​
​ было бы вставить​ одну».​ контекстное меню читайте​

Как в Excel’е (2010) вставить строки после каждой строки в выбранном диапазоне?

​ или ячейку для​ количество строк и​ в ячейку D1.​ (каждый столбец отдельно).​ перейти на нужное​ факт следует учитывать​

​ вы уже знаете​​ можно воспользоваться одним​ «Proredit» и жмем​
​andreyZ​ всё ок. Как​ профан, поэтому не​ пустые строки в​
​ Вот результат копирования-вставки:​ в конец содержимого​Как создать таблицу​ здесь.​
​ вставки.​ столбцов: 16 384 столбца​Теперь выделяем ячейки​ Затем из контекстного​ место и сделать​ при заполнении листа​ из предыдущих уроков.​ из двух способов:​
​ выполнить.​
​: смущает ошибки и​
​ убрать у значений​
​ знаю как это​ исходные данные -​
​без перевода строки​ каждой ячейки символ,​ в Excel смотрите​Сразу посмотрим это меню.​
​Добавить и удалить строку,​ в ширину и​ C1 и D1​
​ меню выбираем функцию​
​ActiveCell.Resize(i).EntireRow.Insert​
​ данными более чем​
​Теперь снова используйте инструмент​Перейдите курсором и активируйте​Francofan​ то, что не​ из столбца H​
​ реализовать​ только вручную​
​ в ячейке​
​ который бы добавлял​
​ в статье «Как​
​ Здесь можно выбрать​
​ столбец в Excel​
​ 1 048 576 строк в​ и копируем (протягиваем)​
​ «Вставить столбец».​где i необходимое​ на 50%-80%.​ «Главная»-«Вставка»-«Вставить строки на​ ячейку A1. Потом​

​: Выделить строку​​ совпадает значения в​
​ *?​
​Заранее большое спасибо​Формулами можно в​1​
​ пустую строку.. вот​ сделать таблицу в​

​ то, что хотим​​можно в любом​
​ высоту.​

​ их вниз до​​Как настроить таблицу, чтобы​ количество строк​

​Beldiver​​ лист». На рисунке​ перейдите на закладку​Нажать CTRL​ соответствующих ячейках ниже​получилось почти как​китин​

​ соседнем столбце:​​2​

​ какой только?​​ Excel» тут.​ вставить: столбец, строку,​ месте таблицы. Можно​Чтобы вставить столбец, выделите​

Как вставить строку или столбец в Excel между строками и столбцами

​ конца таблицы. Получится​ столбец добавлялся автоматически,​Igor67​: Вопрос, такого характера.​ видно как вставить​ «Главная» раздел инструментов​И не отпуская​ штрихкодов и G,H​ надо. Как добавить​: нетути​200?’200px’:”+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЦЕЛОЕ(СТРОКА()/3)=СТРОКА()/3;ИНДЕКС(C$3:C$11;СТРОКА()/3);»»)​4​

​KuklP​Как удалить строку, столбец​ ячейки, которые встанут​ вставить сразу несколько​

​ его, а затем​ так.​ читайте в статье​: А я бы​Как в exele​ пустую строку в​ «Ячейки» кликните по​ эту клавишу выделить​формула в ячейках​ пустые строки в​andreyZ​_Boroda_​с переводом строки​: crlf​

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

​ в Excel.​ справа или внизу​ строк, столбцов в​

​ на вкладке​Таблицу Excel можно​ «Как добавить столбец​ это понял (для​ добавить пустые строки​

1. ​ Excel между строками.​ инструменту «Вставить» из​ следующие строки.​ с ошибкой =СЦЕПИТЬ(#ССЫЛКА!;»,​ столбцах A:H?​: странно О_о Вот​: В общем случае​ в ячейке​Guest​
2. ​Чтобы удалить строки,​ от выделенной ячейки.​ нужном месте таблицы.​Главная​ настроить так, что​

​ в Excel автоматически».​ диапазона) как вставка​ между заполнеными?​

​Несложно догадаться о втором​

Вставка нескольких столбцов между столбцами одновременно

​ выпадающего списка выберите​Потом нажать ВСТАВИТЬ​ «;#ССЫЛКА!)​Russel​ добавил​В ячейке I1​»1​: мм.. что то​ столбцы, нужно выделить​Если сначала выделить​ Есть несколько способов.​нажмите кнопку​ будут автоматически сделаны​

​Если нужно вставить​ ячеек:))​То есть, есть​ способе. Нужно выделить​ опцию «Вставить столбцы​

​ СТРОКИ​Т.е. есть столбец данных,​: Пункт 2 неправильно​Russel​ количество пустых строк,​»​ не работает метод​ их и, в​ не одну, а​Как добавить столбец в​Вставить​

Как вставить строку в Excel между строками?

​ разделительные линии между​ несколько строк или​Cells(i, 1).EntireRow.Insert Shift:=xlDown​ список с 100​ заголовки строк 1​ на лист».​

​Я татьяна​ я хочу проредить​ сделали.​: 1. В любом​ которые нужно добавить​»2​ или я не​ контекстном меню во​ несколько ячеек, столбцов,​ таблицу Excel​и выберите пункт​ строками, например, по​ столбцов не во​

​я​ строками, как автоматически​ и 3. Кликнуть​Щелкните правой кнопкой мышки​: Вау! Всё получилось!​ его пустыми строками.​

​andreyZ​ свободном столбце делаете​200?’200px’:”+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ОСТАТ((СТРОКА()-СТРОКА(G$3));(I$1+1))=0;ИНДЕКС(C$3:C$11;(СТРОКА()-СТРОКА(G$3))/(I$1+1)+1);»»)​»​ понял как это​ втором разделе, выбрать​ строк (столько, сколько​.​

​Вставить столбцы на лист​ датам, по ценам,​ всей таблице, то​: Ребята, а если​ после каждой строчки​

​ правой кнопкой по​ по заголовку столбца​Спасибо огромное!​

Удаление строк и столбцов

​ Данных очень много,​: открыл ваш файл,​ сквозную нумерацию имеющихся​Lunatic​»4​ правильно вставить -​

​ функцию «Удалить». Или​ нам нужно вставить),​Первый способ.​.​ т.д. Подробнее о​

​ можно использовать другие​ нужно вставлять по​ добавить пустую? Чтоб​ одной из выделенных​ A. Из появившегося​Организация кбрцдо​ вручную – не​ но это не​

​ строк​: ух ты . ​»​ если можно -​ выделить строки, столбцы​ то и вставится​Чтобы вставить столбец​Чтобы удалить столбец, выделите​ таком способе визуализации​

​ способы. Смотрите об​ 45 строк, помогите​ получилось в итоге​ строк и выбрать​ контекстного меню выберите​

​: Алексей Матевосов, спасибо​ вариант.​ то, что нужно​2. Копируете/вставляете получившиеся​ работает! спасибо, Serge​конец текста​ поподробнее..​ на строках их​ столько же.​ в Excel, нужно​ его, а затем​ данных в таблице,​ этом статью «Как​

Добавление пустых строк между заполненных

​ неумехе​​ 200 строк, заполненная-пустая-заполненная-пустая?​
​ опцию «Вставить».​ опцию «Вставить»​ огромное!​
​Алексей матевосов (alexm)​или я не​ номера ниже в​ 007, а у​А вставлять пустые​KuklP​ адресов. Нажать правой​
​Третий способ.​ установить курсор на​ на вкладке​ читайте в статье​

​ добавить строку, столбец​​fjeka​

​Или может такое​
​Чтобы добавить строку или​Теперь можно заполнить новый​
​Артур минвалеев​: Можно задачу решить​ понимаю чего-то?​
​ этот же столбце​
​ нас с позволения​
​ строки в Excel​

​: Поскольку Вашего примера​​ мышкой на цифру​Можно настроить Excel,​ строке названия столбцов,​

​Главная​​ «Разделительная линия строк​ в Excel».​: Как сделать чтоб​ в ворде проделать(потом​ столбец в Excel​

​ столбец номерами позиций​​: 6 часов мучался​ макросом​

​Russel​​ в свободные строки​

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

​ в таблице Excel»​​Второй вариант.​ вставлялось по 2​ в ексель загоню),каким​ используйте горячие клавиши​ прайса.​

​ искал так как​Разрешаем выполнение макросов​: Теперь отсортируйте по​
​3. Сортируете этот​
​ так ничего и​

​Guest​​Public Sub www()​ и выбрать функцию​ автоматически. Смотрите статью​
​ рядом с которым​
​Вставить​ здесь.​

​Вставить пустые ячейки в​​ пустые строчки?​ другим редактором?​ CTRL+SHIFT+«плюс» предварительно выделив​​
​ не понимаю ни​

​ Сервис -> Макрос​​ последнему столбцу от​ столбец от мин​ не ответил )))​: Спасибо!​

​Dim c As​​ «Удалить».​ «Как добавить столбец​ нужно вставить столбец.​

Я нашел этот код в предыдущей теме. Он вставляет пустую строку после изменения данных.

Это прекрасно работает, но из-за этой части:

Он перестает работать на пустых строках. Мне нужно, чтобы он игнорировал пустые строки и останавливался только тогда, когда в диапазоне больше не осталось данных. Есть идеи? Я очень новичок в кодировании!

Затем я вставил код, чтобы вставить пустую строку между каждым изменением данных в 1-м столбце. Теперь мне нужно запустить второй код, который вставлял бы пустую строку между каждым изменением данных в 3-й колонке, чтобы это выглядело так:

Смотрите видео после статьи

видео может дополнять статью или содержать её полностью

ПСТР в Excel относится к одной из самых часто используемых функций, поскольку при обработке больших массивов данных, импортированных из других программ, задача по обрезке строк возникает довольно часто. Формула позволяет получить часть строки, задав начало и конец обрезки.

Посмотрим применение функции ПСТР в Excel на практике, но прежде всего разберёмся с особенностями данной формулы.

Синтаксис ПСТР в Excel

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

Поскольку целью является получение подстроки, то очевидно, что задать нужно следующие параметры:

• Текст
  Текстовая строка, часть которой требуется получить. Это может быть ссылка на ячейку (чаще всего), текстовая константа (имеет смысл только для обучения) или результат работы другой функции.
• Начальная позиция
  Считается слева и указывает функции ПСТР, откуда начинается нужный нам фрагмент строки (подстрока). Нумерация начинается с 1, а не с нуля!
• Число знаков
  Сколько символом нужно получить. Минимум 1. Если указать 0, то на выходе получим пустую строку (ничего).

Сама формула в обобщённом виде выглядит следующим образом:
ПСТР(текст; начальная_позиция; число_знаков)

Вообще в руководстве Excel есть ещё функция ПСТРБ, предназначенная для работы с мультибайтовыми строками, но в нашей версии программы эта функция не поддерживается. Если у Вас она окажется, то имейте в виду, что вместо числа знаков последним параметром указывается число байт, поскольку один символ занимает больше одного байта. Этот случай мы не рассматриваем.

На выходе вы получим подстроку или ошибку вида «#ЗНАЧ!», если какие-то параметры были заданы неверно. Например, позиция начала подстроки может быть указана с нуля или число символов в подстроке отрицательное. Про обработку ошибок мы писали ранее.

Можно запомнить следующие особенности функции ПСТР:

• Если значение «начальная_позиция» больше, чем длина текста, то функция ПСТР возвращает строку «» (пустую строку).
• Если значение «начальная_позиция» меньше, чем длина текста, но сумма значений «начальная_позиция» и «число_знаков» превышают длину текста, функция ПСТР возвращает знаки вплоть до конца текста.
• Если значение «начальная_позиция» меньше 1, то функция ПСТР возвращает значение ошибки #ЗНАЧ!.
• Если значение «число_знаков» отрицательно, то функция ПСТР возвращает значение ошибки #ЗНАЧ!.
• Если значение «число_байтов» отрицательно, то функция ПСТРБ возвращает значение ошибки #ЗНАЧ!.

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

Как в Excel получить часть строки (подстроку) при помощи функции ПСТР

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

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

Строка в ячейке B14: «1234567890»

Допустим, нам нужно получить цифру 3. В таком случае выражение будет выглядеть так: «=ПСТР(B14;3;1)». То есть мы указываем, что нужно взять один символ начиная с третьей позиции в тексте. В результате мы как раз и получим цифру 3. На практике этот подход можно использовать для получения различных знаков из предустановленного набора символов.

Остальные примеры, вполне типовые, смотрите в прикреплённом файле и на видео.

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

Если же Вы и так хорошо знаете программы, придумайте интересный пример получения подстроки при помощи функции ПСТР и опубликуйте его в комментариях в помощь менее опытным нашим читателям!