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

MS, Libreoffice & Google docs

365 постов 11 506 подписчиков
29

Как выбрать уникальные данные из таблицы?

Есть таблица - название фирмы, сотрудник, адрес.

Как выбрать уникальные данные из таблицы? Помощь, Microsoft Excel, Формула

Нужно выбрать для каждой фирмы одного любого (первого попавшегося) сотрудника.

Выбрать фирмы - понятно:

=УНИК(Таблица2[[Company ]])

А вот при попытке получить остальные данные - облом-с, либо получаю только один столбец:

=ПРОСМОТРX(J2#;Таблица2[[#Все];[Company ]];Таблица2[[#Все];[First Name ]:[Country ]])
Как выбрать уникальные данные из таблицы? Помощь, Microsoft Excel, Формула

либо только одну строку:

=ПРОСМОТРX(J15;Таблица2[[#Все];[Company ]];Таблица2[[#Все];[First Name ]:[Country ]])
Как выбрать уникальные данные из таблицы? Помощь, Microsoft Excel, Формула

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

Исходник на Яндекс.Диск

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

Как распечатать таблицу в Excel. 5 ПРИЕМОВ

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


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

0:08 - Как распечатать границы ячеек (сетку листа)

1:13 - Как распечатать таблицу в Excel на весь лист

1:43 - Как убрать пунктирные линии после печати

2:16 - Как распечатать часть таблицы

2:53 - Шапка таблицы на каждой странице


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

464

Как сделать выпадающий список в Excel умным

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


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

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

0:47 - Как сделать выпадающий список в Excel

1:53 - Создание именованных диапазонов

2:28 - Создание зависимого списка. Функция ДВССЫЛ

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

378

Функция РАНГ в Excel. Составляем простой рейтинг

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

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

61

Как добавить строку (столбец) в Excel

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


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

0:15 - Как добавить строку в таблице Excel

1:22 - Как добавить столбец в таблицу Excel

1:53 - Нюанс вставки новых строк и столбцов

2:28 - Вставка нескольких строк (столбцов)

2:49 - Как вставить строку (столбец) в умную таблицу


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

234

Умная таблица в Excel. Секреты эффективной работы

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

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


00:00 - Введение - преимущества умных таблиц

00:56 - Как сделать таблицу в Excel

01:54 - Форматирование таблицы в Excel (Стили)

03:11 - Имя (название) умной таблицы

04:09 - Сортировка, фильтр и срез в умных таблицах

06:20 - Строка итогов

07:16 - Как добавить строку или столбец в умную таблицу

08:26 - Как в таблице перенести строку или столбец

09:14 - Как удалить дубликаты в умной таблице

10:23 - Сводная таблица в Excel

11:40 - Умные таблицы и диаграммы

12:20 - Особенности проведения расчетов в умных таблицах

15:14 - Как удалить умную таблицу

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

294

Числа прописью в Excel быстро и без вспомогательных инструментов!

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


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


Вот пример её действия:

Числа прописью в Excel быстро и без вспомогательных инструментов! Microsoft Excel, Формула, Работа, Видео, Длиннопост

Файл с формулой можно скачать по следующей ссылке:


https://drive.google.com/file/d/1YLsHzifuVgath7ruzLlsxywOdTt...


Как можете заметить, в функции очень много ссылок на одну и ту же ячейку – H6. Естественно, вручную перетаскивать все ссылки H6 не вариант (их 97!). Поэтому вот как работаем с этой формулой:

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


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

Числа прописью в Excel быстро и без вспомогательных инструментов! Microsoft Excel, Формула, Работа, Видео, Длиннопост

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


3. Вызываем окно поиска и замены с помощью CTRL+H


4. В «Найти» вписываем заменяемый адрес – H6, в «Заменить на» - адрес новой нужной ячейки – например, C6

Числа прописью в Excel быстро и без вспомогательных инструментов! Microsoft Excel, Формула, Работа, Видео, Длиннопост

5. Нажимаем на «Заменить все». Замена происходит лишь в обеих выбранных ячейках (именно для этого мы и выбирали вторую ячейку, чтобы ограничить замену лишь на выбранный диапазон). Готово! Формула сразу ссылается на нужное значение:

Числа прописью в Excel быстро и без вспомогательных инструментов! Microsoft Excel, Формула, Работа, Видео, Длиннопост

Из особенностей формулы:

1. Формула абсолютно самостоятельна и не использует VBA или сторонние Add-Ins

2. Формула работает с числами до 999.999.999

3. Формула правильно склоняет слова

4. Вся формула в свою очередь состоит в основном из формул ЛЕВСИМВ, ПСТР, ЕСЛИ, ТЕКСТ, И, ВЫБОР

5. Количество символов в формуле с прописью целых и сотых: 6034

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

Показать полностью 2 1
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

138

Функция ПРОСМОТР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

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