Повтор действия в excel

Обновлено: 05.07.2024

Чтобы извлечь первое слово из строки, формула должна найти позицию первого символа пробела, а затем использовать эту информацию в качестве аргумента для функции ЛЕВСИМВ. Следующая формула делает это: =ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1) .

Эта формула возвращает весь текст до первого пробела в ячейке A1. Однако у нее есть небольшой недостаток: она возвращает ошибку, если текст в ячейке А1 не содержит пробелов, потому что состоит из одного слова. Несколько более сложная формула решает проблему с помощью новой функции ЕСЛИОШИБКА, отображая все содержимое ячейки, если произошла ошибка:
=ЕСЛИОШИБКА(ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1);A1) .

Если вам нужно, чтобы формула была совместима с более ранними версиями Excel, вы не можете использовать ЕСЛИОШИБКА. В таком случае придется обойтись функцией ЕСЛИ и функцией ЕОШ для проверки на ошибку:
=ЕСЛИ(ЕОШ(НАЙТИ(" ";A1));A1;ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1))

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

Извлечение последнего слова строки — более сложная задача, поскольку функция НАЙТИ работает только слева направо. Таким образом, проблема состоит в поиске последнего символа пробела. Следующая формула, однако, решает эту проблему. Она возвращает последнее слово строки (весь текст, следующий за последним символом пробела):
=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("*";ПОДСТАВИТЬ(A1;" ";"*";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;"";"")))))

Но у этой формулы есть такой же недостаток, как и у первой формулы из предыдущего раздела: она вернет ошибку, если строка не содержит по крайней мере один пробел. Решение заключается в использовании функции ЕСЛИОШИБКА и возврате всего содержимого ячейки А1, если возникает ошибка:
=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("*";ПОДСТАВИТЬ(A1;" ";"*";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";"")))));A1)

Следующая формула совместима со всеми версиями Excel:
=ЕСЛИ(ЕОШ(НАЙТИ(" ";A1));A1;ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("*";ПОДСТАВИТЬ(A1;"";"*";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))))

Извлечение всего, кроме первого слова строки

Следующая формула возвращает содержимое ячейки А1, за исключением первого слова:
=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ":A1;1)) .
Если ячейка А1 содержит текст 2008 Operating Budget, то формула вернет Operating Budget.

Формула возвращает ошибку, если ячейка содержит только одно слово. Следующая версия формулы использует функцию ЕСЛИОШИБКА, чтобы можно было избежать ошибки; формула возвращает пустую строку, если ячейка не содержит более одного слова:
=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;1));"")

А эта версия совместима со всеми версиями Excel:
=ЕСЛИ(ЕОШ(НАЙТИ(" ";A1));"";ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;1)))

Например, у вас есть список значений в столбце A, и вы хотите повторить значения определенное количество раз в зависимости от количества раз в столбце B, как показано на скриншоте слева, как вы могли бы добиться этого в Excel?

Повторите значения ячеек X раз с формулой и вспомогательным столбцом
Повторить значения ячеек X раз с кодом VBA
Копируйте и вставляйте значения ячеек X раз с потрясающей функцией

Повторите значения ячеек X раз с формулой и вспомогательным столбцом

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

1. Вставьте столбец слева от столбца A и введите 1 в ячейку A2, см. Снимок экрана:

2. Затем положите эту формулу = A2 + C2 в A3 и перетащите маркер заполнения в ячейку A6, см. снимок экрана:

3. Затем введите 1 в ячейку D2 и заполните число, перетащив Автозаполнение на 12, что является общим числом раз в столбце C, см. Снимок экрана:

4. Затем введите эту формулу = ВПР (D2; $ A $ 1: $ B $ 6,2) в ячейку E2 и скопируйте, вы получите следующий результат:

Заметки:
1. В этой формуле D2 обозначает первую ячейку, которую вы заполняете числовой последовательностью, и A1: B6 обозначает диапазон первого вспомогательного столбца и исходных значений ячеек, которые необходимо повторить.
2. После получения повторяющихся значений вы можете скопировать и вставить их как значения в любое другое место.

Легко копируйте и вставляйте строки X раз на основе определенного числа

Обычно нет хорошего метода для многократного копирования и вставки строк, за исключением ручного копирования и вставки. Но с Kutools for Excel's Повторяющиеся строки / столбцы на основе значения ячейки функция, вы можете легко решить эту проблему. Нажмите, чтобы загрузить Kutools for Excel!

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 60 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Повторить значения ячеек X раз с кодом VBA

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

1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модульи вставьте следующий код в Окно модуля.

Код VBA: повторить значения ячеек X раз

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

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

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

Копируйте и вставляйте значения ячеек X раз с потрясающей функцией

Иногда вам может потребоваться скопировать и вставить значения ячеек x раз в зависимости от определенного числа, Kutools for Excel's Повторяющиеся строки / столбцы на основе значения ячейки может помочь вам быстро скопировать и вставить строки на основе указанного вами числа.

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

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Нажмите Kutools > Вставить > Повторяющиеся строки / столбцы на основе значения ячейки, см. снимок экрана:

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

4, Затем нажмите Ok or Применить кнопку, вы получите такой результат, как вам нужно:

Немного теории. Циклом называется конструкция, которая некоторое (определяемое) количество раз выполняет заданные действия. Например, Вам нужно перебрать некий массив данных и выделить в нем пустые поля. В программировании это реализуется при помощи циклов. В VBA наиболее частым вариантом является конструкция For i = 0 to n … Next i.

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

Также отдельно имелись сметы по каждому такому проекту с детализацией статей затрат и с указанием исполнителя по каждой из статей с указанием доли участия. По каждой из статей могло быть до 4 исполнителей.

Задача: свести это в одну таблицу для последующей обработки через ту же сводную таблицу. То есть требовалось получить вот такое представление:

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

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

В нашем примере получалось три цикла (в порядке от младшего к старшему): тип исполнителя (цикл 1), статья затрат (цикл 2), проект (цикл 3). Алгоритм выглядит примерно так:

Цикл 3 (проект)

Цикл 2 (статья)

Цикл 1 (тип исполнителя)

Конец цикла 1

Конец цикла 2

Конец цикла 3

Так бы примерно выглядела бы и структура кода VBA для реализации этих трех циклов, но в самом Excel так сделать нельзя. Что же делать?

Давайте еще раз обратимся к сути цикла: это повторение какого либо действия определенное количество раз. Теперь рассмотрим это на примере одного цикла – цикла 1 (тип исполнителя).

ВАЖНО! Не забудем вычесть заголовок.

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

если предыдущее значение статьи равно количеству статей, то 1,

если не равно, то предыдущее значение + 1,

Вот так это выглядит в экселе:

Осталось только добавить формулы СМЕЩ в ячейки с данными.

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

Интересная статья, но без острой потребности вникать лень.

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

Но я их находил, в основном, на пленетеэксель (не сочтите за рекламу).

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

Плюсану, конечно, но жду таки котиков и сисек, ну или байку какую нибудь )))

Внезапно для решения рабочих вопросов потребовалось резко изучить VBA. Можешь подсказать книги/курсы/видео для поверхностного изучения синтаксиса и общей логики языка?

И растягиваем вниз

Во-первых, @ArtemTabolin, спасибо - делаешь хорошее дело!

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

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

Если честно, то меня эксел бесит :)

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

Я прям благоговею перед их создателями, считаю их просто монстрами. И одновременно мне их жалко, жалко их потраченный впустую труд. Что люди не делают, лишь бы не изучать VBA и SQL :)

И я показываю, им как все эти ужасные формулы на несколько строк переписываются маленьким скриптиком в VBA редакторе.

А если еще на компьютере есть MS Access то, тут вообще возможна истинная магия.

А не легче это все будет делать функциями SUMIFS , COUNTIFS и подобными (сорри, не в курсе как они на русском). Или версия екселя только старая доступна?

А где пример файла что бы вы живую поглядеть формулы?

Готовый файл здесь:

Подобное вроде решается через индекс, счётесли и поискпоз, без каких-то дополнительных столбцов. Разве нет?

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

Сохраню на всякий. Спасибо.

Ребят, как табель в экселе посчитать, если руководство требует записей типа 7/5 в одной клетке (7 часов в день, из них 5 ночных)?

На волне

Токсичная реклама по ТВ

Я чёт только сейчас заметил, насколько токсичная реклама какой-то фигни от простатита крутится по ТВ.

По сюжету какой-то мужик идёт в туалет в который раз, а какая-то его коллега обсуждает это с кем-то, типа "оо, третий раз уже". Бля, сиди работай, хуй ли ты считаешь, кто сколько раз в туалет сходил.

Эта попытка надовить на тревожность "а вдруг это кто-то заметит" и возвести ее в "это точно все видят и даже обсуждают за спиной" просто бесит. И это бесит это - человека, который пока далёк от этого. Хз что чувствуют люди, для которых это реально проблема

Плотность белочки найдите в таблице

С прошедшим днем преподавателя высшей школы. Оказывается и такое бывает.

Эти на дорогах

Дружба vs отношения

Дрова

Доходчиво

Растяжка

Пошутил

Российские школьники установили рекорд на олимпиаде по астрономии и астрофизике

Упущенная возможность

Ответ на пост «Гвоздь»

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

В ноги и руки вставили спицы, для фиксации костей, левая нога стала короче правой. Пришла в себя, никого не помнит, себя не знает, разговаривает с трудом. Болше мычит, или экает. Хочет что-то сказать, рукой машет, и э-э-э!

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

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

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

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

Ctrl+ F2: переключиться в режим предварительного просмотра.

F9 : рассчитать все листы во всех открытых книгах

Ctrl + Shift + U: развернуть или свернуть строку формул.

F11 : создать гистограмму на основе выбранных данных (на отдельном листе)

Alt + F1: создать встроенную гистограмму на основе выбранных данных (тот же лист)

Ctrl + F: поиск в электронной таблице или использование функции "Найти и заменить"

Ctrl + Tab : переключение между открытыми книгами

Alt + F8: создать, запустить, отредактировать или удалить макрос.

Alt + F11: открыть редактор Microsoft Visual Basic для приложений.

Перемещение по листу или ячейке

Стрелка влево / вправо: перемещение на одну ячейку влево или вправо

Ctrl + стрелка влево / вправо: переход к самой дальней ячейке влево или вправо в строке

Стрелка вверх / вниз: перемещение на одну ячейку вверх или вниз

Ctrl + стрелка вверх / вниз: переход к верхней или нижней ячейке столбца.

Ctrl + End : перейти к самой нижней правой используемой ячейке

F5 : перейдите в любую ячейку, нажав F5 и введя координаты ячейки или имя ячейки.

Home : перейти к крайней левой ячейке в текущей строке (или перейти к началу ячейки при редактировании ячейки)

Page Up / Down : перемещение на один экран вверх или вниз по листу.

Alt + Page Up / Down: перемещение на один экран вправо или влево по листу.

Ctrl + Page Up / Down: переход к предыдущему или следующему листу

Выбор ячеек

Shift + стрелка влево / вправо: расширить выделение ячейки влево или вправо.

Ctrl + Shift + Space: выделить весь рабочий лист

Редактирование ячеек

Shift + F2: добавить или отредактировать комментарий к ячейке.

Ctrl + X: вырезать содержимое ячейки, выбранных данных или выделенного диапазона ячеек.

Ctrl + C или Ctrl + Insert: копирование содержимого ячейки, выбранных данных или выделенного диапазона ячеек.

Ctrl + V или Shift + Insert: вставить содержимое ячейки, выбранных данных или выделенного диапазона ячеек.

Ctrl + Alt + V: открыть диалоговое окно Специальная вставка.

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

Alt + Enter: вставить жесткий возврат в ячейку (при редактировании ячейки)

F3: вставить имя ячейки (если ячейки указаны на листе)

Esc: отменить запись в ячейке или строке формул

Enter: заполните запись в ячейке или строке формул.

Форматирование ячеек

Ctrl + B : добавление или удаление жирным шрифтом содержимого ячейки, выбранных данных или выделенного диапазона ячеек.

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

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

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