Генерация случайных чисел в заданном диапазоне. Функция СЛУЧМЕЖДУ (RANDBETWEEN).

Отвечаем на вопрос нашего подписчика. Для того чтобы сгенерировать случайное число, находящееся в диапазоне между двумя заданными числами, нужно воспользоваться функцией СЛУЧМЕЖДУ().  Функция имеет следующий синтаксис:

СЛУЧМЕЖДУ(нижн_граница; верхн_граница)

#случайноечисло #генераторчисел #СЛУЧМЕЖДУ #RANDBETWEEN

Вычисление прошедшей и оставшейся частей года в процентах. Функция ДОЛЯГОДА (YEARFRAC)

При построении в Excel отчетов и графических панелей итоговых данных часто может быть выгодно указать в процентах, какая часть года уже прошла или еще осталась. Эти процентные величины затем могут использоваться в других вычислениях или служить обычным напоминанием для тех, кто будет работать с данным документом.
Функция ДОЛЯГОДА() имеет два обязательных аргумента: начальную дату и конечную дату. На основании этих двух значений вычисляется часть полного года, определяемая заданными начальной и конечной датами.

=ДОЛЯГОДА(A2;B2)

Чтобы получить процентное представление оставшейся части года, следует просто вычесть из единицы возвращаемое функцией ДОЛЯГОДА() значение.

=1-ДОЛЯГОДА(A2;B2)

#дата #ДОЛЯГОДА #YEARFRAC

Импорт данных из буфера обмена.

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

#импортданных #буферобмена #импортизword

Создание новой вкладки.

Для большинства пользователей необходимости настраивать ленту нет. Но если вам постоянно приходится выбирать одну и ту же последовательность команд и при этом переходить из одной вкладки в другую, то вы, безусловно, выиграете, воспользовавшись возможностью разместить все необходимые в вашей работе команды на одной вкладке.
Для создания новой вкладки можно воспользоваться кнопкой Создать вкладку, на вкладке Настроить ленту в Параметрах. Excel создаст вкладку Новая вкладка (Настраиваемая), а в этой вкладке - группу, Новая группа (Настраиваемая). Новым вкладкам и группам всегда присваиваются стандартные имена, а чтобы заменить их какими-либо осмысленными именами, следует воспользоваться кнопкой Переименовать. Чтобы изменить положение новой вкладки на ленте, воспользуйтесь кнопками Вверх и Вниз. Чтобы добавить команду, выбранную в левом списке, в группу, выбранную в правом списке, щелкните на кнопке Добавить.

#параметры #созданиевкладки #созданиегруппы #настройкаленты

Быстрое форматирование. Сочетания клавиш.

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

▪️<Ctrl+B> Полужирное начертание;
▪️<Ctrl+I> Выделение курсивом;
▪️<Ctrl+U> Подчеркивание;
▪️<Ctrl+5>  Перечеркивание.

#форматирование #сочетанияклавиш

Создание «очень скрытого» рабочего листа

Для того чтобы сделать лист «очень скрытым», т.е. лист не будет отображаться в диалоговом окне Вывод на экран скрытого листа, необходимо воспользоваться редактором Visual Basic (Visual Basic Editor), изменив значения свойства листа Visible на 2-xlSheetVeryHidden. Чтобы открыть редактор Visual Basic воспользуйтесь командой на вкладке Разработчик или используйте сочетание клавиш <Alt+F11>.

#скрытыйлист #оченьскрытыйлист #vbe #visible

Удаление пробелов и непечатаемых символов.

Часто бывает так, что данные импортированные в рабочие листы из внешних источников могут содержать лишние пробелы и специальные непечатаемые символы. В Excel есть функции, которые помогут привести данные в порядок.
Функция СЖПРОБЕЛЫ (TRIM) удалит пробелы перед и после текста, а так же внутри текста, оставляя по одному между словами.
Функция ПЕЧСИМВ (CLEAN) удаляет все непечатаемые символы из текста.

#удаление #удалениепробелов #непечатаемыесимволы #удалениесимволов #сжпробелы #печсимв #CLEAN #TRIM

Текстовые функции. Изменение регистра.

Отвечаем на вопрос нашего подписчика. Что бы изменить регистр текста, используются три функции:
▪️ПРОПНАЧ() - первая буква в строке и все первые буквы, следующие за знаками, отличными от букв, становятся прописными.
▪️СТРОЧН() – преобразует все буквы в нижний регистр.
▪️ПРОПИСН() – преобразует все буквы в верхний регистр.

#ПРОПНАЧ #СТРОЧН #ПРОПИСН #текстовыефункции #текст #регистр

Массивы. Использование массивов в формулах.

Массив – это набор элементов, которые могут обрабатываться как единая группа, или каждый по отдельности. В Excel массивы могут быть одномерными и двухмерными. Например: одномерный массив может быть группой ячеек, которые размещены в одной строке (горизонтальный массив) или в одном столбце (вертикальный массив). Двухмерный массив размещается в нескольких строках и столбцах. Трехмерные массивы Excel не поддерживает, но такую возможность имеет VBA.

Создание формулы массива рассмотрим на примере подсчета стоимости товаров. Формула будет введена сразу во все ячейки D2:D9.

Использование формулы массивов имеет ряд преимуществ:.
▪️все формулы диапазона введены идентично;
▪️больше защищены от непреднамеренных изменений, поскольку нельзя изменить одну формулу в ячееке, входящей в массив;
▪️использование формул массивов практически гарантирует, что новички не внесут изменения в ваши формулы.

#массивы #использованиемассивов #формулы

Отображение данных на карте.

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

#карта #данные #данныенакарте

Суммирование значений времени.

Многие пользователи приходят в замешательство, когда сумма значений времени, если она превышает 24 часа, отображается неверно. Например: сумма рабочего времени с понедельника по пятницу, при восьмичасовом рабочем дне, составит 16:00:00, хотя должна равняться 40:00:00.  Для того чтобы исправить отображение времени, превышающих 24 часа, применяется пользовательский формат [ч]:мм:сс, или [ч]:мм если не надо учитывать секунды.

#функции #время #формат #суммирование #суммированиевремени

Power Query. Получение списка имен файлов из папки.

Отвечаем на вопрос нашего подписчика. Для того чтобы получить список имен файлов из папки можно воспользоваться устаревшей функцией ФАЙЛЫ(),  или же воспользоваться более изящным и мощным инструментом - надстройкой Power Query. С ее помощью можно очень легко получить список имен файлов из каталога, создав запрос.

#PowerQuery #запрос #файлы #именафайлов #списокимен

Power Query. Объединение запросов.

В дополнение к предыдущему видео. Для того чтобы проверить у всех ли сотрудников есть фотография в каталоге, так же воспользуемся надстройкой Power Query, только на этот раз выполним объединением запросов из каталога и таблицы.

#PowerQuery #запрос #слияние #слиеяниезапросов #списокименфайлов

Условное форматирование. Использование формул. Выходные дни.

Отвечаем на вопрос подписчика. В Excel есть большое количество правил условного форматирования для работы с датами, но среди них нет правила, которое определяло бы даты, приходящиеся на выходные дни (т.е. которые соответствовали бы субботе и воскресению). Следующая формула возвращает значение ИСТИНА, если дата в ячейке является субботой или воскресением.

=ИЛИ(ДЕНЬНЕД(А1)=1;ДЕНЬНЕД(А1)=7)

#форматирование #условноеформатирование #формулы

Подсчет наиболее часто встречающихся значений. Функция МОДА и МОДА.ОДН

Чтобы определить, какое значение в выделенном диапазоне встречается чаще всего, используется функция МОДА(). Но так как данная функция в последних версиях Excel оставлена для обеспечения совместимости, то в свежих версиях используйте функцию МОДА.ОДН().

#повторяющиесязначения #МОДА #МОДАОДН

Создание собственной функции извлечения цифр из строки.

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

Public Function GetNumbers(TargetCell As Range) As String
 Dim LenStr As Long
 For LenStr = 1 To Len(TargetCell)
 Select Case Asc(Mid(TargetCell, LenStr, 1))
 Case 48 To 57
 GetNumbers = GetNumbers & Mid(TargetCell, LenStr, 1)
 End Select
 Next
End Function

#извлечение #извлечениецифр #собственнаяфункция #созданиефункции

Спарклайны.

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

#спарклайн #диаграмма #график #гистограмма #выигрышпроигрыш

Диаграммы. Диаграмма Ганта

Отвечаем на вопрос нашего подписчика. Excel не поддерживает как таковую диаграмму Ганта, но не смотря на это, ее можно легко создать на основе Линейчатой диаграммы с накоплением. Самое главное правильно задать исходные данные.

#диаграммы #диаграммаганта

Замена текста.

Часто требуется заменить часть текстовой строки другим тестовым фрагментом. Для подобных операций предусмотрены две функции.
▪️Функция ПОДСТАВИТЬ (SUBSTITUTE) заменяет указанный текст в текстовой строке. Эту функцию следует использовать тогда, когда вы знаете какой именно текст нежно заменить в строке, но не знаете где он находится.
▪️Функция ЗАМЕНИТЬ (REPLACE) замещает часть текстовой строки другим тестом, начиная с указанной позиции. Эту функцию применяют тогда, когда известно местоположение заменяемого текста, но не известен сам текст.

#заменатекста #ПОДСТАВИТЬ #ЗАМЕНИТЬ #REPLACE #SUBSTITUTE

Создание пользовательских шаблонов.

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

Для создания шаблона нужно лишь указать Тип файла: Шаблон Excel и указать нужное вам Имя файла. Путь сохранения пользовательских шаблонов будет указан автоматически: "Документы\Настраиваемые шаблоны Office". Ваш шаблон будет доступен на закладке Личные панели Создать.

#шаблоны #пользовательскиешаблоны #созданиешаблонов