MS, Libreoffice & Google docs

Доброго времени суток!

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

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

Вот ссылка на готовый файл
https://drive.google.com/drive/folders/1bBR90GRjQpl7eSqYHks3...

Это суть. Дальше - предыстория и вопрос.

Вообще это мой первый макрос. С Excel'ем я давно на "ты", и давно "облизывалась" на макросы, но все к случаю не приходилось. Все эти If'ы и Then'ы повергали меня в ужас. Ну серьёзно, проще формулой...

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

Код, конечно, кривой, хоть и рабочий; большая часть его кусков скопипастщена с разных форумов, но связана воедино и адаптирована лично мной. Поэтому я сияю, как медный таз - "ОНО РАБОТАЕТ!", а поделиться не с кем - домашние спят - не будить же, пошлют ещё...

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

Пы.Сы. Фотографировала на бессонницу, уж не обессудьте)

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

Итак, пустой рабочий лист:

Первое с чего начнём, это внесём статичное значение 8 в ячейку A1. Просто выбираем её и, нажав на клавишу 8 на клавиатуре вписываем это значение в ячейку:

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

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

Когда Excel видит знак равенства, то понимает, что прописанное далее выражение нужно вычислить. Поэтому, когда мы подтверждаем ввод, по итогу видим всё тоже значение восемь, хоть и помним, что на самом деле внутри ячейки вписана вычисляемая формула:

Это первый пункт, который нужно понять. Ячейки в Excel – это своего рода контейнеры, в которые помещаются либо формулы, либо статичные значения. По итогу, после «просчёта» всех контейнеров, на поверхности ячейки Excel показывает нам либо внесённое статичное значение, либо значение, вычисленное формулой. Формула же при этом всё также остаётся внутри «контейнера».

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

Во время расчёта Excel подставит на место выделенных цветом ссылок итоговые отображаемые значения из соответствующих ячеек выше. В итоге расчёта получаем следующие значения:

Конечно же, ссылки и статичные значения можно комбинировать:

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

И нам остаётся рассмотреть лишь последний уровень, своеобразную линзу – уровень форматирования.

Выберем ячейки из первого столбца и поменяем их формат на денежный (Главная -> Число -> Денежный):

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

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

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

В этом можно кстати еще раз убедиться вот так: скопировав область значений, попробуем вставить их ниже. Щелчок ПКМ по А4 выводит следующее контекстное окно:

1 – Вставка в качестве значений: форматирование теряется, и все значения вставляются в статичном виде (то есть формулы заменяются результатами вычисления этих формул):

2 – Вставка функций: форматирование теряется, формулы вставляются формулами (следим за сдвигом ссылок! Поможет здесь F4), а статичные значения – всё также статичными значениями:

3 – Копирование форматирования: копируется лишь эта итоговая «линза» форматирования (здесь значения оставлены с предыдущей вставки):

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

В видео ниже я рассказал об описанном выше принципе работы ячеек в виде 3D-визуализации, а также перечислил и показал все самые важные особенности и фишки профессиональной работы с формулами и функциями в Excel, так что предлагаю также посмотреть его:

Приветствую зашедшего!
Нужна помощь в написании макроса в VBA Excel для выполнения следующей задачи.
Есть Лист1, в котором заполнены 3 столбца. В столбце1 отображаются нули или единицы. Нужно, чтобы при выполнении макроса, значения из столбца3 напротив единиц копировались в первую пустую ячейку столбца1 на Листе2. То есть, чтобы на выходе я получил в столбце1 Листа2 перечень без пропущенных ячеек всех значений столбца3 Листа1, напротив которых в столбце1 Листа1 стоят единицы.
Понимаю, что заслуживаю быть посланным т.к задача по службе. Денег мне за решение не заплатят, но жизнь себе облегчу сильно.
Работа с IT никак не связана, потому в VBA практически ноль, хотя немного разбираюсь в Excel.
Наверняка, это тривиальная задача и знатоки поржут над нубом. Могу сказать, что смех продлевает жизнь, а автоматизация и взаимопомощь ее облегчают.
Буду благодарен за любую помощь, в т.ч. за ссылки на похожие примеры т.к. из-за низкого уровня знаний, возможно, я даже поисковый запрос формулирую неправильно и из-за этого не нахожу искомое.
Ходил на форумы, но что-то мне подсказывает, что ответов я там не дождусь т.к. из-за простоты задачи, статьям и топикам на эти темы уже по 10 лет и авторы и помнить о них уже забыли.
Прошу не топить, а для минусов оставлю сразу 4 комментария.
Заранее спасибо!

Приветствую Вас, читатели Пикабу!

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

К делу.

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

Недавно в разговоре с моим тренером узнала, что ей нужна для учета оплат и посещений табличка в excel, но у неё самой что-то не получается это сделать. Мне данная задача показалась интересной (она такой и была), и я приступила к её выполнению.

Сначала подумала над структурой.

Имеем:

- список клиентов (есть постоянные и разовые);

- несколько групп по разным направлениям;

- 4 типа абонементов (групповые, разовые групповые, индивидуальные), с параметрами по срокам действия и количеству занятий;

- скидки;

Что необходимо было реализовать:

- Учет клиентской базы;

- Учет оплат;

- Табель учета посещений + чтобы оплаченный период в нем подсвечивался цветом;

Процесс реализации (излагаю то, что помню).

1. Для учета клиентов и последующего вызова информации по ним в различных таблицах и формулах решено завести им УИН (уникальные идентификационные номера). Это облегчит поиск. Это было начало таблицы «Клиенты».

2. «Для подтягивания» информации по группам, типам абонементов, был создал лист «Справочники». В таблице «Группы» так же проставляются данные по дням недели, в которые проводятся занятия у группы. Это необходимо для форматирования внешнего вида табеля. Так же тут применена функция «Проверка данных» (чтобы в ячейку можно было внести только значения из списка).

3. Лист «Учет оплаты». Здесь в первой графе в ячейке выбирается УИН (через проверку данных), в ФИО через функцию ВПР подтягиваются данные ФИО. Дата проставляется вручную. Тип абонемента выбирается из списка (проверка данных) и с помощью ВПР подтягивает данные о количестве занятий и сроке действия абонемента в соответствующие ячейки.

4. На основе таблицы на листе «Учет оплаты» сделана сводная таблица (лист «Свод_оплата»)по оплате, с группировкой по месяцам. При желании таблицы и отчет можно дополнить типом оплаты – наличные либо безналичный перевод.

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

Тут хотела бы остановиться чуть подробнее.

Для подсвечивания актуального оплаченного периода необходимо выбирать последнюю оплату (максимальную дату) из таблицы по учету оплат. Я перерыла свою настольную книгу «Excel 2016 Библия пользователя». Потом я перерывала интернет. Находила аналогичные задачи, которые решали через формулы, при адаптации которых в моей книге ничего не получалось. Так я корпела дня два, в процессе которых нашла формулу для построения горизонтальной диаграммы Ганта через условное форматирование.

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

Попробовала реализовать – ПОЛУЧИЛОСЬ!!!!! Я запрыгала и затанцевала!

Я была счастлива!

Простое и элегантное решение было найдено!

Оставалось только его из сводной таблицы отправлять в табель. Тут в течение дня и докопалась до ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. В книге «Библия пользователя» данная функция описана весьма скудно, и благо, что интернет всегда под рукой и там полно умных людей, кто знает excel гораздо лучше меня.

После этого дело оставалось за малым – оформить книгу под N-е количество групп и сделать условное форматирование.

В итоге получился вот такой табель.

С чем еще предстоит разобраться.

Даты в табель выводятся вообще все подряд. Пришлось их группировать по 10 дней.

Хочу разобраться как на основании заданных даты 1го дня занятий в году и дней недели, по которым проходят занятия, создавать последовательность дат для табеля, чтобы эта «колбаса» уменьшилась в 2-3 раза. Если кто-то знает, как это можно сделать – напишите, пожалуйста, в комментариях. Буду очень благодарна!!!!

Ссылка на скачивание описанного в посте файла

P.S. Есть платные программы для ведения учета в спорте, но они заточены под фитнес-клубы (несколько тренеров, видов оплат, разные там онлайн кассы и личные кабинеты клиентов – это всё лишнее в данной ситуации).

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

Записано по тексту может быть и "м.кв.",  "кв.м.", и "м2" (аналогично для кубов). Часть этих двоек и троек в "м2" и "м3" нормально степенью в верхнем регистре, а часть просто текстом. Не очень красиво. Хорошо хоть "м^2" и "м^3" нет.

И если текстовые разнообразия написания можно легко привести к единому виду через Ctrl+H, то вручную менять "2" и "3" на надстрочный знак я подзадолбался через пару часов работы. Хоть мышкой, хоть горячими клавишами Ctrl+Shift++ нудно.

Думал как-то можно через "Найти и заменить", но нет. Для абзацев есть спецсимволы, для степеней нет. Автозамена отпадает.

Полез искать в интернете. Сначала ничего толкового не нашлось - просто несколько способов как поставить знак степени в Word.

Потом-таки я более корректно сформулировал запрос и нашел рабочий макрос.

Все взято с WordExpert

Создайте для этого макроса кнопку на панели инструментов или назначьте сочетание клавиш:

Sub M2a()
Dim rng As Range
Set rng = ActiveDocument.Range
With rng.Find
.MatchWildcards = True
.Text = "[А-яЁё][1-9]{1}"
.MatchCase = False
While .Execute
rng.Characters.Last.Font.Superscript = True
rng.Collapse Direction:=wdCollapseEnd
Wend
End With
End Sub

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

Работает только для русских букв А-я. Латиницу a-z и украинские і, ї, є и прочее нестандартное не меняет!

Сделал свою версию макроса, которая меняет на степень только для буквы "М" и только если после буквы "м"  стоит "2" или "3".

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

Макрос:

Sub ZnakStepeniTolkoM2M3()
Dim rng As Range
Set rng = ActiveDocument.Range
With rng.Find
.MatchWildcards = True
.Text = "[Мм][2-3]{1}"
.MatchCase = False
While .Execute
rng.Characters.Last.Font.Superscript = True
rng.Collapse Direction:=wdCollapseEnd
Wend
End With
End Sub

Может кому-то пригодится.

UPD. Дополнение #comment_190437019