Создание зависимых списков.

Ответ на вопрос нашего подписчика. Чтобы создать второй раскрывающийся список, зависящий от значения выбранного в первом списке, воспользуемся Проверкой данных, только в Источнике данных укажем функцию =ДВССЫЛ().

#Списки #ЗависимыеСписки #ПроверкаДанных

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

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

#ДатаИВремя #СуммированиеВремени

Microsoft 3D Maps.

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

#Maps #3DКарты #Карты

Присвоение имен константам и формулам.

Работа с формулами сильно упрощается если присваивать имена ячейкам и диапазонам, но в Excel так же есть возможность присваивать имена константам например: НалогСтавка=0,13 и формулам например: Прибыль=Доход-Расход.

#ДиспетчерИмен #ПрисвоениеИменКонстантам #ПрисвоениеИменФормулам

Создание таблицы.

Отвечаем на вопрос нашего подписчика. Перед созданием таблицы убедитесь, что диапазон не содержит полностью пустых строк или столбцов. Установите курсор на ячейку диапазона и нажмите кнопку команды Вставить Таблицу или комбинацию клавиш <Ctrl+T>. Откроется диалоговое окно Создание таблицы. Excel попытается самостоятельно определить диапазон ячеек. Если же диапазон определен неправильно, укажите его вручную.
После преобразования диапазона в таблицу, на ленте появится вкладка Конструктор.

#СозданиеТаблицы

Текст по столбцам. Транспонирование.

Отвечаем на вопрос нашего подписчика. Команда Текст по столбцам позволяет разбить значения ячейки/ячеек на составляющие их элементы.
Опция Транспонирование диалогового окна Специальная вставка изменяет ориентацию строк и столбцов копируемого диапазона. Строки становятся столбцами, а столбцы – строками.

#ТекстПоСтолбцам #Транспонирование

Спарклайны.

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

#Cпарклайн #Спарклайны

Автоматическое назначение десятичных запятых.

Этот способ ввода чисел заинтересует тех, кому приходится вводить множество цифр с фиксированным числом десятичных разрядов. Выберите команду Файл > Параметры и щелкните на вкладке Дополнительно. Установите флажок Автоматическая вставка десятичной запятой и убедитесь в том, что в поле Число знаков после запятой установлено нужное число десятичных разрядов.
Если опция активизирована, Excel автоматически расставляет десятичные запятые по мере ввода чисел.
Например, если задать четыре десятичных знака после запятой, то после ввода в ячейку значения 123456 программа автоматически будет интерпретировать это число как 12,3456 (десятичную запятую она также добавит автоматически).
Чтобы вернуться к первоначальному режиму, сбросьте флажок этой опции в диалоговом окне Параметры Excel. Установка или отмена данной опции не влияет на ранее введенные числа.

#ДесятичныеЗапятые #ВводЧисел

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

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

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

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

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

Создание отношений между таблицами в модели данных Power Pivot.

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

#PowerPivot #ОтношенияТаблиц #Связи

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

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

#Параметры #СозданиеВкладки #НастройкаЛенты

Обнаружение повторяющихся значений. Функция СЧЕТЕСЛИ (COUNTIF).

Если вам необходимо выделить повторяющиеся значения без их удаления, можно воспользоваться функцией СЧЕТЕСЛИ().
Данный способ работает с реальными, а не с отображаемыми значениями.

#Дубли #ПовторяющиесяЗначения #СЧЕТЕСЛИ #COUNTIF

Работа с датами.

Excel оперирует датами, используя их последовательную нумерацию. Самая ранняя дата, которую способна распознать программа Excel 1 января 1900 года. Этой дате присвоен порядковый номер 1.
Дата 2 января 1900 года имеет порядковый номер 2 и т.д. Такая система представления значительно облегчает использование дат в формулах. Зная это, можно, например, ввести формулу, по которой вычисляется количество дней между двумя датами.

В большинстве случаев вам не придется напрямую иметь дело с системой последовательной нумерации дат, принятой в Excel. Дата вводится в привычном формате, а о деталях программа заботится сама. Предположим, вам нужно ввести следующую дату: 19 мая 2020 года. Для этого нужно набрать 19/5 (или использовать любой другой из распознаваемых форматов дат). Excel интерпретирует эту дату как значение 43970, которое является порядковым номером введенной вами даты.

#Даты #РаботаСДатами

Ввод значений времени.

При вводе значений времени Excel расширяет систему представления дат так, чтобы она включала десятичные дроби.
Другими словами, Excel трактует время как дробную часть дня. Например, порядковый номер даты 19 мая 2020 года - 43970. Полдень указанного дня соответствует внутреннему представлению 43970,5, поскольку представляющая этот момент времени дробь просто добавляется к порядковому номеру дня для получения полного числового значения даты/времени.

#Дата #Время #РаботаСоВременем #РаботаСДатами

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

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

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

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

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

#ДатаИВремя #ДОЛЯГОДА #YEARFRAC

Проверка выполнения простого условия. Функция ЕСЛИ (IF).

Функция ЕСЛИ() - это фундаментальная функция проверки условий в Excel. Она имеет три аргумента:
- условие;
- действие, когда условие выполняется;
- действие, когда условие не выполняется.

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

=ЕСЛИ(B2>СРЗНАЧ(B$2:B$6);"Высокая";"Низкая")

#Условие #ПростоеУсловие #ЕСЛИ #IF

Ввод текущих даты и времени.

Иногда нужно ввести в ячейку текущую дату или время. Для этой цели в Excel предусмотрены два клавиатурных эквивалента.
▪️ Текущая дата. Комбинация <Ctrl+;> (точка с запятой).
▪️ Текущее время. Комбинация <Ctrl+Shift+;> (точка с запятой).

Чтобы ввести в ячейку одновременно дату и время, нажмите <Ctrl+;>, введите пробел, а затем нажмите <Ctrl+Shift+;>.

Данные о дате и времени берутся из системного времени вашего компьютера.

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

#ДатаИВремя #ВводДанных #ТДАТА #СЕГОДНЯ #NOW #TODAY

Суммирование значения, удовлетворяющих условиям. Функция СУММЕСЛИМН (SUMIFS).

Чтобы вычислить сумму значений, удовлетворяющих нескольким условиям, нужно воспользоваться функцией СУММЕСЛИМН().
Например: суммировать только значения более 18 000 и менее 35 000.
Данная функция появилась в Excel 2007.

#МатематическиеФункции #СУММЕСЛИМН #SUMIFS

Выделение ячеек на основании дат. Условное форматирование.

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

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

Англоязычный вариант формулы:

=OR(WEEKDAY(A1)=1;WEEKDAY(A1)=7

)

#условноеформатирование #ДЕНЬНЕД #ИЛИ

Автозаполнение. Прогрессия

Инструмент Прогрессия, позволяет быстро заполнить ячейки значениями. Например, необходимо быстро заполнить диапазон или таблицу только рабочими днями, это легко сделать, выбрав начальную дату периода в диапазоне и указав конечную дату. Арифметическая и геометрическая прогрессия позволят быстро заполнить диапазон значениями с определенным шагом до предельного значения.

#Прогрессия #Автозаполнение #АрифметическаяПрогрессия #ГеометрическаяПрогрессия