- Вывести ячейку значение другой ячейки
- Как вернуть другую ячейку, если одна ячейка равна тексту или другому в Excel?
- Вывести ячейку значение другой ячейки
- Найдите наибольшее значение и верните значение соседней ячейки с формулами
- Найдите и выберите самое высокое значение и верните значение соседней ячейки с помощью Kutools for Excel
- Вывод соседнего значения ячейки при условии сравнения других ( соседних ячеек)
- Изменение значения ячейки в зависимости от другой ячейки Excel
- MS, Libreoffice & Google docs
- Правила сообщества
- Макросы. 7 Способов запуска макроса. Академия Excel
- Макросы. Знакомство с макросами. Академия Excel
- VBA для создания прайс-листа с изображениями
- Нужен макрос или скрипт
- Редизайнер таблиц в Excel
- Как найти и распарсить JSON на странице сайта в интернете с помощью VBA Excel?
- Макрос получения курсов доллара за период с сайта Банка России
- VBA Excel — вывести формулы в ячейки
- Отслеживание входа пользователей в книгу Excel
- Макрос для выделения дубликатов разными цветами
- Автоматизация Excel с помощью VBA на примере графика отпусков
- Не только финансовая система может держаться на Excel
Вывести ячейку значение другой ячейки
Как вернуть другую ячейку, если одна ячейка равна тексту или другому в Excel?
Например, у меня есть следующий диапазон данных, теперь я хочу извлечь значения в столбце C, если соответствующие данные в столбце A равны смежным данным в столбце B, как показано ниже. Как я мог справиться с этой задачей в Excel?
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Вот простая формула, которая может помочь вам вернуть значение в другой ячейке, если одна ячейка равна соседней, сделайте следующее:
Введите эту формулу: = ЕСЛИ (A2 = B2; C2; «») (A2 и B2 две ячейки, которые вы хотите сравнить, и C2 — это значение ячейки, которое вы хотите вернуть) в пустую ячейку, где вы хотите отобразить результат, а затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите вернуть соответствующие значения, см. снимок экрана:
Вот еще одна ситуация, скажем, у вас есть два столбца, и вам нужно вернуть значение ячейки на основе другой ячейки, если она содержит определенный текст, например «Да», как показано на следующем снимке экрана:
Чтобы справиться с этой задачей, вам следует применить эту формулу: = ЕСЛИ (B2 = «да»; A2; «») в пустую ячейку, чтобы вывести результат, а затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите содержать эту формулу, и все значения, смежные с вашим конкретным текстом, были возвращены, как показано на следующем снимке экрана:
Если у вас есть Kutools for Excel, С его Выбрать определенные ячейки вы можете быстро выбрать строки с определенным текстовым значением, а затем вставить строки в другое место по мере необходимости.
| Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 60 дней. | ||
Найдите наибольшее значение и верните значение соседней ячейки с формулами
| Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней. | |
Вывод значения при определенном условии | работа , книга 2, 22019.xlsx (12.3 Кб, 7 просмотров) |
https://www.youtube.com/watch?v=_NEvWYp33Eg
https://www.youtube.com/watch?v=jFf3Zq8DZX0
возможно кому-то понадобиться это — сравнение 1 столбца со многими, при нахождении искомого элемента сравнить 2 поиска и элементами поиска (тоже 2) и вывести значение
Добавлено через 1 час 23 минуты
Поиск и подстановка по 2 критериям (ВПР по 2 столбцам)
Добрый вечер, какая-то странность твориться, не могу до конца доработать формулу , есть таблица, в которой нужно выводить значение «если больше номинала» и «если меньше номинала».
Благодаря функции «индекс» получается вывести необходимое число массива. но как сделать так, что бы если одно из чисел ( вспомогательное) менялось, происходило сравнение и вычитание, для определения на сколько больше или на сколько меньше число номинала? Для удобства, таблицу форматировал под — «умную».
Формулу которую использую = ЕСЛИ(G11>=ИНДЕКС(R10:R15;ПОИСКПОЗ(Q10:Q15&R10:R15;[Столбец3]&[Столбец6];0));0;ИНДЕКС(R10:R15;ПОИСКПОЗ(Q10:Q15&R10:R15;[Столбец3]&[Столбец6];0)-G12)).
Суть, нужно сравнить 2 ячейки и если они равны, то если количество больше номинального происходило вычитания фактического — номинальное; если же меньше — записывалось в другую ячейку и отнималось от фактическое — номинальное.
Подскажите, пожалуйста, как это все сделать?
Источник
Изменение значения ячейки в зависимости от другой ячейки Excel
Доброго времени суток.
Перерыл кучу информации, но так и не понял, как присвоить значение ячейке в зависимости от значения другой, при этом сделать это не возвращением, а именно присвоением, чтобы если что ячейки которым присвоят значение могли меняться.
Пример:
Есть ячейка B3, в которой на данный момент внесено значение 100
Есть ячейка B4, в которой на данный момент значение 0.
Как сделать так, что при внесении любого числа больше 0 в ячейку B4, ячейка B3 автоматически станет равна 0 и наоборот.
Т.е. эти ячейки должны остаться редактируемыми, но при этом автоматически меняющимися в зависимости от значений друг друга.
Как я понял из стандартных функций екселя, прямого влияния ячейка на другую ячейку не может оказывать, т.к. все функции именно возвращают значение в ту самую ячейку, в которой функция прописана.
UPD ответ найден в комментарии #comment_196491784
MS, Libreoffice & Google docs
456 постов 12.6K подписчика
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 2 And Target.Row = 3 And Target.Value > 0 Then
Cells(4, 2) = 0
ElseIf Target.Column = 2 And Target.Row = 4 And Target.Value > 0 Then
Cells(3, 2) = 0
End If
End Sub
конкретной задачи нет, но попробуйте функцию «ЕСЛИ»
Эксель иногда очень криво работает с представлением цифр. И формула может не сработать, если представленное число в виде цифр или текст — с проверкой другой испостаси. В этом случае очень помогает функция Текст(хх;0). В вашем случае поможет простая формула (заодно добавил проверку, если вдруг ничего не стоит, в этом случае тоже выведется 100):
Смотрите, Вашу задачу можно решить макросом. Например так: макрос идет по первому столбцу и если в ячейке значение >0 то в сопоставимую ячейку второго столбца ставится 0, дальше идет поиск по второму столбцу и если значение ячейки >0, то в сопоставимую ячейку первого столбца ставится 0. Единственно, когда макрос не сработает это когда в обеих ячейках двух столбцов стоит 0, если такой вариант не может быть, то могу накидать макрос, причем на закрытие книги с сохранением, т.е. при закрытии книги условия соответствия будут автоматически проверяться. (можете написать в личку hathory@sfletter.com).
А через формулы нельзя?
Или поменять местами 100 и 0.
Как я понял из стандартных функций екселя, прямого влияния ячейка на другую ячейку не может оказывать, т.к. все функции именно возвращают значение в ту самую ячейку, в которой функция прописана.
Макросы. 7 Способов запуска макроса. Академия Excel
Давайте научимся запускать макросы 7-ью различными способами. В процессе прохождения уроков Вы узнаете ещё минимум 2 способа, но для начала вполне достаточно семи. Как показывает практика Вы будете пользоваться не всеми, поэтому сразу присмотритесь тому, что Вам по душе.
00:12 — №1. Вкладка Разработчик — Макросы
00:35 — №2. Сочетание клавиш Alt+F8
00:47 — №3. Вкладка Вид — Макросы
00:58 — №4. Вкладка Разработчик — Эл.упр. — Кнопка
01:33 — №5. Вкладка Вставка — графический элемент
02:45 — №7. Событие — Активация листа
03:57 — Подводим итоги урока
04:20 — Анонс следующего урока
Макросы. Знакомство с макросами. Академия Excel
Как писать макросы в Excel? Что такое макросы? Как они работают? Как создавать свои макросы? вот вопросы, на которые вы получите ответ, посмотрев видео.
Это первый урок, в котором мы начинаем разбираться в теме макросы с самых основ. Вы убедитесь, что не надо уметь программировать, чтобы научиться писать макросы.
00:11 — Что такое макрос?
00:42 — Знать English нет необходимости
01:13 — Что делают эти команды?
02:32 — Способы написания макросов
03:38 — Пишем свой первый макрос
03:47 — Добавляем вкладку Разработчик
04:47 — Включаем запись
06:24 — Проверяем что записали
07:01 — Подводим итоги урока и анонс следующего
VBA для создания прайс-листа с изображениями
Доброго времени суток!
Не могу не поделиться результатами своих двухнедельных мучений (ну и похвастацца, конечно).
Макрос создаёт прайс (а может и не прайс, смотря какая у вас потребность приключится) с изображениями и их именами из выбранной папки. Высота изображений определяется пользователем на листе и конечные размеры картинок в готовом файле изменяются пропорционально, ячейки подстраиваются под их размер.
Это суть. Дальше — предыстория и вопрос.
Вообще это мой первый макрос. С Excel’ем я давно на «ты», и давно «облизывалась» на макросы, но все к случаю не приходилось. Все эти If’ы и Then’ы повергали меня в ужас. Ну серьёзно, проще формулой.
Но тут подвернулась работа, в которой моих знаний стало явно не хватать, нужен макрос. Пришлось осваивать. И вот, спустя две недели ночных свиданий с ноутом, макрос готов и все пожелания заказчика учтены.
Код, конечно, кривой, хоть и рабочий; большая часть его кусков скопипастщена с разных форумов, но связана воедино и адаптирована лично мной. Поэтому я сияю, как медный таз — «ОНО РАБОТАЕТ!», а поделиться не с кем — домашние спят — не будить же, пошлют ещё.
Ну все, похвасталась, теперь, собственно, вопрос. Пока сидела с этим макросом, суть работы VBA в общих чертах и понятиях, конечно, уловила. Но слишком сумбурно. Если кто знает хорошую литературу или ресурсы, полезные начинающим, посоветуйте, пожалуйста, буду очень благодарна!
Пы.Сы. Фотографировала на бессонницу, уж не обессудьте)
Нужен макрос или скрипт
Доброго времени суток уважаемому Сообществу.
Имеется склад. На складе — дохленький комп и сканер штрих-кода. Каждый день, на склад приезжает около 1000+ коробок, на которых наклеены этикетки с штрих-кодом.
Штрих-код выглядит так: 11540507202024780029090102.
Количество цифр — постоянно.
Сканер, считывая штрих-код, заносит его в первую ячейку таблицы в Libre Office Calc.
Далее, нужно чтобы штрих-код был разложен на значения и разнесён по ячейкам таблицы, как показано на скрине:
Порядковый номер должен ставиться автоматически.
Первая цифра в штрих-коде — № стеллажа.
Следующие три цифры — № ячейки в стеллаже.
Далее, восемь цифр — дата упаковки коробки.
Следом, одна цифра — № смены.
А вот следом, две сложности 🙂 13 цифр — это код наименования товара, находящегося в коробке. Список всех наименований с кодами, расположен на соседнем листе документа. Можно расположить на этом же листе, если так будет проще.
Выглядит список вот так:
Нужно чтобы происходил поиск по столбцу В и при нахождении совпадения, в ячейку Наименование, вставлялось название номенклатуры из столбца А.
Вторая сложность — это ячейка Количество. Нужно чтобы автоматически вёлся подсчёт количества каждой номенклатуры и результат, заносился в соответствующую ячейку.
Если нужно, можно на комп, где всё это будет работать, установить какой-нибудь MS Office.
Буду благодарен за любую помощь.
Если кто сможет самостоятельно сделать — пишите (mostgraycat@gmail.com), скину исходный файл + оплачу какую-нибудь разумную денюжку. Межбанковским переводом, ибо нахожусь в ближнем забугорье и тут на картах другая валюта.
Редизайнер таблиц в Excel
Не секрет, что большинство пользователей Excel, создавая таблицы на листах, думают в первую очередь о собственном комфорте и удобстве. Так рождаются на свет красивые, со сложными «шапками», пестрые и громоздкие таблицы, которые при этом совершенно нельзя ни отфильтровать, ни отсортировать, а про автоматический отчет сводной таблицей лучше и не думать вообще.
Рано или поздно пользователь такой таблицы приходит к мысли, что «пусть будет не так красиво, зато можно работать» и начинает упрощать дизайн своей таблицы, приводя его в соответствие с классическими рекомендациями:
— простая однострочная шапка, где у каждого столбца будет свое уникальное название (имя поля)
— одна строка — одна законченная операция (сделка, продажа, проводка, проект и т.д.)
— без объединенных ячеек
— без разрывов в виде пустых строк и столбцов
Но если сделать однострочную шапку из многоэтажной или разбить один столбец на несколько достаточно просто, то реконструирование таблицы может занять много времени (особенно при больших размерах ). Имеется ввиду следующая ситуация:
из такой таблицы
В терминах баз данных нижнюю таблицу обычно называют плоской (flat) — именно по таким таблицам лучше всего строить отчеты сводных таблиц (pivot tables) и проводить аналитику.
Преобразовать двумерную таблицу в плоскую можно при помощи простого макроса. Откройте редактор Visual Basic через вкладку Разработчик — Visual Basic (Developer — Visual Basic Editor) или сочетанием клавиш Alt+F11. Вставьте новый модуль (Insert — Module) и скопируйте туда текст этого макроса:
Dim hc As Integer, hr As Integer
Dim ns As Worksheet
hr = InputBox(«Сколько строк с подписями сверху?»)
hc = InputBox(«Сколько столбцов с подписями слева?»)
Set inpdata = Selection
Set ns = Worksheets.Add
For r = (hr + 1) To inpdata.Rows.Count
For c = (hc + 1) To inpdata.Columns.Count
ns.Cells(i, j) = inpdata.Cells(r, j)
ns.Cells(i, j + k — 1) = inpdata.Cells(k, c)
ns.Cells(i, j + k — 1) = inpdata.Cells(r, c)
После этого можно закрыть редактор VBA и вернуться в Excel. Теперь можно выделить исходную таблицу (полностью, с шапкой и первым столбцом с месяцами) и запустить наш макрос через Разработчик — Макросы (Developer — Macros) или нажав сочетание Alt+F8.
Макрос вставит в книгу новый лист и создаст на нем новый, реконструированный вариант выделенной таблицы. С такой таблицей можно работать «по полной программе», применяя весь арсенал средств Excel для обработки и анализа больших списков.
Также есть второй вариант для работы с большими таблицами
Dim inpdata As Range, realdata As Range, ns As Worksheet
Dim i&, j&, k&, c&, r&, hc&, hr&
Dim out(), dataArr, hcArr, hrArr
hr = Val(InputBox(«Сколько строк с подписями данных сверху?»))
hc = Val(InputBox(«Сколько столбцов с подписями данных слева?»))
Set inpdata = Selection
If inpdata.Rows.Count Показать полностью 2
Как найти и распарсить JSON на странице сайта в интернете с помощью VBA Excel?
В этом посте я покажу, как с помощью VBA сделать, то, для чего VBA вроде бы как изначально не предназначен – как получить значения нужных переменных из структуры JSON.
Этот пост сделан по мотивам просьбы моего первого и пока единственного подписчика, сделавшего комментарий на предыдущий пост «Макрос получения курсов доллара за период с сайта Банка России»
В чем отличие между сервисом ЦБ России и сайтом worldometers.info ? В том, что ЦБ предлагает XML сервис для автоматической загрузки информации (см. http://www.cbr.ru/development/SXML/ ) – ее неудобно смотреть через веб браузер, но удобно получать с помощью паучьих алгоритмов, а worldometers.info предлагает информацию для людей, а не для пауков.
Поэтому создаваемому на VBA паучку придется постараться, чтобы понять разметку «для людей».
Для работы паука необходимо дополнительно подключить три библиотеки:
1. Microsoft XML parser (MSXML) – тот же, что использовался для получения курсов ЦБ с сайта Банка России.
2. Библиотеку для работы с объектной моделью HTML.
3. Библиотеку для использования возможностей JavaScript из VBA.
Получаем html с сайта:
В полученном html паучку нужно найти и распарсить данные о количестве зарегистрированных случаев из формата JSON. Эти данные представлены вторым аргументом в вызове функции Highcharts.chart(chartName, chartData), которая на сайте рисует график.
В результате выполнения нижепредставленного кода в переменной strJson должна оказаться структура с данными в JSON формате.
Теперь самое интересное – как распарсить эту JSON структуру? Чистый VBA это делать не умеет. Но с JSON прекрасно работает JavaScript. А в VBA есть инструмент для использования возможностей JavaScript для пользователей MS Excel.
Мы можем в VBA получить уже распарсенную JSON переменную:
Проблема в том, что с объектом objJSON ничего нельзя сделать в рамках VBA – у него нет ни свойств, ни методов. Поэтому создаем эти методы на языке JavaScript. Нам нужно вытащить даты (xAxis) и количество (series->data):
Вот что пишем в VBA редакторе:
Загоняем данные в привычные VBA массивы:
Ну и раскатываем эти массивы по рабочему листу:
Вот, что получилось в результате на листе рабочей книги:
По этим данным легко построить график, например, такой:
Если захотите получить готовый файл Excel с рабочим кодом – дайте знать в комментариях.
Макрос получения курсов доллара за период с сайта Банка России
Excel (Эксель) прекрасен, а мир огромен. И готов предложить для анализа много разной информации из сети под названием Интернет. Часто видел, как аналитики мужеского и женского рода просто перебивают ручками данные со страниц разных сайтов для своей работы.
Иногда целесообразнее написать небольшой макрос, который будет получать данные из интернета автоматически. Для этого уже давно придуман Microsoft XML parser (MSXML).
Для примера, я и покажу, как с его помощью, получить курсы доллара за период с сайта ЦБ.
1) Организуйте столбец с датами на одной из «Sheets» экселя. У меня это столбец «A»
2) Подключите ссылку на Microsoft XML
3) Собственно пишем процедуру
Объявляем переменные и открываем окно в мир интернета:
Dim strCCY As String, strRateCCY As String, strRateSource As String
Dim xmlDoc As MSXML2.DOMDocument
Set xmlDoc = New MSXML2.DOMDocument
MSXML2 – это и есть упомянутый выше Microsoft XML parser, который нужно направить на сайт Банка России
Запрос будет неполным без указания даты, которую для каждой строчки мы возьмем из столбца, который предварительно датами заполнили.
Dim strDate As String
Do While Not Range(«a» & i) = «»
strDate = Format(Range(«a» & i), «dd\/mm\/yyyy»)
Всякое бывает с сайтами или вашим интернетом, поэтому нужно проверить результаты попытка загрузки xml файла.
If xmlDoc.Load(strRateSource & strDate) <> True Then
MsgBox «Сайт ЦБ сейчас не в духе, попробуйте обратиться к нему позже. «
Если же загрузка прошла успешно, то начинается магия xPath. Сначала получим дату, к которой на самом деле привязан курс доллара. Она не всегда совпадает с той датой, на которую вы курс запросили. И поместим дату ЦБ в столбец «B»
Range(«b» & i) = xmlDoc.selectNodes(«//ValCurs»)(0).Attributes(0).Text
«//ValCurs» – это и есть выражение XPath, которое может быть очень интересным и витиеватым, и которое позволяет добраться практически до любой точки xml файла. В вышеприведенном примере я взял дату валютирования из тега ValCurs.
А ниже выражение посложнее. С его помощью я нахожу валюту «доллар» среди множества других (у этой валюты и прошу показать мне только курс этой валюты (там есть и другая информация: буквенный и цифровой коды валюты в соответствии с ISO 4217 и/или ОКВ, номинал, описание, — но нам нужен только курс).
Далее я привожу текст с курсом к числу с учетом настроек символа разделителя разрядов.
Range(«c» & i).Value = CdblLocaleIndependent(strRateCCY)
Функцию CdblLocaleIndependent в этом посте показывать не буду, пока желающих на нее посмотреть не будет достаточно.
Закругляемся с циклом и заканчиваем работу:
MsgBox «Курсы сняты с сайта Банка России.»
VBA Excel — вывести формулы в ячейки
Была задача по переносу вычислений из экселя. Чтобы не лезть и не смотреть формулу в каждой ячейке я написал небольшой макрос, который их выводит.
Ниже выделенного диапазона на 10 строк выводятся все формулы и значения из заполненных ячеек.
Получается вот такая штука, которую гораздо проще разобрать и перенести
Sub DrawFormulas()
For Each Cell In Selection
CellFormula = Cell.Formula
If Left(CellFormula, 1) <> «=» Then CellFormula = «=» + CellFormula
If Trim(CellFormula) <> «=» Then Cell.Offset(Selection.Rows.Count + 10).Value = Cell.Address + CellFormula
Next
End Sub
Отслеживание входа пользователей в книгу Excel
Как понятно из заголовка, мы сделаем так, чтобы открытие рабочего файла на общем сетевом диске не осталось бесследным. Макрос будет фиксировать на отдельном (скрытом) листе имя пользователя, открывшего файл, а также дату-время открытия и закрытия файла.
Этап 1. Создаем «Лог»
Добавим в нашу книгу новый лист, куда будет записываться информация о всех пользователях и назовем его, например, Лог. На нем создадим простую шапку будущего журнала учета посетителей:
Этап 2. Макросы фиксации входа-выхода
Теперь добавим макросы для записи на лист Лог даты-времени и имен пользователей при открытии и закрытии книги. Для этого нужно открыть редактор Visual Basic с помощью сочетания Alt+F11 или с помощью кнопки Visual Basic на вкладке Разработчик (Developer) и найти в левом верхнем углу панель Project (если она не отображается, то включить ее можно сочетанием клавиш Ctrl+R):
Двойным щелчком откройте модуль ЭтаКнига (ThisWorkbook) и вставьте туда пару наших макросов для обработки событий открытия и закрытия книги:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
‘ищем последнюю занятую строчку в логах
‘заносим дату-время выхода из файла
If lastrow>1 Then Worksheets(«Лог»).Cells(lastrow, 3) = Now
‘сохраняемся перед выходом
Private Sub Workbook_Open()
‘ищем последнюю занятую строчку в логах
‘заносим имя пользователя и дату-время входа в файл
Worksheets(«Лог»).Cells(lastrow + 1, 1) = Environ(«USERNAME»)
Worksheets(«Лог»).Cells(lastrow + 1, 2) = Now
Попробуйте открыть-закрыть этот файл пару раз и убедитесь, что на лист Лог попадает ваше имя пользователя (логин входа в Windows) и дата-время:
Этап 3. Улучшаем надежность
Можно было бы скрыть лист Лог и на этом остановиться, но есть одно «но»: если у пользователя, который открывает нашу книгу, макросы разрешены по умолчанию либо он сам их разрешает, нажав в окне предупреждения на кнопку Включить содержимое, то все в порядке:
Но что если пользователь не разрешит выполнение макросов или они отключены у него по умолчанию? Тогда наши макросы отслеживания выполняться не будут и фиксации имени и даты не произойдет 🙁 Как же заставить пользователя разрешить использование макросов?
Чтобы обойти эту проблемку воспользуемся небольшой тактической хитростью. Добавьте в нашу книгу еще один чистый лист, назовите его Предупреждение и вставьте на него следующий текст:
Суть в том, чтобы по умолчанию скрыть в книге все листы кроме этого, а рабочие листы с данными отображать с помощью специального макроса. Если пользователь не разрешил выполнение макросов, то он увидит в книге только один лист с предупреждением. Если же макросы разрешены, то наш макрос обработки события открытия книги скроет лист с предупреждением и отобразит листы с данными. Чтобы пользователь сам не отобразил их — используем суперскрытие вместо обычного скрытия листов (параметр xlSheetVeryHidden вместо обычного False).
Чтобы реализовать все описанное, слегка изменим наши процедуры в модуле ЭтаКнига (ThisWorkbook)
Private Sub Workbook_BeforeClose(Cancel As Boolean)
‘ищем последнюю занятую строчку в логах
‘заносим дату-время выхода из файла
If lastrow > 1 Then Worksheets(«Лог»).Cells(lastrow, 3) = Now
‘скрываем все листы, кроме листа ПРЕДУПРЕЖДЕНИЕ
For Each sh In ActiveWorkbook.Worksheets
If sh.Name = «Предупреждение» Then
‘сохраняемся перед выходом
Private Sub Workbook_Open()
‘ищем последнюю занятую строчку в логах
‘заносим имя пользователя и дату-время входа в файл
Worksheets(«Лог»).Cells(lastrow + 1, 1) = Environ(«USERNAME»)
Worksheets(«Лог»).Cells(lastrow + 1, 2) = Now
‘отображаем все листы
For Each sh In ActiveWorkbook.Worksheets
‘скрываем листы ПРЕДУПРЕЖДЕНИЕ и ЛОГ
Чтобы просмотреть скрытый Лог откройте редактор VisualBasic (Alt+F11), выделите лист на панели Project и измените его видимость на панели Properties, используя свойство Visible:
Если пользователи настолько продвинутые, что знают про суперскрытые листы и могут их отобразить через редактор Visual Basic или нарушить работу наших макросов, то можно дополнительно поставить пароль на просмотр и изменение макросов. Для этого щелкните правой кнопкой мыши по имени файла в панели Project (строка VBAProject (blackbox.xls)), выберите команду VBA Project Properties и включите флажок Lock project for viewing и задайте пароль на вкладке Protection:
Теперь точно никто не уйдет безнаказанным.
Интересные поправки в макрос из комментария источника:
Вместо поиска последней занятой строки в таких случаях я обычно вставляю новую строку сразу после «шапки». Все предыдущие строки сдвигаются вниз, конечно. В результате немного упрощается код, сортировка получается обратная, «новые сверху», что позволяет быстрее просматривать последние записи, не прокручивая лист. При открытии — вставляется строка, её номер всегда будет 2. При закрытии файла в строку 2 добавляется время. Переполнение этого стека маловероятно, но и проверку такого переполнения проводить не нужно, просто отрезать строки ниже запланированного количества записей, без проверки.
Worksheets(«Реестр изменений»).Rows(«2:2»).Insert Shift:=xlDown ‘вставляем между строками 1 и 2 новую строку
Worksheets(«Реестр изменений»).Rows(«501:501»).Delete Shift:=xlUp ‘удаляем строку 501 (реестр на 500 строк)
Worksheets(«Реестр изменений»).Cells(2, 1) = Environ(«USERNAME») ‘запись в первую ячейку второй строки
Worksheets(«Реестр изменений»).Cells(2, 2) = Now ‘запись во вторую ячейку второй строки
Макрос для выделения дубликатов разными цветами
Как известно, чтобы выделить дубликаты цветом в Excel можно воспользоваться специальной опцией в «условном форматировании».
Достаточно выделить диапазон, задать цвет заливки, — и все повторяющиеся (или, наоборот, уникальные) значения будут выделены.
Но иногда требуется, чтобы различные повторяющиеся значения были выделены РАЗНЫМИ ЦВЕТАМИ.
В этом случае, без макросов не обойтись. Нажимаем сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), вставляем новый пустой модуль через меню Insert — Module и копируем туда код этого макроса:
On Error Resume Next
‘ массив цветов, используемых для заливки ячеек-дубликатов
Colors = Array(12900829, 15849925, 14408946, 14610923, 15986394, 14281213, 14277081, _
9944516, 14994616, 12040422, 12379352, 15921906, 14336204, 15261367, 14281213)
Dim coll As New Collection, dupes As New Collection, _
cols As New Collection, ra As Range, cell As Range, n&
Err.Clear: Set ra = Intersect(Selection, ActiveSheet.UsedRange)
If Err Then Exit Sub
ra.Interior.ColorIndex = xlColorIndexNone: Application.ScreenUpdating = False
For Each cell In ra.Cells ‘ запонимаем значение дубликатов в коллекции dupes
Err.Clear: If Len(Trim(cell)) Then coll.Add CStr(cell.Value), CStr(cell.Value)
If Err Then dupes.Add CStr(cell.Value), CStr(cell.Value)
For i& = 1 To dupes.Count ‘ заполняем коллекцию cols цветами для разных дубликатов
n = n Mod (UBound(Colors) + 1): cols.Add Colors(n), dupes(i): n = n + 1
For Each cell In ra.Cells ‘ окрашиваем ячейки, если для её значения назначен цвет
Теперь можно выделить любой диапазон с данными на листе и запустить макрос с помощью сочетания клавиш Alt+F8 или через кнопку Макросы (Macros) на вкладке Разработчик (Developer).
Плюсом этого макроса является то, что он не виснет при выделении всего листа (или столбца) целиком и не вылетает с ошибкой, если найдено более 55 дубликатов. Макрос взят здесь
Автоматизация Excel с помощью VBA на примере графика отпусков
(Офисной оптимизации пост (теперь уже с примерами))
В прошлом своём посте (где рассматривал, что есть VBA в Excel и зачем это может пригодиться) целых 137 человек подписалось на меня, в комментариях были призывы к каким-нибудь примерам использования VBA, да и обещал я @Tiafreed подкинуть материалов для ВКР, так что набросал за ночь простенький (в сотню строк кода без использования массивов, классов и т.д.) файлик в Excel с VBA модулем. Пост разделю условно на две части: для пользователей, кому интересно просто посмотреть как выглядит, что делает, плюс скачать, поиграться и для продвинутых пользователей, кому интересно как это работает и как настроить подобное под себя. Цель поста — показать возможности VBA (частично), предложить интересный вариант реализации достаточно распространённой задачи по расчёту отсутствия сотрудников.
Если формат поста зайдёт, то в следующий раз набросаю пример, как формировать Word документы из списка данных в Excel, используя шаблон и пользовательскую форму (и не используя ублюдскую рассылку ИМХО).
Стартуем. Что нам нужно? Чтобы мы вносили людей в таблицу, забивали им отпуск, по нажатию одной кнопки машина рассчитывала нагрузку на каждый отдел (и организацию в целом) на каждый день и выделяла это графически. Бонус — построить график из полученных данных и научить выпадающий список самому подставлять данные в него прямо с листа двумя кликами.
Итак, какой порядок. Если запуск макросов разрешен, совместимость не барахлит, молния не ударила в системник после запуска файла и удача нам благоволит, то можем начинать. Сначала вносим данные (тут важное уточнение, даты я вношу в текстовом формате для удобства работы и совместимости).
*Все персонажи вымышлены, совпадения случайны
Дальше идём на другой лист, нажимаем кнопку
После чего идут расчёты какое-то время (у меня это где-то половина секунды)
Машина рапортует нам об успешном завершении своей миссии, идём смотреть, что вышло.
(Да, дизайнер я говно (как и VBA кодер в общем-то, но не об этом)) оформить самому никогда не сложно, главное, что на каждый день для каждого человека машина посчитала отпуска (можно любое другое отсутствие), вычислила цифры, разбила по отделам и отразила это графически, идём на главную
Тут всё ещё проще, кнопка для запуска, табличка с примитивными расчётами (формула МАКС) и график на 366 дней который можно с лёгкостью оформить самому и с помощью которого отлично видны провалы и пики нагрузок. Нажатием на выпадающий список, мы выбираем отдел по которому выводятся данные. Вот и всё, просто и удобно. Набросал за пару ночных часов. Сразу предупреждаю, что я это не предлагаю, как готовый продукт (успешное бизнес-решение ваших кадровых проблем), просто накидал маленький пример и делюсь им с вами, потому ответственность за его использование и обслуживание не несу, но если есть желание доработать его в своих целях, готов подсказать и помочь. Да, если возникла ошибка, вероятнее всего, что формат даты/числа нарушен, защиту от дурака не ставил, ибо цели чисто демонстрационные, но если вдруг мой косяк (протестить нет возможности) перезалью и ссылку в комментарии кину. Вот сам файл (на свой страх и риск :D, никаких гарантий, что будет работать). https://yadi.sk/d/lsRdKL8wQ42FFw (и не забываем включить макросы)
Сразу отвечу на вопрос — нахрена на VBA можно же на формулах? Да, можно, но так динамично (легко добавлять/убирать людей/отделы), наглядно и расчёт каждого дня формулами будет очень сильно грузить проц, а так мы считаем лишь раз, когда кнопку нажимаем.
Тэкс. Теперь вторая часть, в принципе, дальше можно не читать, так, для очистки совести её пишу. Кому интересно, как это работает или как вообще выглядит VBA на практике. Всё просто, в основе лежит вот эта строка (в ней мы будем искать колонку с датой отпуска и уже в ней работать)
Сначала мы подготавливаемся, что-то где-то очищаем, что-то добавляем (всё в общем-то закомментил) и сортируем строки по отделам
Потом запускаем цикл перебора строк с сотрудниками, в этом цикле для каждого работника мы проверяем, является ли он началом нового отдела, если да, то делаем разделитель, если нет — кладём болт и идём дальше, дальше рассчитываем отпуска, каким образом? Берём дату начала и ищем её в строке с датами, находим (или не находим и крашимся, если закосячили, не стал пилить защиту от дурака), берём эту ячейку как точку начала, прибавляем количество дней отпуска, отнимаем один (ибо включительно) и это наша точка окончания, объединяем эти ячейки, окрашиваем, в этих столбцах делаем простые расчёты (+1 к каждому дню и перерасчёт процентовки). После прохода по всем персонажам просто копируем полученные цифры на главную страницу, чтобы подставлять их в график. Всё, почти.
И простейший обработчик для выпадающего списка — просто вставляем в строку из которой берёт данные график данные из нужной нам строки. Рассчитываем её как номер строки начала (у нас 22) + номер элемента выпадающего списка (нумерация идёт с нуля у listindex)
P.S. Знаю что говнокод, так что буду рад конструктивной критике, чему-то подучиться у более знающих. В свою защиту скажу, что код писался на халяву, без обработчика ошибок, заплаток, защиты от выстрелов в ноги, ограничений действий, фиксирования диапазона вводимых данных, добавления полосы загрузки и всё такое, чисто для демонстрации. Спасибо, если дочитал это до конца.
Не только финансовая система может держаться на Excel
(Офисной оптимизации пост, точнее, об одном из инструментов этой самой оптимизации)
Привет Пикабу, увидел недавно забавный, но правдивый пост про Excel и решил немного поделиться информацией об этой интересной программе. Предупреждаю, что пост будет длинным и не на ширпотреб, но постараюсь не кидаться терминологией и не вдаваться в тонкости.
Так вот, многие даже и не подозревают, сколько всего может на нём держаться, — не только российская мировая финансовая система, но и, например, целый каскад программного обеспечения в какой-нибудь организации. Сам я работал айтишником в одной организации и ввиду специфики работы всей компании, довелось разрабатывать ПО (программное обеспечение) на Excel (да, на этих страничках с табличками, где, как я думал ещё в школьные годы, проводятся всякие узкоспециализированные записи, ну кто будет оформлять документ в какой-то неудобной таблице), профи в области Excel себя не считаю, ибо есть куча белых пятен вроде финансовых функций и надстроек в которых всегда найдутся люди, которые разбираются лучше меня. Но это так, отступление.
Сами по себе формулы — очень мощный инструмент, на умении их использовать вывезло столько оптимизаторов, не владеющих программированием, что, думаю, нет еще офиса, где какой-нибудь местный Кулибин в обеденный перерыв не замутил еще какую-нибудь узкоспециализированную считалку для отдела. Но, работая с большими массивами возникает ряд проблем в использовании формул: относительность (ты получаешь не строку информации в базе данных, а динамичный результат вычисления) и оптимизация. Да, оптимизация на нескольких тысячах строк с десятком колонок и, допустим, парой связанных таблиц, это беда. Такая связка на i3 4гб оперативы просто будет повергать бедный офисный комп в ужас, заставляя его терять сознание при каждом пересчете и вылетать, если ты нарушил священный ритуал пятиминутного сохранения (знал я одного мужика, у него была такая формульная портянка, что сохранял базу он лишь два раза в день, перед обедом и уходя домой, ибо на сохранение уходило минут 20, ненавидели мы его все, ибо, уходя в отпуск, он оставлял это чудище кому-то из нас). Специфика работы была такова, что интернета у нас не было, а стороннее ПО нельзя — пользуйтесь чем дали. Окей, но автоматика же нужна, без неё никуда, потому пришлось использовать то, что есть и открывается это:
Разработчикам показалось мало создать мощный инструмент формул, они решили впаять в и так могущественное ПО целый язык программирования (точнее его диалект) VBA (Visual Basic for Application), возможность подключать модули с других языков, использовать API (хоть и работает это ИМХО через жопу) и встроенную среду разработки (а это означает, что вообще ничего не надо качать, если у Вас есть офис, значит все что нужно, чтобы стать мамкиным программистом уже есть). Но что нам это даёт? Огромные (ну это как посмотреть) возможности для разработки ПО, преимущественно узкоспециализированного, без использования чего-либо кроме Excel; базы данных? О чём речь, Excel — это и есть БД (то ещё извращение, но для утонченных можно связать с Access или Sql), возможность проводить расчёты (циклы, тонкие переборы, фильтрация) над большими массивами информации в кустарных условиях, использовать встроенные библиотеки для работы с другими приложениями (самое важное — MS Word), возможность наконец применить Visual Basic, который ты учил лет 20 назад, а он нигде так и не пригодился, ну и самое главное, научиться основам программирования, если ты что-то шаришь, но твои лучшие успехи — верстка шаблонов сайтов на HTML, CSS с вкраплениями PHP. Также это нам даёт возможность кодить на ведре (прям совсем ведре-ведре). VB хоть и относится к ООП, но де-факто работа в нём редко сводится к пользовательским классам, всяким тонкостям и т.д., в основном он ощущается как скриптовый язык, работа приходит к чему:
накидать разных элементов управления (это если у нас пользовательская форма, если просто код, то вообще просто запихать всё в модуль) и привязать к ним обработчики событий. Вкурил в циклы, условные конструкции, чутка простого синтаксиса и на рабочий телефон уже звонят из Майкрософт и предлагают тебе перейти к ним (Нет). Но не буду вдаваться в разработку ведь не об этом пост, что из этого можно получить на практике? Рабочие программы с базами данных и пользовательским интерфейсом (чтобы упростить и ограничить взаимодействие пользователя с данными) или скрипты для обработки целой тонны упорядоченной (а может и нет, слава условным конструкциям) информации. Например (набросал за минуту, палками не бить):
Немного возни и у нас есть пользовательская форма со списком сотрудников и полями, которые надо заполнить, на выходе по нажатию кнопки мы получаем готовую вордовскую справку. Для этого нужен лист с данными сотрудников, лист список справок и шаблон MS Word. Готово, вы бог офисной оптимизации. И так можно многое: отчёты, справки, товарные чеки, письма, документы разные, приказы целые, любые действия с информацией.
Сразу предупрежу всех, кто уже нацелился писать «на кой хрен ты раскопал этот старый кусок говна на заре 30го десятилетия 21го века». Пост чисто информативный, это не гайд, не самоучитель, может кому интересно, на прорыв в IT сфере ни разу не претендует, это раз, есть в нашей стране места, где развитие этой сферы отстаёт как раз на эти 20 лет, это два, ну и просто, может кому понадобится, может кто-то захочет на работе чему-то подучиться.
Какие минусы? Оптимизация всё равно сосёт бибу (но не такую, как формулы), безопасность тоже, чисто теоретически можно использовать криптографическое шифрование БД и расшифрование в ходе работы, но я не проверял, можно ли легко вскрыть защиту самого VBA проекта, да и оптимизация пососёт ещё большую бибу (да и вообще, кому это надо, ребят, это же Excel), ну а стандартную Excel защиту листов можно вскрыть обычным архивом и блокнотом. Также, недостатком я считаю ряд ограничений среды, по типу того, что без API не работает прокрутка колёсиком мыши, стабильность — excel иногда любит чудить. Совместимость — отдельные танцы с бубном для x64 и x32 (но это если используете сторонние API, модули). Ну и объяснять людям, как разрешить запуск макросов :D.
Спасибо, если дочитал этот длинный (и наверное скучный) пост до конца, если вдруг кому стало интересно могу написать ещё много чего, например, как написать сапёр на Excel, как сделать различные простенькие, но очень нужные офисные программки, как научиться этому (но.. зачем?), как использовать макрорекордер и много чего ещё, связанного с Excel. А ведь это всё ещё цветочки, есть люди, которые целые стратегические пошаговые игры в ячейках писали на том же VBA.
Источник
