How to automate tasks in google sheets with macros

ТРАНСП – меняем местами строки и столбцы

Сложность: низкая.

Функция =ТРАНСП (или =TRANSPOSE) работает с массивами ячеек и меняет местами строки и столбцы.

Синтаксис:

=ТРАНСП(массив_или_диапазон)

Применение

Функция ТРАНСП полезна при работе со сводными таблицами. В некоторых случаях смена отображения данных в таблице улучшает представление данных и с таблицей работать удобнее.

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

Нам нужно проанализировать и сравнить между собой типы кампаний. Удобнее это делать, когда данные по каждому типу кампании расположены в одну строку. Чтобы быстро получить нужный нам вид таблицы, используем функцию =ТРАНСП, в аргументах которой задаем диапазон исходной таблицы):

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

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

Как импортировать макросы

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

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

Откройте Google Sheet с макросом, который вы хотите скопировать, а затем нажмите «Инструменты»> «Макросы»> «Управление макросами».

Затем нажмите значок «Еще» рядом с макросом, который вы хотите скопировать, а затем нажмите «Редактировать скрипт».

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

Выделите макрос (ы), которые хотите скопировать, затем нажмите Ctrl + C. Обязательно скопируйте все до закрывающей точки с запятой включительно.

Теперь откройте другую таблицу, в которую нужно импортировать макрос, и нажмите «Инструменты»> «Макросы»> «Записать макрос».

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

Снова нажмите «Сохранить».

Откройте скрипт Google Apps, щелкнув Инструменты> Редактор скриптов, а затем откройте файл macros.gs на левой панели. Удалите существующую функцию, а затем нажмите Ctrl + V, чтобы вставить макрос из другого листа.

Нажмите Ctrl + S, чтобы сохранить сценарий, закройте вкладку и вернитесь в свою электронную таблицу.

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

Затем нажмите Инструменты> Макросы> Импорт.

Наконец, нажмите «Добавить функцию» под макросом, который вы хотите добавить.

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

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

Brady GavinBrady Gavin has been immersed in technology for 15 years and has written over 150 detailed tutorials and explainers. He’s covered everything from Windows 10 registry hacks to Chrome browser tips. Brady has a diploma in Computer Science from Camosun College in Victoria, BC. Read Full Bio »

Conclusion

In this tutorial you learned how to use macros to automate repetitive tasks in Google Sheets. You don’t even have to write Apps Script code! Simply begin recording a macro, perform a set of actions manually and save the maco. That’s it! You can then apply those same set of actions in the future by running the macro that you recorded.

Macros are essentially just Google scripts behind the scenes so you can use them to learn how to implement some functionality in Apps Scripts. Simply record a macro and then look at the code that was auto-generated for you.

Macros are a powerful and underutilized tool in Google Sheets. You can get the benefit of Apps Scripts without even knowing how to code!

Hope this tutorial gave you ideas for things you can automate to become more productive in Google Sheets. Thanks for reading!

Добавить макросы в новые таблицы

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

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

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

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

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

Совет . У вас есть скрипт Google Apps, который вам хотелось бы упростить? Вы можете превратить его в макрос, вставив сценарий в редактор сценариев макроса, как указано выше.

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

Делайте больше с Google Таблицами

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

Или используйте интеграцию с Google Таблицами Zapier, чтобы ваша электронная таблица работала за вас, даже когда вы спите. Хотите, чтобы ваши новые контакты или заказы автоматически добавлялись в электронную таблицу по мере поступления, или хотите создавать новую электронную таблицу проекта каждое утро понедельника? Вы можете сделать это и многое другое с помощью интеграции Zapier с Google Sheets, которая подключает более 1000 приложений к вашей электронной таблице. Вот некоторые из самых популярных:

Как записать макрос в Google Sheets

Запустите Google Sheet и нажмите Инструменты> Макросы> Запись макроса.

Это открывает меню записи в нижней части окна, с двумя вариантами записи ваших действий:

Абсолютные ссылки: макрос будет выполнять задачи только в тех ячейках, которые вы записали. Если вы выделите курсором ячейку B1, макрос выделит только курсив B1 независимо от того, на какую ячейку вы щелкнули.
Относительные ссылки . Макрос будет выполнять задачи в выбранных ячейках независимо от того, где они находятся на листе. Если вы выделите курсором B1 и C1, вы можете повторно использовать один и тот же макрос для выделения курсором ячеек D1 и E1 позже.

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

После того, как вы захватили все действия для этого макроса, нажмите «Сохранить».

Введите имя для вашего макроса. Google также позволяет создавать ярлыки для десяти макросов. Если вы хотите привязать макрос к сочетанию клавиш, введите число от 0 до 9 в соответствующем поле. Когда вы закончите, нажмите «Сохранить».

Если вам нужно изменить имя макроса или ярлык, вы можете отредактировать макрос, нажав Инструменты> Макросы> Управление макросами.

В открывшемся окне настройте по желанию, а затем нажмите «Обновить».

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

Создание нового файла сценария

Используя ту же таблицу и проект скриптов, что и в разделе выше с макросами, вы можете следовать инструкциям ниже, чтобы узнать, как создать новый сценарий (который в конечном итоге можно использовать для создания своей первой настраиваемой функции!):

Чтобы создать новый файл Apps Script в текущем проекте, в левой части редактора рядом с полем «Файлы» нажмите «Добавить файл» > Скрипт.
Назовите новый файл сценария «Пользовательские функции» (Apps Script автоматически добавляет расширение «.gs» к имени).

В редакторе появится новая вкладка с именем «Пользовательские функции.gs».

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

Формулы

В Google Sheet 371 функция! Здесь их полный список с описаниями на русском языке. Они распределены по 15 разделам:

Для справки, в Excel их на сто функций больше. Если что-то очень нужное, как раз из этой сотни, это не повод отказываться от Google Spreadsheet. Ведь можно создать функцию под себя!

Инструкция по созданию собственных функций

1. Открываем редактор скриптов:

2. Набираем код функции:

function c100wN(x) {
x = x*100*1.4;
return x;
}

3. Сохраняемся.

4. Пробуем:

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

P.S. В Excel тоже можно создавать пользовательские функции.

Как просто подключить форму на лендинге к google таблице

У Phil Nash есть хорошая серия статей о том какие манипуляции возможно делать с google таблицами используя их API. В одной из статей есть короткий и простой туториал как подключить форму на посадочной странице (лендинг) к google таблице. Этот туториал может быть очень полезным в быту, он иллюстрирует простой (кустарный) способ как сделать легкую и небольшую автоматизацию. Для него нужно всего лишь готовый html-шаблон и доступ к аккаунту на google Drive. Функционал основан на готовой библиотеке google_drive. С помощью нее можно читать/записывать информацию в файлы/таблицы в Google Drive/Docs.

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

Подготовка google Drive

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

Нажимаем ссылку “Включить API”. Далее, из всего разнообразия продуктов google, нас интересует “Google Drive API”. Так как у нас нет созданного профиля, высветиться предупреждение “Чтобы использовать этот API, нужно создать учетные данные.”. Нажимаем кнопку “Создать” и настраиваем доступы как на скриншоте.

На втором этапе:

Придумываем и заполняем “Название сервисного аккаунта”
Выбираем роль Project -> Редактор
Оставляем Тип ключа -> json

Для подробностей, можно посмотреть gif от Phil Nash.

Стучимся к таблице

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

Создадим отдельную папку, например docs_test. Переместим в эту папку файл-ключик и переименуем его в client_secret.json. Создадим файл Gemfile и пропишем в него следующий код:

Запустим из консоли команду “bundle install”. Создадим файл app.rb и поместим в него следующий код:

В переменную doc_url нужно вместо текущего значения вставить url к google таблице. Запустим файл app.rb из консоли выполнив “ruby app.rb”.

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

Интеграция формы

Итак, у нас есть готовая верста лендинга, а именно:

Файл index.html где находится вся верстка
Остальные папки с ресурсами такими как: css, img, js, lib и прочее

Создадим папку, например landing_page и перейдем в нее. Переместим в эту папку файл-ключик и переименуем его в client_secret.json. Создадим файл Gemfile и запишем в него следующее:

Выполним “bundle install”. Создадим папку public и перенесем в нее все папки с ресурсами которые нужны для верстки (css, img, js, lib и прочее). Создадим папку views и перенесем в нее файл index.html предварительно переименуя его в index.erb.

Теперь создаем файл app.rb в котором будет вся логика работы.

Отлично, теперь можно запустить наш лендинг локально, выполнив “bundle exec ruby app.rb”. Увидеть его и отправить данные можно зайдя локально на http://localhost:4567/.

Вот таким способом можно собирать подписную базу с лендинга на google Таблицы. Этот маленький скрипт можно без проблем загрузить на сервер и проверить работоспособность там.

Что же дальше?

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

Далее следует улучшить функционал и самостоятельно добавить:

Маски ввода информации на полях формы
Валидацию правильности ввода данных (на фронте и бэке)
Сбор и сохранение информации об utm-метках, что бы знать откуда пользователь пришел на страницу
Подключить e-mail уведомления о полученной информации
Сделать отправку данных без перезагрузки всей страницы
И многое другое на что хватит фантазии

Ссылки на оригинальную серию статей:

medium.com

Пример 2

Эта процедура перечисляет все значения числовой последовательности Фибоначчи, вплоть до 1000. В этом примере приводятся следующие возможности макросов Excel:

Объявление переменных.
Цикл Do While.
Ссылки на ячейки текущего листа Excel.
Условный оператор.

‘ Подпрограмма для перечисления всех значений последовательности Fibonacci для всех значений ниже тысячи

Sub Fibonacci()

Dim i As Integer ‘ счетчик для позиции в серии значений

Dim iFib As Integer ‘ сохраняет текущее значение в серии

Dim iFib_Next As Integer ‘ сохраняет следующее значение в серии

Dim iStep As Integer ‘ хранит размер следующего шага

‘ Инициализация переменных variables i и iFib_Next

i = 1

iFib_Next = 0

‘ Цикл Do While, который исполняется до тех пор, пока номер

‘ числа в последовательности Фибоначчи меньше 1000.

Do While iFib_Next < 1000

If i = 1 Then

‘ Специальный случай для первой записи в серии

iStep = 1

iFib = 0

Else

‘ Сохраняется следующий размер шага, перед перезаписью

‘ текущей записи в серии

iStep = iFib

iFib = iFib_Next

End If

‘ Печать текущего значения последовательности Фибоначчи для столбца А

‘ текущего листа

Cells(i, 1).Value = iFib

‘ Вычисление следующего значения последовательности и увеличение

‘ маркера позиции на 1

iFib_Next = iFib + iStep

i = i + 1

Loop

End Sub

WinAutomation

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

Профессиональная версия продается за $ 485. Хотя есть более дешевая Стандартная версия, которая по-прежнему включает в себя большинство опций и инструментов в Pro версии.

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

Это также включает в себя визуальные подсказки отладчика и всплывающие подсказки, которые выделяют ошибки.

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

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

Интеграция с Google Drive

Для хранения таблиц Google Sheets предоставляется 15 Гб места . На том как крут этот сервис мы останавливаться не будем, а просто расскажем о беcценной фиче под названием ОФФЛАЙН РЕЖИМ. Он доступен для текстовых документов, таблиц и презентаций.

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

Стоит также уточнить, что использование Google Drive клиента на компьютере не позволяет работать с таблицами офлайн. Файлы Google Sheet хранятся на компьютере в виде файлов-ссылок, при открытии которых запускается браузер.

How Macros in Google Sheets Work

Google explains it this way:

“Let’s say you need to format new data imports or build the same chart across multiple sheets of quarterly data. Repeating the same steps manually can take hours, but the Sheets macro recorder lets you record those actions and play them back on command without having to write any code.”

The new “record macros” feature sits in the Tools menu. It’s very easy to use. Via

On your computer, open a spreadsheet at sheets.google.com.
At the top, click Tools > Macros > Record macro.
At the bottom, choose which type of cell reference you want your macro to use:
- Use absolute references: The macro will do tasks on the exact cell you record. For example, if you bold cell A1, the macro will only ever bold cell A1 regardless of which cell you clicked.
- Use relative references: The macro will do tasks on the cell you select and its nearby cells. For example, if you record bolding cells A1 and B1, the macro can later be used to bold cells C1 and D1.
Complete the task you want to record. When you’re done, click Save.
Name the macro, create a custom shortcut, and click Save
Note: When you create a macro in Google Sheets, an Apps Script is created. To edit this Apps Script, at the top, click Tools > Script editor.

.

To perform a macro, click Tools Macros > the macro you want.

Macros in Google Sheets + Collaboration

Easy collaboration has always been one of Google Sheet’s most important features. True to form, macros in Google Sheet are positioned as a tool for teams and collaboration. As Google says on its blog, “After nearly 40 years, it’s time for teams to approach spreadsheets cloud-first.”

Groups of people can run macros simultaneously in the same Google Sheets file without interrupting each other. As Google notes,

“The best part about Sheets Macros is that they’re built for use in cloud-based files, which means that teams can run macros at the same time that others are working in the sheet without interrupting them. For example, a finance team having a budget meeting can run macros while reviewing the same spreadsheet. It also means that coworkers or clients won’t be forced to download sensitive files to use your macros. Since your Sheets files are in the cloud, you can keep tighter controls over who can view and re-share your data.”

Macros also guarantee process consistency. On a team where everyone might process spreadsheet data slightly differently, macros make it easy for everyone to follow an identical workflow.

Editing Macros in Google Sheets With Google Apps Script

When you record macros in Google Sheets, your actions are converted into a Google Apps Script. You can edit this script if you want to update your macro. (No need to re-record from scratch.) You can also write your own Apps Script functions and import them as new macros.

Edit your macros

On your computer, open a spreadsheet at sheets.google.com.
At the top, click Tools Macros Manage macros.
Next to a macro, click More . Rename or remove the macro.
When you’re done, click Update.

Import custom functions

You can import custom functions made with Google Apps script.

On your computer, open a spreadsheet at sheets.google.com.
At the top, click Tools Macros > Import.
Next to the function you want to import, click Add function.

Further reading: Quickstart: Macros, Menus, and Custom Functions

Other Updates in Google Sheets

Macros in Google Sheets were the headline announcement for this most recent Sheets update, but several other powerful features were also rolled out:

New chart types (waterfall and 3D)
New ways to embed charts while keeping data up-to-date in Docs and Slides (even if you move files)
More functions (up to 400+ now)
The ability to add printing page breaks, custom paper sizes
New options for row and column grouping
A way to add checkboxes in cells.
A new to group your data by time frame (like week, month or year) when you create pivot tables.

Regarding pivot tables, Google notes: “Our engineering team has also been hard at work bringing the power of Google’s artificial intelligence into Sheets to help teams know what their data knows. You might have noticed that we recently added intelligent pivot tables in Sheets to help analyze and find new insights.”

Как импортировать макросы

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

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

Откройте Google Sheet с макросом, который вы хотите скопировать, а затем нажмите Инструменты & gt; Макросы & gt; Управление макросами.

Затем нажмите значок «Дополнительно» рядом с макросом, который вы хотите скопировать, и нажмите «Редактировать сценарий».

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

Выделите макрос (ы), который вы хотите скопировать, затем нажмите Ctrl + C. Обязательно скопируйте все до и включая заключительную точку с запятой.

Теперь откройте другую электронную таблицу, в которую вы будете импортировать макрос, и нажмите Инструменты & gt; Макросы & gt; Запись макроса.

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

Нажмите «Сохранить» еще раз.

Откройте скрипт Google Apps, нажав Инструменты & gt; Редактор сценариев, а затем откройте файл macros.gs на левой панели. Удалите существующую функцию, а затем нажмите Ctrl + V, чтобы вставить макрос из другого листа.

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

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

Затем нажмите Инструменты & gt; Макросы & gt; Импортировать.

Наконец, нажмите «Добавить функцию» под макросом, который вы хотите добавить.

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

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

Как использовать Google Sheets в автономном режиме

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

После того, как вы загрузите официальное расширение для Chrome, перейдите на домашнюю страницу Google Sheets и в верхнем левом углу нажмите меню Hamburger> Настройки. Переключите «Offline» в положение «On», затем нажмите «OK».

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

Код макроса на VBA Excel

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

Примечание! По умолчанию данной вкладке в Excel 2010 может и не быть, чтобы ее отобразить нажмите правой кнопкой по ленте пункт меню «Настройка ленты»

затем, в правой области поставьте галочку напротив пункта «Разработчик»

После вкладка разработчик станет отображаться на ленте.

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

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

Sub Карточка()
'Книга
NewBook = ""
' Путь, где будут храниться наши карточки
' Т.е. в той папке, откуда запустился файл с макросом
Path = ThisWorkbook.Path
' Выбираем лист с данными
Sheets("Данные").Select
' Запускаем цикл, скажем на 100000 итераций
' Начиная со второй строки, не учитывая заголовок
For i = 2 To 100000
' Выйдем из него, когда фамилии закончатся, т.е. строки
If Cells(i, 1).Value = "" Then
        i = 100000
    Exit For
End If
' Имя файла карточки, назовем по фамилии
Name_file = Path & "\" & Sheets("Данные").Cells(i, 1).Value & ".xls"
‘Выбираем лист с шаблоном
Sheets("Шаблон").Select
' Присваиваем значения нашим ячейкам, по именам которые мы задавали
    Range("fio").Value = Sheets("Данные").Cells(i, 1).Value & " " & _
        Sheets("Данные").Cells(i, 2).Value & " " & Sheets("Данные").Cells(i, 3).Value
    Range("number").Value = Sheets("Данные").Cells(i, 4).Value
    Range("addres").Value = Sheets("Данные").Cells(i, 5).Value
    Range("dolgn").Value = Sheets("Данные").Cells(i, 6).Value
    Range("phone").Value = Sheets("Данные").Cells(i, 7).Value
    Range("comment").Value = Sheets("Данные").Cells(i, 8).Value
    ' Копируем все
    Cells.Select
    Selection.Copy
    ' Создаем новую книгу или делаем ее активной 
    If NewBook = "" Then
        Workbooks.Add
        NewBook = ActiveWorkbook.Name
    Else
        Workbooks(NewBook).Activate
        Cells(1, 1).Select
    End If
    ' Вставляем данные в эту книгу
    Application.DisplayAlerts = False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ' Сохраняем с нашим новым названием
    ActiveWorkbook.SaveAs Filename:= _
    Name_file, FileFormat:=xlExcel8, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    NewBook = ActiveWorkbook.Name
    Application.DisplayAlerts = True
    ' Снова активируем файл с макросом и выбираем лист
    Workbooks("Макрос.xls").Activate
    Sheets("Данные").Select
' Переходим к следующей строке
Next i
' Закроем книгу
Workbooks(NewBook).Close
' Выведем сообщение об окончании
MsgBox ("Выполнено!")
End Sub

Теперь осталось выполнить этот макрос, для этого откройте вкладку разработчик->макросы->выполнить наш макрос:

и после выполнения у Вас в той же папке появится вот такие файлы

Вот с таким содержимым:

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

Нравится24Не нравится1

ПОДСТАВИТЬ – замена фрагментов текста

Сложность: низкая.

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

Синтаксис:

=ПОДСТАВИТЬ(текст; искать; замена; ).

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

Применение

С помощью функции ПОДСТАВИТЬ можно быстро убрать из списка фраз лишние символы. Например, вы спарсили семантику из Вордстата, и у части слов в списке есть модификатор «+». Для загрузки в Google Ads вы хотите почистить ключевые слова и загрузить их в чистом виде.

Поэтому напротив ячейки с ключевой фразой вводим формулу =ПОДСТАВИТЬ(А2; «+»; «»;).

Функция заменит все символы «+», которые найдет в ячейке, на пустой символ. Проще говоря, просто уберет все плюсы.

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

Затем копируем весь столбец и с помощью «Специальной вставки» вставляем только значения в свободный столбец. Теперь их можно загружать в Google Ads или использовать для других целей.

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

=ПОДСТАВИТЬ(; «для»; «+для»).

GOOGLETRANSLATE – переводим ключевики с русского на английский (или любой другой язык)

Сложность: низкая.

Функция GOOGLETRANSLATE – Google Переводчик внутри Google Таблиц.

Синтаксис:

GOOGLETRANSLATE(текст; ; )

Применение

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

Для этого выгружаем в Google Таблицу список ключевых слов. В соседнем столбце прописываем формулу для перевода с русского на английский:

=GOOGLETRANSLATE(A1;»ru»;»en»)

Затем выделяем столбец с переводом, копируем и вставляем в отдельный столбец как значения (Ctrl+Shift+V).

С помощью этой функции можно переводить на любые языки, поддерживаемые Google.

Запуск вашего скрипта Google

Теперь выберите значок воспроизведения вверху, чтобы просмотреть код. Поскольку вы впервые запускаете свой новый скрипт, вам необходимо предоставить приложению Google Script разрешение на доступ к вашей электронной таблице (данным) в вашей учетной записи Google. Выбрать Просмотр разрешений во всплывающем окне.

Вам нужно будет войти в свою учетную запись Google. А поскольку это настраиваемое приложение, не зарегистрированное в Google, вы увидите предупреждение о том, что приложение не проверено. Выберите продвинутый ссылку, а затем выберите Перейти к образцу скрипта Google (небезопасно) продолжать.

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

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