Сообщество - MS, Libreoffice & Google docs
Добавить пост

MS, Libreoffice & Google docs

471 пост 12 814 подписчиков
9

Вопроса пост к программистам, датасаянтистам

Товарищи, прошу посильной помощи, подсказки профи и умельцев во имя научения.


Добрый день.

"Напоролся" на ситуацию впервые, но, как я понимаю, для специалистов по BigData - вполне частая ситуация.

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

Power BI не умеет работать с ftp, где расположены данные, потому пришлось искать варианты решения. Остановился на написании bat-файла, который загружает обновлённые данные c ftp на жёсткий и может обновлять: (код bat-файла, если вдруг интересно)


@Echo off

set downloadfolder="fcs_regions/Permskij_kraj/contracts/"

IF not exist %downloadfolder% (mkdir %downloadfolder%)

"C:\Program Files (x86)\WinSCP\WinSCP.com" ^

/log="D:\Code\M + DAX\ftp.zakupki.gov.ru\scp_sync_log.txt" /ini=nul ^

/command ^

"open ftp://free:free@ftp.zakupki.gov.ru/" ^

"synchronize -criteria=size -transfer=binary local ""d:\Code\M + DAX\ftp.zakupki.gov.ru\%downloadfolder%"" %downloadfolder%" ^

"close" ^

"exit"

set WINSCP_RESULT=%ERRORLEVEL%

if %WINSCP_RESULT% equ 0 (

echo Success

) else (

echo Error

)

exit /b %WINSCP_RESULT%


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

В общем-то и всё. Начинаем грузить информацию. К слову говоря, делаем это на сервере, где выделяется 36 Гб оперативки и достаточная производительная мощность. Однако, в процессе загрузки (а это только часть данных) всё крашится с неведомыми ошибками. Я полагаю, что просто ресурсов не хватает. Шутка ли, загрузить в кеш 20 Гб данных в виде архивов, каждый из которых надо распаковать и всё это ещё и объединить и прочее.

Собственно, теперь вопрос. Как построить работу с таким объёмом данных и умудриться это всё обработать?


let

Источник = Folder.Files(#"Путь к папке"),

#"Полный путь к файлу" = Table.AddColumn(Источник, "Полный путь к файлу", each [Folder Path]&[Name]),

#"Другие удаленные столбцы" = Table.SelectColumns(#"Полный путь к файлу",{"Полный путь к файлу"}),

#"Измененный тип" = Table.TransformColumnTypes(#"Другие удаленные столбцы",{{"Полный путь к файлу", type text}}),

#"Контент фалйа" = Table.AddColumn(#"Измененный тип", "Контент файла", each File.Contents ([Полный путь к файлу])),

#"Вызвана настраиваемая функция" = Table.AddColumn(#"Контент фалйа", "fx_UnzipContents", each fx_UnzipContents([Контент файла])),

#"Проверка целостности" = Table.AddColumn(#"Вызвана настраиваемая функция","IsEmpty", each Table.IsEmpty([fx_UnzipContents])),

#"Строки с примененным фильтром" = Table.SelectRows(#"Проверка целостности", each ([IsEmpty] = false)),

#"Развернутый элемент fx_UnzipContents" = Table.ExpandTableColumn(#"Строки с примененным фильтром", "fx_UnzipContents", {"FileName", "Content"}, {"FileName", "Content"}),

#"Исключение не xml" = Table.SelectRows(#"Развернутый элемент fx_UnzipContents", each (Text.End([FileName],4) = ".xml")),

#"Добавлен пользовательский объект" = Table.AddColumn(#"Исключение не xml", "Содержание XML", each Xml.Tables([Content])),

#"Развернутый элемент Содержание XML" = Table.ExpandTableColumn(#"Добавлен пользовательский объект", "Содержание XML", {"Name", "Table"}, {"Name", "Table"}),

#"Развернутый элемент Table" = Table.ExpandTableColumn(#"Развернутый элемент Содержание XML", "Table", {"http://zakupki.gov.ru/oos/types/1", "Attribute:schemeVersion"}, {"http://zakupki.gov.ru/oos/types/1", "Attribute:schemeVersion"}),

#"Другие удаленные столбцы1" = Table.SelectColumns(#"Развернутый элемент Table",{"http://zakupki.gov.ru/oos/types/1"}),

#"Развернутый элемент http://zakupki.gov.ru/oos/types/1" = Table.ExpandTableColumn(#"Другие удаленные столбцы1", "http://zakupki.gov.ru/oos/types/1", {"id", "regNum", "number", "publishDate", "signDate", "versionNumber", "foundation", "customer", "protocolDate", "documentBase", "price", "currency", "singleCustomerReason", "executionDate", "finances", "products", "suppliers", "href", "printForm", "scanDocuments", "currentContractStage"}, {"id", "regNum", "number", "publishDate", "signDate", "versionNumber", "foundation", "customer", "protocolDate", "documentBase", "price", "currency", "singleCustomerReason", "executionDate", "finances", "products", "suppliers", "href", "printForm", "scanDocuments", "currentContractStage"}),

#"Развернутый элемент customer" = Table.ExpandTableColumn(#"Развернутый элемент http://zakupki.gov.ru/oos/types/1", "customer", {"regNum", "fullName", "inn", "kpp"}, {"customer.regNum", "customer.fullName", "customer.inn", "customer.kpp"}),

#"Развернутый элемент currency" = Table.ExpandTableColumn(#"Развернутый элемент customer", "currency", {"code", "name"}, {"currency.code", "currency.name"}),

#"Развернутый элемент executionDate" = Table.ExpandTableColumn(#"Развернутый элемент currency", "executionDate", {"month", "year"}, {"executionDate.month", "executionDate.year"}),

#"Развернутый элемент singleCustomerReason" = Table.ExpandTableColumn(#"Развернутый элемент executionDate", "singleCustomerReason", {"id", "name"}, {"singleCustomerReason.id", "singleCustomerReason.name"}),

#"Удаленные столбцы" = Table.RemoveColumns(#"Развернутый элемент singleCustomerReason",{"printForm", "scanDocuments"}),

#"Развернутый элемент foundation" = Table.ExpandTableColumn(#"Удаленные столбцы", "foundation", {"oosOrder"}, {"foundation.oosOrder"}),

#"Развернутый элемент foundation.oosOrder" = Table.ExpandTableColumn(#"Развернутый элемент foundation", "foundation.oosOrder", {"notificationNumber", "lotNumber", "placing"}, {"foundation.oosOrder.notificationNumber", "foundation.oosOrder.lotNumber", "foundation.oosOrder.placing"}),

#"Развернутый элемент suppliers" = Table.ExpandTableColumn(#"Развернутый элемент foundation.oosOrder", "suppliers", {"supplier"}, {"suppliers.supplier"}),

#"Развернутый элемент suppliers.supplier" = Table.ExpandTableColumn(#"Развернутый элемент suppliers", "suppliers.supplier", {"participantType", "inn", "kpp", "organizationName", "country", "factualAddress", "postAddress", "contactPhone"}, {"suppliers.supplier.participantType", "suppliers.supplier.inn", "suppliers.supplier.kpp", "suppliers.supplier.organizationName", "suppliers.supplier.country", "suppliers.supplier.factualAddress", "suppliers.supplier.postAddress", "suppliers.supplier.contactPhone"}),

#"Развернутый элемент suppliers.supplier.country" = Table.ExpandTableColumn(#"Развернутый элемент suppliers.supplier", "suppliers.supplier.country", {"countryCode", "countryFullName"}, {"suppliers.supplier.country.countryCode", "suppliers.supplier.country.countryFullName"}),

#"Развернутый элемент finances" = Table.ExpandTableColumn(#"Развернутый элемент suppliers.supplier.country", "finances", {"financeSource", "extrabudget", "extrabudgetary"}, {"finances.financeSource", "finances.extrabudget", "finances.extrabudgetary"}),

#"Развернутый элемент finances.extrabudget" = Table.ExpandTableColumn(#"Развернутый элемент finances", "finances.extrabudget", {"code", "name"}, {"finances.extrabudget.code", "finances.extrabudget.name"}),

#"Развернутый элемент finances.extrabudgetary" = Table.ExpandTableColumn(#"Развернутый элемент finances.extrabudget", "finances.extrabudgetary", {"month", "year", "substageMonth", "substageYear", "KOSGU", "price"}, {"finances.extrabudgetary.month", "finances.extrabudgetary.year", "finances.extrabudgetary.substageMonth", "finances.extrabudgetary.substageYear", "finances.extrabudgetary.KOSGU", "finances.extrabudgetary.price"}),

#"Развернутый элемент products" = Table.ExpandTableColumn(#"Развернутый элемент finances.extrabudgetary", "products", {"product"}, {"products.product"}),

#"Развернутый элемент products.product" = Table.ExpandTableColumn(#"Развернутый элемент products", "products.product", {"sid", "OKPD", "name", "OKEI", "price", "quantity", "sum"}, {"products.product.sid", "products.product.OKPD", "products.product.name", "products.product.OKEI", "products.product.price", "products.product.quantity", "products.product.sum"}),

#"Развернутый элемент products.product.OKPD" = Table.ExpandTableColumn(#"Развернутый элемент products.product", "products.product.OKPD", {"code", "name"}, {"products.product.OKPD.code", "products.product.OKPD.name"}),

#"Развернутый элемент products.product.OKEI" = Table.ExpandTableColumn(#"Развернутый элемент products.product.OKPD", "products.product.OKEI", {"code", "nationalCode"}, {"products.product.OKEI.code", "products.product.OKEI.nationalCode"})

in

#"Развернутый элемент products.product.OKEI"


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

Показать полностью
296

Ответ на пост «Как из Полного Имени Сделать Фамилию и Инициалы в Excel формулой» 

Видео из оригинального поста довольно шакальное и с весьма устаревшей информацией  и довольно сложный способ. Обратите внимание на стандартную функцию "Мгновенное заполнение". по этой теме уже был пост от @Veseliy.4elМгновенное заполнение в Excel



а также ютуб вариант, автор - Николай Павлов

23

Как из Полного Имени Сделать Фамилию и Инициалы в Excel формулой

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


Формула:

=ПСТР(ФИО;1;ПОИСК(" ";ФИО;1)+1-2)& " " &ПСТР(ФИО;ПОИСК(" ";ФИО;1)+1;1)& "." & ПСТР(ФИО;ПОИСК(" ";ФИО;ПОИСК(" ";ФИО;1)+1)+1;1)&"."

965

Как удалить фон картинки в Excel / Word / PowerPoint – Просто!

Если вдруг нужно вырезать фон картинки, а под рукой нет Photoshop-а – не беда! Ведь эту задачу можно легко решить и непосредственно в Excel, Word-е, или же в PowerPoint-е.


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

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

Вырежем ей фон в Excel, и укажем вырезанным символом руки на показатель выручки в Берлине за июль вот в этом небольшом отчете:

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

Итак, переходим по пути команд: ВСТАВКА -> Рисунки -> Это устройство

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

Выбираем на компьютере нужную картинку и вставляем её на рабочий лист:

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

Вот и наша картинка:

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

Теперь выбираем её и во вкладке «Формат рисунка» щелкаем по кнопке «Удалить фон»:

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

Excel сразу пытается угадать, что нужно вырезать, и помечает эти части фиолетовым цветом. Так что теперь просто с помощью карандаша добавления (А) добавляем требуемые области в выбор, а с помощью карандаша удаления (Б) удаляем ненужное:

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

Оставив лишь требуемое, нажимаем на «Сохранить изменения» и получаем желаемый результат:

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

Ну и размещаем, как хотели, вырезанный указатель:

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

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


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

Показать полностью 9 1
265

Консолидация Данных в Excel Уроки Excel

В этом видео показана консолидация данных в эксель.

285

Опять прописал всё ЗАГЛАВНЫМИ БУКВАМИ? Не надо исправлять вручную!

Абсолютно точно уверен, Вам это знакомо: печатаешь текст, поднимаешь глаза на экран и понимаешь, что всё прописано заглавными буквами. Всё стираешь, перепечатываешь текст заново… Больше так не делайте!


В этом посте хочу поделиться очень простой, крайне полезной, и далеко не всем известной горячей клавишей для Word и PowerPoint – Shift+F3.


В описанной ранее ситуации просто выберите текст зажмите шифт и затем нажмите F3:

Опять прописал всё ЗАГЛАВНЫМИ БУКВАМИ? Не надо исправлять вручную! Microsoft office, Microsoft Word, Горячие клавиши, Гифка, Видео, Длиннопост

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


При циклированном применении этой комбинации клавиш текст будет постоянно менять регистр в трёх вариантах - все прописные, все строчные, все прописные первые буквы:

Опять прописал всё ЗАГЛАВНЫМИ БУКВАМИ? Не надо исправлять вручную! Microsoft office, Microsoft Word, Горячие клавиши, Гифка, Видео, Длиннопост

Вот в этом видео я рассказал об этой горячей клавише более подробно. Кроме того, здесь представлен аналог подобного функционала в Excel (ведь там эта комбинация клавиш не работает!). Так что предлагаю посмотреть это видео тоже:

Показать полностью 1 1
5502

Забудьте об объединении ячеек, ведь намного лучше поступать вот так!

Точно уверен, что Вам часто приходилось понервничать с объединёнными ячейками!


Возьмём в качестве примера вот такую таблицу:

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

Выглядит, вроде, неплохо, вот только большим, на первый взгляд невидимым, минусом является то, что ячейки в диапазонах B2:E2 и B8:E8 объединены!

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

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

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

Наверняка Вы уже и сами пользовались подобным объединением ячеек – и я Вам хочу дать совет – постарайтесь больше этот инструмент не использовать, так как он может быть источником самых разных проблем - начиная от проблем с шорткатами, вплоть до сложностей при написании кода в VBA.


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


Для этого сперва отменяю объединение ячеек:

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

После этого выбираю все требуемые ячейки (я выбрал ячейки сразу как в строке 2, так и 8):

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

Теперь щелкаем по вот этому символу:

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

И в открывшемся окне в пункте «по горизонтали» выбираем настройку «по центру выделения».

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

Нажимаем на ОК и всё готово (на картинке ниже показано, что объединение ячеек не использовано)!

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

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

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

Всё описанное я также наглядно показал вот в этом видео, советую его посмотреть:

Показать полностью 9 1
85

Макросы VBA для Excel: выделение значений цветом (PERSONAL.xlsb, переменные, цикл For Each, всплывающие окна, заливка ячеек цветом)

Всем привет!


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


Сразу договоримся вот о чем: у меня стоит Office 2016 на домашнем ноуте и 2019 на рабочем компьютере. Они почти не отличается интерфейсом. Я прошу разрешить мне не расписывать, где что нажимать в других версиях, чтобы не раздувать посты еще сильнее. Если у вас что-то не будет получаться, просто спросите в комментариях, и я вам помогу.


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


Но сначала кое-что важное:


Материалы данного поста созданы непрофессиональным программистом.

Я не претендую на гордое звание преподавателя, коуча или сенсея.

Я буду показывать решения, которые просто будут работать.

Критика и советы горячо приветствуются.

При, по крайней мере, написании поста ни один настоящий программист не пострадал.


А теперь поехали!


1. Что такое VBA? Зачем писать на нем макросы и что нужно, чтобы они работали?

Об этом писал в первом посте. В вашего позволения, повторяться не буду.


2. Мне прислали книгу в Excel, где там макросы?

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

Если вы открыли Excel, а вкладки нет, ее нужно включить. Для этого нажмите A, C, вверх, B, вверх, B, A, вниз откройте настройки («Параметры» в самом низу, если нажать «Файл» сверху слева). Слева в списке выберите «Настроить ленту». У вас будет два списка с набором команд и вкладок. В правом найдите строчку «Разработчик» и включите напротив нее галочку, вот так:

Макросы VBA для Excel: выделение значений цветом (PERSONAL.xlsb, переменные, цикл For Each, всплывающие окна, заливка ячеек цветом) Макрос, Vba, Microsoft Excel, Урок, Длиннопост

Кстати, неплохо бы еще прогуляться в «Центр управления безопасностью», нажать кнопку «Параметры центра управления безопасностью…», затем выбрать «Настройки макросов» и выбрать подходящий вам пункт. У меня лично так:

Макросы VBA для Excel: выделение значений цветом (PERSONAL.xlsb, переменные, цикл For Each, всплывающие окна, заливка ячеек цветом) Макрос, Vba, Microsoft Excel, Урок, Длиннопост

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


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

Макросы VBA для Excel: выделение значений цветом (PERSONAL.xlsb, переменные, цикл For Each, всплывающие окна, заливка ячеек цветом) Макрос, Vba, Microsoft Excel, Урок, Длиннопост

Кнопка с подписью «Visual Basic» откроет редактор проектов VBA, который почти такой же, как в прошлом посте про AutoCAD.


Через кнопку «Макросы» можно увидеть и запустить макросы, которые сейчас доступны и загружены в Excel.


Еще есть кнопка поменьше, «Запись макроса». Нажав на нее, можно сделать макрос без программирования. Можете попробовать сами, я объяснять подробно не буду, потому что лично мне кажется, что написанные таким образом макросы подходят только для имитации бурной деятельности разовых и крошечных задач. Нам эта кнопка пригодится чуть ниже для кое-чего другого.


3. Как написать свой макрос для Excel?

В редакторе проектов VBA вы увидите объекты листов, книги, а также можете добавлять свои модули и формы.

Опять-таки, пока я советую добавлять новые модули, а не писать их в книгах или листах. Со временем нам попадется ситуация, когда это будет необходимо. Если вообще будет.


В общем, добавляем модуль, чтобы потом писать туда код:

Макросы VBA для Excel: выделение значений цветом (PERSONAL.xlsb, переменные, цикл For Each, всплывающие окна, заливка ячеек цветом) Макрос, Vba, Microsoft Excel, Урок, Длиннопост

Наверное, вы уже заметили, что в отличие от AutoCAD, в Excel не нужно подгружать макросы в виде отдельных файлов .dvb, они сохраняются вместе с книгами. Это отчасти удобно, но есть и минус: чтобы запустить какой-то макрос, надо держать открытой книгу, в которой он сохранен. Или копировать его каждый раз в каждую новую книгу.


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

У вас ее, скорее всего нет.

Чтобы она появилась, нужно три раза позвать Битлджуса сделать макрос через «Запись макроса». Нажимаете эту кнопку, потом обязательно в списке «Сохранить в» выбираете «Личная книга макросов», что-нибудь делаете (например, выделяете ячейку, пишете в нее что-нибдуь) и нажимаете «Остановить запись». Потом нажимаете кнопку «Остановить запись», она будет на месте кнопки «Запись макроса».


Если вы теперь откроете редактор VBA (нажав кнопку «Visual Basic»), то в списке проектов увидите книгу PERSONAL.xlsb, а в ней модуль с кодом того, что вы делали, пока записывали макрос. Советую удалить его, но я вам не командир.


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


Давайте прикинем задачу.

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

Макросы VBA для Excel: выделение значений цветом (PERSONAL.xlsb, переменные, цикл For Each, всплывающие окна, заливка ячеек цветом) Макрос, Vba, Microsoft Excel, Урок, Длиннопост

Пока давайте заранее решим, что максимальная снаряженная масса котиков – 5 кг. Тогда код будет такой:

Макросы VBA для Excel: выделение значений цветом (PERSONAL.xlsb, переменные, цикл For Each, всплывающие окна, заливка ячеек цветом) Макрос, Vba, Microsoft Excel, Урок, Длиннопост

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


Давайте разбирать код подробно.

В самой первой строке:

Sub findLargeValues()

мы с помощью ключевого слова Sub объявляем процедуру (процедура просто выполняет какой-то список команд по порядку и завершает работу) findLargeValues без параметров, потому что после ее названия идут пустые круглые скобки ().


Дальше идут строки:

Dim maxWeight As Integer

maxWeight = 5

здесь мы используем ключевое слово Dim, чтобы объявить переменную и через другое обязательное ключевое слово As задаем ей тип Integer (целое число).

Но нам мало объявить машине, что теперь будет такая переменная такого типа, VBA по умолчанию придаст ей значение 0 (это так для Integer и Double, например). Поэтому в следующей строке мы присваиваем ей значение 5, с помощью простого знака равенства.


Закрепим напоследок:

процедуру мы объявляем через Sub

функцию (которая выполняет команды, но потом еще возвращает какое-то значение) через Function

все переменные через Dim.


Далее идет цикл For Each … In … Next.

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


Я понимаю, что это непросто, но давайте представим, что вы – Гринч Санта Клаус.

Вся ваша ежегодная работа будет описываться циклом:

Для Каждого ребенка В спискеХороших

ребенку.подаритьПодарок

Следующий


На языке VBA это будет выглядеть так:

For Each child In niceList

child.makeAGift

Next


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

В примере с Сантой, коллекцией будет niceList, переменной – child. Циклю For Each … In … Next будет брать каждого child, который содержится в niceList, и вызывать для него метод makeAGift (читай – дарить подарок ему).


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


Дальше идет условный оператор, If <условие> Then <код1> Else <код2> End if.

Я уже разобрал его в первом посте, но давайте еще раз.


<условие> - это что-то, что можно подать машине как Boolean, как величину True или False (Да или Нет).

Это может быть переменная, которая объявлена как Boolean (if <переменная> then … ), это может быть сравнение величин (x = 5, y < 12, z >= -4, x = y).

Если <условие> = True, то выполняется код1. В противном случае выполняется код2.

Если Else <код2> вообще нету, то код1 выполняется, если <условие> = True, а в противном случае вообще ничего не происходит в программа просто идет по строчкам дальше, что там написано после End if.


В нашем случае <условие> - это выражение Val(cell.Value) > maxWeight.

Что мы имеем в нем:

cell – это та самая переменная из цикла For Each … in … Next. Она олицетворяет одну отдельно взятую ячейку из выделенного диапазона.

Через cell.Value мы получаем доступ к тому, что написано в этой ячейке, к ее значению.

Функция Val(“строка”) извлекает из строки число, если оно там вообще есть. Мы применяем ее, чтобы обезопасить себя от ситуации, когда в Excel e ячейки cell значание будет, например «5а». В этом случае функция Val(cell.Value) вернет нам просто число 5.

Далее все это выражение просто сравнивается с нашей переменной maxWeight.


Таким образом, все это выражение Val(cell.Value) > maxWeight будет True, если в ячейке cell будет написано число больше 5, и будет False, если оно будет меньше или равно 5.


Ну и дальше мы видим, что в случае True, то есть когда вес нашего отдельно взятого кота, который записан в cell, больше 5 кг, выполняется «код1»:

cell.Interior.Color = vbRed

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

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


Цвета, начинающиеся с vb… «вшиты» в сам Visual Basic, они бывают

vbBlack (черный),

vbBlue (синий),

vbCyan (бирюзовый),

vbGreen (зеленый),

vbMagenta (пурпурный),

vbRed (красный),

vbWhite (белый),

vbYellow (желтый).


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


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

cell.Interior.Color = RGB(rr, gg, bb),

а вместо rr, gg и bb подставить число от 0 до 255, обозначающее интенсивность красного, зеленого и синего соответственно.


Последняя строка в нашем коде End Sub обозначает, что все, что процедура кончилась.


Результат его выполнения будет такой:

Макросы VBA для Excel: выделение значений цветом (PERSONAL.xlsb, переменные, цикл For Each, всплывающие окна, заливка ячеек цветом) Макрос, Vba, Microsoft Excel, Урок, Длиннопост

Такой вот нехитрый макрос.


Но давайте добавим в него чуть-чуть универсальности и интерактивности.

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

Изменим код вот так

Макросы VBA для Excel: выделение значений цветом (PERSONAL.xlsb, переменные, цикл For Each, всплывающие окна, заливка ячеек цветом) Макрос, Vba, Microsoft Excel, Урок, Длиннопост

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

Макросы VBA для Excel: выделение значений цветом (PERSONAL.xlsb, переменные, цикл For Each, всплывающие окна, заливка ячеек цветом) Макрос, Vba, Microsoft Excel, Урок, Длиннопост

Вообще, этот метод в полной форме выглядит так:

InputBox(prompt, [ title ], [ default ], [ xpos ], [ ypos ], [ helpfile, context ])

prompt – обязательный параметр, сообщение в самом окошке, значение String (строка, пишется обязательно в кавычках);

[title] – необязательный параметр, заголовок этого окошка, тоже значение String;

[default] – необязательный, то, что по умолчанию уже будет написано, можете писать туда любое число или String;

[ xpos ], [ ypos ] – необязательные, положение окна на экране от верхнего левого угла, любое число;

[ helpfile, context ] – необязательные, helpfile - String, ссылающаяся на файл справки, а context – на пункт в ней (в виде числа).


Мы, как вы заметили, использовали только первые три параметра.


Если бы вы, например, хотели указать только prompt, title и xpos, ypos, то нужно было бы писать так:

InputBox(“Сообщение”,”Заголовок”,,100,150), то есть ставить запятую, перед тем местом, где должен был быть default, а потом еще одну, перед xpos. А после ypos просто закрывать скобку.


На этом, пожалуй, остановимся. Всем спасибо за внимание, желаю успехов в освоении VBA.

Буду рад видеть в комментариях ваши мнения, просьбы и советы.

Показать полностью 9
20

Excel. Помогите пожалуйста!

Уважаемые Пикабушники!

Сегодня полдня бился над  проблемой! Помогите пожалуйста!

Задача такая: В Excel есть столбец с количеством работников, есть второй столбец - с количеством положенной им спецодежды. Нужно сделать так: если ячейка в столбце 2(кол-во одежды) пустая, то оставить её пустой или поставить 0. А если не пустая, то имеющуюся в этой ячейке цифру нужно разделить на 3. Для чего это нужно? есть, допустим, 6 человек. Им положена каждому 1 куртка на 3 года. Необходимо рассчитать среднее за один год, в данном случае - 6 /3=2. А если не положено им ничего - то так пустой ячейку и оставить. Сам понимаю, что довольно запутанно, но надеюсь на понимание и помощь.


Решение найдено!

Спасибо Вам всем за помощь!

Для меня наиболее простым решением оказалось предложение уважаемого querulus: пишешь в любое пустое место на листе цифру 3. Наживаешь Ctrl+C

Выделяешь свой столбец (диапазон данных) и жмешь Ctrl+Alt+V , выбираешь справа вниз "Разделить"

Готово!

Там где было пусто - будет 0, там где была цифра она разделится на 3

16

Excel =ЕСЛИ(

Прошу помочь. Сам в экселе чуть более чем ноль. Смотрел гайды читал инструкции, но всё равно что-то делаю не так.
Что хочу что бы было. Есть 3 столбца C, D, E. В них есть "едто", "енто", либо одно либо другое, либо и то и то. Так вот, нужно что бы в зависимости от того есть ли там что либо из перечисленного, в столбец F автоматически приписывалось "едто".
То что получилось у меня, сомневаюсь что нужна такая длинна формула на каждый столбец отдельно, а по другому не получилось. Если захватывать сразу 3 столбца, то не работает. Почему?...
И еще почему =ЕСЛИ не воспринимает лог-выражение если записать через пробел? (=ЕСЛИ(C3="ЕДТО ЕНТО") вот так не работает...

Excel =ЕСЛИ( Microsoft Excel, Помощь, Без рейтинга

Буду благодарен за помощь!
(Да не скриншот, а фото. Ну и что:))

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