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

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

#своднаятаблица #созданиесводнойтаблицы

Множественная сортировка.

С сортировкой по одному столбцу многие знакомы. Часто получаем вопросы от подписчиков как выполнить сортировку сразу по нескольким столбцам. Рассказываем, Множественную сортировку можно выполнить диалогового окна Сортировка. Данное окно можно открыть четырьмя способами, с вкладки Главная, с вкладки Данные, из контекстного меню любой ячейки таблицы, а также фильтра. В диалоговом окне Сортировка надо указать, по какому столбцу будет выполнять сортировка и в каком порядке.

#сортировка #множественнаясортировка

Фильтрация таблицы по срезам.

Очень удобный способ фильтрации таблиц – использование одного и более среза. Метод визуально очень привлекательный. Срезы очень наглядны, и очень легко видеть, какой тип фильтрации задействован. Недостаток срезов заключается в том, что они занимают много места на экране.

#фильтрация #срезы

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

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

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

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

Текстовые функции. Функция ТЕКСТ.

Отвечаем на вопрос нашего подписчика. Чтобы вывести итоговые значения по таблице одной текстовой фразой используем в формуле несколько функций, основная из них ТЕКСТ, преобразующая числовые значения в строковые, а также символ конкатенации &, позволяющую объединять несколько строковых значений в одну. С другими функциями, как СУММ и СЧЕТЗ вы знакомы.

#текстовыефункции #ТЕКСТ

Прибавление к значению времени часов, минут и секунд.

Отвечаем на вопрос нашего подписчика. Чтобы прибавить к значению времени определенное количество часов, минут или секунд, обычно используется функция ВРЕМЯ().

#функции #ВРЕМЯ #прибавлениевремени

Функции поиска. Функция ВПР.

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

ВПР(Искомое_значение;Таблица;Номер_столбца;Параметр_просмотра)

#поиск #ВПР #функциипоиска

Преобразование единиц измерения. Функция ПРЕОБР.

Отвечаем на вопрос нашего подписчика. Для того чтобы преобразовать мили в километры, необходимо воспользоваться функцией ПРЕОБР(). Но, единицы измерения километр нет у данной функции, поэтому преобразуем сначала в метры, а далее полученное число делим на 1000.

#преобразование #единицыизмерения #ПРЕОБР

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

Отвечаем на вопрос нашего подписчика. Для того чтобы извлечь цифры из строки создадим собственную функцию 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

Подробнее редактор и язык VBA мы будем рассматривать позже.

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

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

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

#суммирование #условия #СУММЕСЛИМН

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

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

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

Автозамена.

С помощью функции Автозамена можно создавать сокращения для часто употребляемых слов или фраз. Например, если вы работаете в фирме ОАО «Рога и копыта», то для названия можно создать сокращение рик. Каждый раз при наборе сокращения рик Excel автоматически будет заменять это сокращение на ОАО «Рога и копыта».
Список автозамены общий для всех приложений Microsoft Office, поэтому любые элементы автозамены, созданные в Excel, будут доступны и Word.

#автозамена #параметры

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

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

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

Текст по столбцам. Строковые функции. Конкатенация.

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

#Текстпостолбцам #строковыефункции #канкатенация #ПРОПНАЧ #СТРОЧН

Макрос. Список всех формул активного листа.

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

Sub СписокФормул()
    Dim InputRange As Range
    Dim OutputSheet As Worksheet
    Dim OutputRow As Long
    Dim Cell As Range
    
    Set InputRange = ActiveSheet.UsedRange
    Set OutputSheet = Worksheets.Add
    OutputRow = 1
    
    For Each Cell In InputRange
        If Cell.HasFormula Then
            OutputSheet.Cells(OutputRow, 1) = "'" & Cell.Address
            OutputSheet.Cells(OutputRow, 2) = "'" & Cell.Formula
            OutputRow = OutputRow + 1
        End If
    Next Cell
End Sub

#макрос #списокформул

Удаление содержимого ячейки

Для удаления содержимого ячейки многие привыкли использовать всего лишь одну клавишу <Delete>, но при данном способе удаления все атрибуты форматирования, которые были применены к ячейке, сохраняются.
Для более гибкого контроля над процессом удаления используется команда Очистить, имеющая пять дополнительных опций:
▪️Очистить все – полностью очищает ячейку. Будут удалены: содержимое ячейки, ее атрибуты и примечания;
▪️Очистить форматы – удаляет только атрибуты форматирования, сохраняя значение ячейки и примечание;
▪️Очистить содержимое – удаляет только содержимое ячейки, сохраняя все атрибуты форматирования и примечание;
▪️Очистить примечания – удаляет примечание (если оно есть);
▪️Очистить гиперссылки – удаляет гиперссылки в ячейках, текст сохраняется, но ячейка больше не работает как гиперссылка.

#удаление #содержимоеячейки #форматы #примечания #гиперссылки

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

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

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

Текстовые функции. Функции ЛЕВСИМВ, ПРАВСИМВ и ПСТР

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

#ЛЕВСИМВ #ПРАВСИМВ #ПСТР #текстовыефункции

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

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

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

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

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

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