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

MS, Libreoffice & Google docs

347 постов 11 361 подписчик
71

Макросы. Изучаем редактор VBE. Академия Excel

Давайте разбираться где происходит написание макросов? В Excel существует встроенный редактор Visual Basic Editor (VBE), в котором собственно и происходит создание макросов с нуля, редактирование существующий, удаление ненужных.

Посмотрев урок, Вы научитесь настраивать редактор под себя, познакомитесь с новым понятием Модуль (узнаете основные операции с ними).


ПРОШЛЫЙ УРОК


01:05 - Как попасть в редактор VBE

01:41 - Kак вернуться к Excel

02:32 - Настройка внешнего вида редактора

04:24 - Перемещение окон редактора

06:20 - Окно проекта. Знакомство с модулями.

07:26 - Создание модулей

08:04 - Удаление модулей

09:28 - Переименование модуля

11:27 - Форматирование кода макроса

Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi

137

Функция ПРОСМОТРX - наследник ВПР

День добрый, пост по просьбе @Merimin,


Кому интересно, в конце прикреплена видеоверсия


В мае 2019 года руководитель команды разработчиков Microsoft Excel Joe McDaid анонсировал выход новой функции, которая должна прийти на замену легендарной ВПР (VLOOKUP). Новая функция получила сочное английское название XLOOKUP и не очень внятное русское ПРОСМОТРX (причем последняя буква тут именно английская "икс", а не русская "ха" - забавно).

Полгода Microsoft тренировалась на кошках тестировала эту функцию на своих сотрудниках и добровольцах-инсайдерах и, наконец, в январе 2020 года было объявлено, что XLOOKUP готова к использованию и будет в ближайшее время разослана с обновлениями всем подписчикам Office 365.

Давайте разберёмся, в чем её преимущества перед классической ВПР (VLOOKUP), и как она может нам помочь в повседневной работе с данными в Microsoft Excel.


Старый добрый ВПР


Предположим, перед нами стоит задача найти в прайс-листе цену, например, для гречки. При помощи привычно функции ВПР (VLOOKUP) это решалось бы примерно так:

Функция ПРОСМОТРX - наследник ВПР Впр, Microsoft Excel, Видео, Длиннопост

На всякий случай, напомню:

Первый аргумент здесь - искомое значение ("гречка" из H4).

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

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

Последний аргумент отвечает за режим поиска: 0 - точный поиск, 1 - поиск ближайшего наименьшего значения (для чисел). Причем 0 не подразумевается по умолчанию - нужно вводить его явно.

Привычно, знакомо и делается многими на автомате, не приходя в сознание. ОК.

Теперь посмотрим как то же самое можно вычислить с помощью новой функции ПРОСМОТРX (XLOOKUP).


Синтаксис ПРОСМОТРX (XLOOKUP)


Сначала, для порядка, давайте озвучим официальный синтаксис. У нашей новой функции 6 аргументов:


=ПРОСМОТРX(искомое_значение; просматриваемый_массив; возвращаемый_массив; [если_ничего_не_найдено]; [режим_сопоставления]; [режим_поиска])


Выглядит немного громоздко, но последние три аргумента [в квадратных скобках] не являются обязательными (мы разберёмся с ними чуть позже). Так что, на самом деле, всё проще:

Функция ПРОСМОТРX - наследник ВПР Впр, Microsoft Excel, Видео, Длиннопост

Первый аргумент (искомое_значение) - что мы ищем ("гречка" из ячейки H4)

Второй аргумент (просматриваемый_массив) - диапазон ячеек, где мы ищем (столбец Товар в прайс-листе).

Третий аргумент (возвращаемый_массив) - диапазон, откуда хотим получить результаты (столбец Цена в прайс-листе).

Если сравнивать с ВПР, то стоит отметить, что:

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

Не нужно отсчитывать и задавать номер столбца (третий аргумент ВПР). В больших таблицах это бывает непросто (особенно с учетом наличия скрытых столбцов).

Из предыдущего пункта автоматом следует, что вставка/удаление столбцов в прайс не ломают формулу (как было бы с ВПР).

Нет проблемы "левого ВПР", когда нужно извлечь значение левее просматриваемого столбца (например, артикул в нашем случае) - просматриваемый и возвращаемый массивы в ПРОСМОТРX могут располагаться как угодно (даже на разных листах, в общем случае!)

В общем и целом синтаксис гораздо проще и понятнее, чем у ВПР.

Также приятно, что ПРОСМОТРX отлично работает и в горизонтальном варианте без каких-либо доработок:

Функция ПРОСМОТРX - наследник ВПР Впр, Microsoft Excel, Видео, Длиннопост

Раньше для этого нужно было использовать уже функцию ГПР (HLOOKUP) вместо ВПР (VLOOKUP).


Перехват ошибок #Н/Д


Если искомое значение отсутствует в списке, то функция ПРОСМОТРX, как и ВПР, выдаёт знакомую ошибку #Н/Д (#N/A):

Функция ПРОСМОТРX - наследник ВПР Впр, Microsoft Excel, Видео, Длиннопост

Раньше для перехвата таких ошибок и замены их на что-нибудь более осмысленное применяли вложнную конструкцию из функций ЕСЛИОШИБКА (IFERROR) и ВПР (VLOOKUP). Теперь же можно сделать всё "на лету", используя 4-й аргумент [если_ничего_не_найдено] нашей новой функции :

Функция ПРОСМОТРX - наследник ВПР Впр, Microsoft Excel, Видео, Длиннопост

Удобно.


Приблизительный поиск


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

В старой ВПР за это отвечал последний аргумент [интервальный_просмотр] - если задать его равным 1, то ВПР переходила в режим поиска ближайшего наименьшего значения. В ПРОСМОТРХ за этот функционал отвечает 5-й аргумент [режим_сопоставления]:

Функция ПРОСМОТРX - наследник ВПР Впр, Microsoft Excel, Видео, Длиннопост

Он может работать по четырём различным сценариям:

0 - точный поиск (это режим по-умолчанию)

-1 - поиск предыдущего, т.е. ближайшего наименьшего значения (для 29 шт. товара это будет скидка 5%)

1 - поиск следующего, т.е. ближайшего наибольшего (для 29 шт. товара это будет уже 10% скидки)

2 - неточный поиск текста с использованием подстановочных символов

Если с первыми тремя вариантами тут всё более-менее понятно, то последний стоит прокомментировать дополнительно. Имеется ввиду ситуация, когда мы ищем значение, где помимо букв и цифр использованы подстановочные символы * (звёздочка = любое количество любых символов) и ? (вопросительный знак = один любой символ).

На практике это может использоваться, например, так:

Функция ПРОСМОТРX - наследник ВПР Впр, Microsoft Excel, Видео, Длиннопост

Заметьте, что, например, капуста в прайс-листе и бланке заказа здесь записана по-разному, но ПРОСМОТРX всё равно её находит, т.к. ищем мы уже не просто капусту, а капусту с приклеенными в начале и конце звёздочками и четвёртый аргумент нашей функции равен 2.

Функция ВПР, кстати говоря, всегда умела такое "из коробки", так что особого преимущества у ПРОСМОТРX здесь нет. Но важен другой нюанс: функция ВПР при включенном приблизительном поиске (последний аргумент =1) строго требовала сортировки искомой таблицы по возрастанию. Новая функция прекрасно ищет ближайшее наибольшее или наименьшее и в неотсортированном списке.

Направление поиска

Если в таблице есть не одно, а несколько совпадений с искомым значением, то функция ВПР всегда выдает первое, т.к. ведёт поиск исключительно сверху-вниз. ПРОСМОТРX может искать и в обратном направлении (снизу-вверх) - за это отвечает последний 6-й её аргумент [режим_поиска]:

Функция ПРОСМОТРX - наследник ВПР Впр, Microsoft Excel, Видео, Длиннопост

Благодаря ему, поиск первого и (главное!) последнего совпадения больше не представляет сложности - различие будет только в значении этого аргумента:

Функция ПРОСМОТРX - наследник ВПР Впр, Microsoft Excel, Видео, Длиннопост

Раньше для поиска последнего совпадения приходилось неслабо шаманить с формулами массива и несколькими вложенными функциями типа ИНДЕКС, НАИБОЛЬШИЙ и т.п.

Резюме

Если вы дочитали до этого места, то выводы, я думаю, уже сделали сами :) На мой взгляд, у Microsoft получилось создать очень достойного наследника легендарной функции ВПР, добавив мощи и красоты и сохранив, при этом, простоту и наглядность использования.

Минус же пока только в том, что эта функция в ближайшее время появится только у подписчиков Office 365. Пользователи standalone-версий Excel 2013, 2016, 2019 эту функцию не получат, пока не обновятся до следующей версии Office (когда она выйдет). Но, рано или поздно, эта замечательная функция появится у большинства пользователей - вот тогда заживём! :)



Автор материала Николай Павлов, материал взят отсюда

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

Средний балл в Excel

Рассмотрим три способа рассчитать средний балл в Excel на примере ведомости оценок учеников.


0:00 - Постановка задачи

0:14 - Расчет среднего балла вручную

0:43 - Функции СУММ и СЧËТ

1:18 - Функция СРЗНАЧ


Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi

107

Функция ВПР в Excel. Как пользоваться функцией ВПР (VLOOKUP) в Excel

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


00:00 - Введение

00:16 - Зачем нужна функция ВПР (VLOOKUP)

01:33 - Как работает функция ВПР

02:27 - Синтаксис и аргументы функции ВПР

07:00 - Пример использования функции ВПР

09:55 - 5 причин, почему формула с функцией ВПР не работает

13:46 - Интервальный просмотр - приблизительное совпадение

17:39 - Недостатки функции ВПР


Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi

78

Макросы. 7 Способов запуска макроса. Академия Excel

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

ПРОШЛЫЙ УРОК


00:12 - №1. Вкладка Разработчик - Макросы

00:35 - №2. Сочетание клавиш Alt+F8

00:47 - №3. Вкладка Вид - Макросы

00:58 - №4. Вкладка Разработчик - Эл.упр. - Кнопка

01:33 - №5. Вкладка Вставка - графический элемент

02:03 - №6. Редактор VBE

02:45 - №7. Событие - Активация листа

03:57 - Подводим итоги урока

04:20 - Анонс следующего урока


Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi
71

Как сделать абсолютную ссылку в Excel

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


0:00 - Введение

0:18 - Что такое относительная адресация в Excel

3:35 - В каких случаях абсолютная ссылка предпочтительнее

4:13 - Как сделать абсолютную ссылку в Excel

5:27 - Как сделать таблицу умножения в Excel


Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi

306

5 Полезных функций Excel, о которых вы могли не знать

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


0:00 - Вступление

0:09 - Функция СЦЕПИТЬ в Excel и ее замена

0:39 - Как в Excel посчитать сумму столбца или строки

1:19 - Комментарий в формуле с помощью функции Ч

1:55 - Римские цифры в Excel

2:25 - Разница между датами в Excel

Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi

866

Мгновенное заполнение в Excel. Малоизвестные функции Excel

Мгновенное заполнение в Excel (Flash Fill) поможет в том случае, если часто приходится формировать какие-то списки или вносить данные. Например, с помощью функции мгновенное заполнение можно разделить ФИО на три столбца. Подробности в видео.

Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi

91

5 СОВЕТОВ по оформлению таблицы в Excel

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


0:18 - Стили таблицы

1:05 - Стили ячеек

2:55 - Темы книги

3:53 - Очистка форматирования

4:19 - Диагональ в заголовке таблицы

Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi

366

Диаграммы в Excel. 5 Советов как создать диаграмму в Excel

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

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

0:00 - Введение.

0:21 - Разъезжающаяся диаграмма.

0:55 - Несколько диаграмм на одном листе.

2:00 - Год в качестве ряда данных.

2:51 - Разные величины на одной диаграмме.

https://t.me/hacks_excel/2095

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