Метод blup в эксель
Экспоненциальное сглаживание выполняется на основе наблюдений данных и формул, это утомительная задача, но Excel предоставил нам встроенный инструмент для использования этого метода, после выбора данных, которые нам нужны, для перехода на вкладку «Данные», а затем в анализ данных, в котором мы найдем метод экспоненциального сглаживания.
Что такое экспоненциальное сглаживание в Excel?
Экспоненциальное сглаживание — один из трех основных методов прогнозирования продаж, используемых в статистических полях. Экспоненциальное сглаживание — более реалистичный метод прогнозирования, позволяющий получить лучшее представление о бизнесе.
- Логика экспоненциального сглаживания будет такой же, как и у других методов прогнозирования, но этот метод работает на основе взвешенных коэффициентов усреднения. Чем старше данные, тем меньше у них веса или приоритета, а для более свежих данных или релевантных данных им дается больший приоритет или больший вес.
- Несмотря на то, что экспоненциальное сглаживание учитывается для старых рядов данных, оно отдает предпочтение самым последним наблюдениям или рядам данных.
Типы экспоненциального сглаживания в Excel
В Excel доступны в основном 3 типа экспоненциального сглаживания.
- Простое / однократное экспоненциальное сглаживание: В этом типе а (альфа) близко к нулевому значению. когда а (альфа) близко к нулю, это означает, что скорость сглаживания очень низкая.
- Двойное экспоненциальное сглаживание: Этот метод подходит для анализа данных, которые показывают больше трендовых индикаторов.
- Тройное экспоненциальное сглаживание: Этот метод подходит для данных, которые показывают больше тренда, а также сезонности в ряду.
Где найти экспоненциальное сглаживание в Excel?
Экспоненциальное сглаживание является частью многих инструментов анализа данных в Excel. По умолчанию он не отображается в Excel. Если в вашем Excel не отображается инструмент анализа данных, следуйте нашим старым статьям, чтобы отобразить пакет инструментов анализа данных.
Если он не скрыт, вы должны увидеть параметр «Анализ данных» на вкладке «Данные».
Щелкните Анализ данных; вы увидите много статистических методов. В этой статье мы сосредоточимся на экспоненциальном сглаживании.
Как сделать экспоненциальное сглаживание в Excel?
Ниже приведены примеры выполнения экспоненциального сглаживания в Excel.
Вы можете скачать этот шаблон Excel с экспоненциальным сглаживанием здесь — Шаблон Excel с экспоненциальным сглаживанием
Пример экспоненциального сглаживания №1 — Прогноз продаж
Мы увидим одни простые данные, чтобы сделать прогноз продаж на следующий год. У меня есть данные о доходах за 10 лет.
Используя экспоненциальное сглаживание, нам нужно спрогнозировать доход.
Шаг 1: Щелкните вкладку Данные и Анализ данных.
Шаг 2: Выберите параметр «Экспоненциальное сглаживание».
Шаг 3: Для диапазона ввода укажите доступные точки данных. Диапазон наших данных — B1: B11.
Шаг 4: Коэффициент демпфирования сглаживает график, и его значения должны быть от 0 до 1. Технически это так. 1 — α (альфа). Я упомянул 0,3 как коэффициент демпфирования.
Шаг 5: Поскольку мы также выбрали наш заголовок во входном диапазоне, нам нужно поставить галочку в поле Labels.
Шаг 6: Теперь выберите диапазон, в котором будет отображаться выходной диапазон. Я выбрал следующий столбец существующих данных, то есть C2.
Шаг 7: Теперь нам нужно сказать, нужна ли нам диаграмма для графического представления данных или нет. Всегда рекомендуется представлять данные графически. Итак, выберите вывод диаграммы.
Шаг 8: Мы заполнили все поля. Нажмите ОК, чтобы получить результаты.
Пояснение: Мы установили коэффициент демпфирования на 0,3, а альфа — на 0,7. Это показывает, что для недавних значений (значений выручки за последние годы) имеет вес 70%, а для относительно старых значений — 30%.
График показывает более или менее ту же тенденцию в этом методе. Поскольку предыдущее значение за 2007 год отсутствует, Excel не может вычислить сглаженное значение, а сглаженное значение второй серии данных всегда равно первой точке данных.
Пример экспоненциального сглаживания № 2 — Тренд прогноза при различных коэффициентах демпинга
Введите эти числа в таблицу Excel вертикально.
Шаг 1: Щелкните вкладку Данные и Анализ данных.
Шаг 2: Выберите параметр «Экспоненциальное сглаживание».
Шаг 3: В поле «Диапазон ввода» выберите доступные сведения о предыдущем доходе. Коэффициент демпфирования 0,1.
Шаг 4: Нажмите ОК, чтобы отобразить результаты прогноза, если коэффициент демпфирования равен 0,1.
Теперь запустите экспоненциальное сглаживание еще два раза, изменив коэффициент демпфирования на 0,5 и 0,9.
Коэффициент демпфирования @ 0,5
Коэффициент демпфирования @ 0,9
У нас есть все три разных результата.
Теперь нарисуем для него график. Выберите данные и перейдите на вкладку вставки выберите линейную диаграмму.
График должен выглядеть так, как показано ниже. Вы можете изменить свою диаграмму в соответствии с вашими требованиями; на этом графике я изменил заголовок диаграммы и цвет линии.
Похожие темы научных работ по математике , автор научной работы — Кудрик Н. А., Горлов А. И., Ивина Е. А., Мокеев И. А., Шульга М. В.
Прогнозирование длительности послеоперационного восстановительного периода методом сердечно-дыхательного синхронизма (СДС) с применением АСК-анализа (часть 1)
Использование когнитивного подхода при построении сценариев развития регионального АПК работа выполнена в рамках темы «Разработка когнитивной методологии исследования и моделирования слабоструктурированных проблем социальноэкономических систем» АВЦП «Развитие научного потенциала высшей школы (20072011гг. )».
Текст научной работы на тему «Оценка баранов-производителей методом BLUP Sm»
ОЦЕНКА БАРАНОВ-ПРОИЗВОДИТЕЛЕЙ МЕТОДОМ BLUP SM
Н.А. Кудрик, кандидат с.-х. наук; А.И. Горлов, кандидат с.-х. наук; Е.А. Ивина, И.А. Мокеев, М.В. Шульга (Институт животноводства степных районов имени М. Ф. Иванова - Национальный селекционно-генетический центр по овцеводству)
Во многих странах мира для прогноза и оценки племенной ценности животных используются статистические модели смешанного типа. Эти модели включают в себе фиксированные и случайные (рандомизированные) факторы. Наилучшие линейные несмещенные (верные) оценки для фиксированных эффектов (значение которых у потомков одного производителя является константой, то есть фиксировано) и наилучший линейный несмещенный прогноз для случайных эффектов (значение которых у потомков одного производителя является случайной величиной) определяются по ММЕ (английская аббревиатура) - уравнению смешанной модели, которое имеет вид:
где у - вектор зависимой переменной; fi - вектор фиксированных эффектов; s - вектор рандомизированных эффектов родителей; е -вектор неучтенных случайных в модели факторов, средняя которых приближается к нулю и в большинство моделей не включается; X Z -матрицы, в которых зафиксировано наличие - 1 или отсутствие - 0 оцениваемых эффектов. Векторы fins модели (1) - неизвестные величины, которые определяются решением следующей системы линейных уравнений:
Zi обозначены в которых строки
В системе (2) 1 символами Х и
транспонированные по отношению к Х и Т матрицы стали столбцами.
Символом А обобщенно обозначены фиксированные эффекты Л], дк ( индексы ], к фиксированным эффектам Л], дк обозначают номер градации соответствующего эффекта). В матрицах количество столбцов равняется числу градаций фиксированных эффектов для матрицы X (в порядке Л2>. Л], д], д2, .дк) и числу градаций случайных эффектов для матрицы Ъ (в>, в2, .в,), количество строк обеих матриц
определяется общим количеством градаций фиксированных и случайных эффектов.
Матрица-столбец или (что то же) вектор у есть последовательно записанные значения признака потомков всех оцениваемых производителей (например, живой массы).
-индивидуальные номера и линии оцениваемых производителей;
-значения селекционного признака потомков и их пол.
Первым этапом решения системы (2) для определения племенной ценности производителей по качеству потомков методом BLUP SM (модель отца) является построение матриц X и Z, в которые входят фиксированные и случайные эффекты.
Процесс составления матриц иллюстрируется на четырех действующих баранах-производителях и их потомках двух линий ДПДГ "Аскания-Нова". В модели в качестве фиксированных эффектов приняты пол потомков и генетическая группа (линии), а градации случайных эффектов - бараны-производители.
В таблице Microsoft Excel данные сортируются по трем признакам одновременно: первый - генетическая группа, второй - номер барана-производителя, третий - пол потомков. Результаты сортировки приведены на рис. 1. Затем выявляются уникальные значения эффектов и их количество: для h (1, 2) - 2 (столбец А), для д (767, 831) - 2 (столбец В), для s (33564, 33587, 0477, 30881) - 4 (столбец С). Дальше наличие или отсутствие эффекта заносится в матрицы X и Z следующим образом. Последовательно элементы соответствующего столбца сравниваются с очередным уникальным значением эффекта. Если значения совпадают, то в очередном столбце и строке матрицы ставится единица. Например, берется первое уникальное значение столбца А (это единица) и сравнивается со всеми значениями в этом столбце. Три первых значения совпадают, поэтому в первом столбце матрицы X ставятся единицы (рис. 1), последующие пять значений не совпадают - ставятся нули и так до конца первого столбца матрицы X. Берется второе уникальное значение столбца А и повторяется такая же процедура, в результате которой формируется второй столбец матрицы X. Аналогичным способом ставятся единицы и нули в генетических группах g и группах производителей s; то есть, если эффект группы gj представляет линия 767, то в этом столбце будут стоять единицы, а напротив линии 831 -нули, а в эффекте группы д2 -наоборот, в эффекте S] единицы будут стоять напротив номера барана 33564, а дальше - нули и так далее. Значения продуктивности потомков заносятся в вектор У.
Дальше матрицы X и Z транспонируются с помощью функции: ТРАНСПОНИРОВАНИЕ. Определяются произведения матриц,
транспонированных и прямых, с помощью функции МУМНОЖ. Таким же образом определяются произведения транспонированных матриц и ]-вектора. В итоге получаются произведения: ХХ, X7Z, Ъ7Х, Ъ7Ъ, XV Ъу. К матрице Ъ7Ъ добавляется матрица ХА или Я/
родства, / -единичная матрица, ;] = , Л2 - коэффициент
А і в ! С I О ! Е ! р I е I нТ І I J
1 16.00 0.00 6.00 10.00 3.00 3.00 5.00 5.00 934.00
2 0 00 18.00 9.00 9.00 5.00 4.00 4.00 5.00 893,00
3 6.00 9.00 15.00 0.00 8 00 7.00 0.00 0.00 768.00
4 10.00 9.00 0.00 19.00 0.00 0.00 9.00 10.00 1059,00
5 3.00 5.00 8 00 0.00 23.00 0,00 0.00 0 0 0 396.00
6 3 00 4.00 7 00 0.00 0.00 22.00 0.00 ООО 372.00
7 5 00 4.00 0.00 9 00 0.00 0.00 24.00 ООО 500.00
8 Л 5 00 5.00 0.00 10.00 0.00 0 00 0,00 25.00 559.00
11 0.00 18,00 9.00 9.00 5.00 4,00 4.00 5.00 0.00 893,00
12 6.00 9.00 15.00 0.00 8.00 7.00 0.00 0 00 0.00 768.00
13 10.00 9.00 0.00 19.00 0.00 0.00 9.00 10.00 1.00 1059.00
14 3.00 5.00 8.00 0.00 23.00 0.00 0.00 0.00 0.00 396.00
15 3.00 4.00 7 00 0.00 0.00 22.00 0.00 0.00 0.00 372 00
16 5 00 4.00 0.00 9,00 0.00 0.00 24.00 0 00 0.00 500.00
17 5.00 5.00 0.00 10.00 0.00 0.00 0.00 25.00 0.00 559.00
18 0.00 0 00 0.00 1.00 0.00 0,00 0 00 0 00 0.00 0.00
19 20 0.11 0.06 -0.03 0.00 0.00 0.00 -0.03 -0.03 -1.00 59 67
21, 0.06 0.12 -0 09 0.00 0.00 0.00 -0.03 -0.04 -1.00 51.35
22 -0 08 -0.09 0.19 0.00 -0.03 -0.03 0.03 0.03 1.00 -3.44
23 0 00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.00 0.00
24 0.00 0.00 -0.03 0.00 0.06 0.01 0.00 0.00 0.00 -0.53
25 0 00 0.00 -0.03 0.00 0.01 0.06 0.00 0 00 0.00 0.53
26 -0.03 -0 03 0.03 0 00 0.00 0.00 0.05 0.01 0.00 -0.16
27 -0.03 -0.04 0.03 0.00 0.00 0.00 0.01 0.05 0.00 0.16
28 -1.00 -1.00 1.00 1.00 0.00 0.00 0 00 0.00 0.00 0,00
Рис. 1 Создание матриц по первичным данным Конечная система уравнений смешанной модели (2) получается присоединением произведений в вышеупомянутом порядке (рис. 2, верхняя часть, в которой в столбце / стоят свободные члены уравнения). Как правило, такая система линейно зависимая и не имеет решения. Поэтому для превращения системы в линейно независимую применяется искусственный прием.
Это можно сделать двумя путями: или вычеркнуть столбец и строку
фиксированного эффекта, а значения вычеркиваемой строки прибавить ко
всем другим строкам, а столбец - ко всем оставшимся столбцам (при
этом порядок системы уменьшится на единицу), или прибавить к системе
одну нулевую строку и нулевой столбец (при этом порядок системы
увеличится на единицу) и поставить в них единицу с номером столбца
градации, который предполагалось исключить (рис. 2, средняя система
уравнений). Независимо от вида искусственного приема, результаты
решения не меняются.
Для решения системы надо матрицу (на рис. 2 это А 10:118) обратить с помощью функции МОБР и умножить на вектор у (рис. 2, Л0:Л8 ) с помощью функции МУМНОЖ. В результате определяются корни системы (рис. 2, строки 20-28 столбца J), являющиеся оценками племенной ценности животных, из которых J20:J23 есть наилучшие линейные несмещенные оценки для фиксированных эффектов, показывающие их прогнозируемые различия.
АТв I С |'Р I Е I F I G ~| н I I I ~Т~Гк I I ~~| м Г 'FT [ о
1 2 3 h 1 9 767 S 33564 hi h2 1 X gi 0 g2 1 0 s1 s2 1 z s3 0 s4 0 0 У 47
4 1 767 33564 1 0 1 0 1 0 0 0 54
5 1 767 33564 1 0 1 0 1 0 0 0 55
‘6 2 767 33564 0 1 1 0 1 0 0 0 47
7 2 767 33564 0 i 1 0 1 0 0 0 49
8 2 767 33564 0 1 1 0 1 0 0 0 40
9 2 767 33564 0 1 1 0 1 0 0 0 52
10 2 767 33564 0 1 1 0 1 0 0 0 52
11 1 767 33587 1 0 1 0 0 1 0 0 51
12~ 1 767 33587 1 0 1 0 0 1 0 0 61
13 1 767 33587 1 0 1 0 0 1 0 0 55
14 2 767 33587 0 1 1 0 0 1 0 0 53
'15 2 767 33587 0 1 1 0 0 1 0 0 44
16 2 767 33587 0 1 1 0 0 1 0 0 50
17 2 767 33587 0 1 1 0 0 1 0 0 48
18 | 1 831 0477 1 0 0 1 0 0 1 0 70
19 1 831 0477 1 0 0 1 0 0 1 0 57
20 1 831 0477 1 0 0 1 0 0 1 0 62
21 1 831 0477 1 0 0 1 0 0 1 0 55
22 1 831 0477 1 0 0 1 0 0 1 0 57
23 2 831 0477 0 1 0 1 0 0 1 0 53
24 2 831 0477 0 1 0 1 0 0 1 0 49
26 2 831 0477 0 1 0 1 0 0 1 0 50
26' 2 831 0477 0 1 0 1 0 0 1 0 47
27 1 831 30881 1 0 0 1 0 0 0 1 58
'28 1 831 30881 0 0 1 0 0 0 1 60
29 1 831 30881 1 0 0 1 0 0 0 1 57
30 1 831 30881 1 0 0 1 0 0 0 1 53
31 1 831 30881 1 0 0 1 0 0 0 1 72
32 2 831 30881 0 1 0 1 0 0 0 1 53
33 2 831 30881 0 1 0 1 0 0 0 1 50
34 2 831 30881 0 1 0 1 0 0 0 1 48
35' 2 831 30881 0 1 0 1 0 0 0 1 48
'36 2 831 30881 0 1 0 1 0 0 0 1 60
Рис. 2. Системы уравнений и решения независимой линейной системы
Ранги корней системы J24:J27 характеризуют производителей с точки зрения наилучшего линейного несмещенного прогноза по исследуемому селекционируемому признаку. Таким образом, описан алгоритм оценки баранов-производителей методом BLUP SM в среде Microsoft Excel, что делает метод доступным для широкого круга селекционеров.
1. Кузнецов В.М. Основы научных исследований в животноводстве /В.М.Кузнецов - Киров: Зональный НИИСХ Северо-Востока, 2006. - С. 299-568.
2. Кузнецов В.М. Методы племенной оценки животных с введением в теорию BLUP /В.М.Кузнецов - Киров: Зональный НИИСХ Северо-Востока, 2003. - 358 с.
3. Даншин В.А. Оценка генетической ценности животных /В.А. Даншин -Киев: Аграрная наука, 2008. - 180 с.
BLUP — это метод селекционной и генетической оценки животных. Это индекс, оценка, цифра, значение которой позволяет сделать вывод о практической ценности (генетический потенциал, продуктивные качества и т.д.) для конкретной свиньи, коровы, лошади, собаки и т.д. В основе метода лежат очень сложные математические и статистические расчеты.
Ключевые преимущества использования BLUP на практике:
- максимально точное разделение критериев определяющих продуктивность животного:
- влияние окружающей среды
- генетика (наследственность);
BLUP — аббревиатура от английского словосочетания "Best Linear Unbiased Prediction", которое переводится как "лучший линейный неискаженный прогноз".
Понятие BLUP было введено Чарльзом Роем Хендерсоном, профессором Корнельского универститета в 50-е годы, правда в то время речь шла только о теоретической модели — абсолютно не приемлемой для практического использования ввиду крайней сложности вычислений. Но с конца 70-х и начала 80-х годов, с развитием науки и совершенствованием компьютерных технологий, которые позволили быстро производить сложные статистические и математические расчеты, метод стал использоваться в селекционной практике.
Оценка племенной ценности необходима, чтобы корректно перевести качество наследственной основы (например, высокий среднесуточный привес) в числовое выражение. Это обусловливает необходимость разработки соответствующих статистических методов, которые на основе собственной продуктивности (фенотип) позволяют сделать заключение о генетической предрасположенности к определенной продуктивности (племенная ценность).
Продуктивность конкретного животного определяется не только племенной ценностью — она также сильно зависит от условий окружающей среды в которой оно находится(содержание и кормление). Упрощенно это можно представить в виде формулы: «Фенотип = Генотип + Окружающая среда». Чем выше коэффициент наследуемости признака, тем больше вклад генотипа.
Так, свинья, генетически предрасположенная к высокой продуктивности, но содержащаяся в неудовлетворительных условиях, при плохом кормлении или низком статусе здоровья никогда не проявит свой потенциал в должной мере.
Разумеется, другие негенетические факторы тоже оказывают влияние. Например, толщина шпика зависит от веса животного, а число поросят при рождении — от количества опоросов. Все эти факторы необходимо учитывать, чтобы можно было корректно сравнивать животных друг с другом. Поэтому крайне важно иметь способ для точного определения племенной ценности, который позволил бы различать вклад генетической составляющей и условий среды в проявление продуктивности.
Необходимо особо отметить отличие BLUP от других селекционных методов, оно одно, но крайне важное — это статистическая неискаженность. Свойство неискаженности достигается за счет одновременного определения племенной ценности и влияния среды. Получается система, состоящая из множества уравнений с очень большим числом неизвестных (от десятков тысяч до нескольких миллионов). Решить ее можно только с помощью компьютера.
Как же на практике используют BLUP? Данные о собственной продуктивности животных поступают в базу данных, который содержит в себе очень подробную информацию обо всех животных популяции, даже включая тех, которые уже давно не существуют. Этот банк данных ежедневно пополняется новыми сведениями о продуктивности молодых животных и их происхождении. На основании этих данных инициируется BLUP-оценка с использованием модели конкретного животного. В результате рассчитываются значения племенной ценности, которые представляют из себя цифры — отклонения от средних показателей для данной популяции.
Эти значения умножаются на экономический вес признаков. Он рассчитывается исходя из вклада конкретного признака в общую рентабельность, а также его соответствующего веса в селекции. Например, в отцовских линиях признаки мясной продуктивности имеют, как правило, больший вес по сравнению с материнскими. С другой стороны, продуктивность линий с недостатками в признаках может быть повышена за счет увеличения их экономического веса.
В результате суммирования BLUP-значений племенной ценности и экономических весов каждого из признаков получают BLUP-индекс — монетарное преимущество потомков племенного животного в сравнении со средним значением популяции.
Чтобы легче анализировать и учитывать общую племенную ценность, среднее значение всех животных популяции принимают за 100, а значение стандартного отклонения приравнивают к 20 пунктам BLUP-индекса. Таким образом, BLUP-индекс несет в себе еще и точность оценки: животные с менее точными значениями племенной ценности, обусловленными недостаточным количеством информации о продуктивности, сильнее регрессируют (стремятся) к среднему значению по сравнению с животными с более точными значениями.
Говоря о сравнимости значений племенной ценности, следует подчеркнуть, что они действуют только внутри оцениваемой популяции. Если между популяциями нет генетических связей, то значения племенной ценности не сопоставимы друг с другом вследствие различий в статистических моделях, оцениваемых признаках, экономических весах, определении прибыли и т.д., а также в генетическом уровне спаривания.
Я получаю этот запрос все время. У людей огромные наборы данных, и кто-то в их команде выделил некоторые записи, отформатировав их жирным шрифтом.
Теперь вы тот, кто получает эти данные, и вам нужно отфильтровать все эти записи, которые имеют полужирное форматирование.
Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите отфильтровать все ячейки, отформатированные полужирным шрифтом.
Давайте посмотрим правде в глаза.
Нет простого способа сделать это.
Вы не можете просто использовать фильтр Excel, чтобы выделить все выделенные жирным шрифтом ячейки. Но это не значит, что вам нужно тратить часы и делать это вручную.
В этом уроке я покажу вам три способа фильтрации ячеек с полужирным форматированием шрифта в Excel:
Метод 1. Фильтрация выделенных жирным шрифтом ячеек с помощью функции "Найти и заменить"
Найти и заменить можно использовать для поиска определенного текста на листе, а также определенного формата (например, цвета ячейки, цвета шрифта, полужирного шрифта, цвета шрифта).
Идея состоит в том, чтобы найти на листе выделенное жирным шрифтом форматирование и преобразовать его во что-то, что можно легко отфильтровать. (Подсказка: цвет ячейки можно использовать как фильтр).
Вот шаги фильтрации ячеек с полужирным шрифтом:
На описанных выше шагах мы преобразовали текстовый формат, выделенный жирным шрифтом, в формат, который распознается Excel как критерий фильтрации.
Теперь, чтобы отфильтровать эти ячейки, выполните следующие действия:
- Выберите весь набор данных.
- Перейдите на вкладку "Данные".
- Щелкните значок фильтра (Сочетание клавиш: Control + Shift + L)
- Для столбца, который вы хотите отфильтровать, щелкните значок фильтра (стрелка вниз в ячейке).
- В раскрывающемся списке перейдите к параметру «Фильтр по цвету» и выберите цвет, который вы применили к ячейкам с текстом, выделенным жирным шрифтом.
Это автоматически отфильтрует все ячейки с полужирным шрифтом.
Метод 2 - Использование формулы Get.Cell
Пришло время для скрытого драгоценного камня в Excel. Это макро-функция Excel 4 - GET.CELL ().
Это старая функция, которая не работает на листе как обычные функции, но по-прежнему работает в именованных диапазонах.
Функция GET.CELL дает вам информацию о ячейке.
Например, он может сказать вам:
- Имеет ли ячейка полужирное форматирование или нет
- Если в ячейке есть формула или нет
- Заперта ли ячейка или нет и тд.
Вот синтаксис формулы GET.CELL
- Type_num является аргументом для указания информации, которую вы хотите получить для указанной ячейки (например, если вы введете 20 в качестве type_num, он вернет TRUE, если ячейка имеет полужирный формат шрифта, и FALSE, если нет).
- Справка ссылка на ячейку, которую вы хотите проанализировать.
Теперь позвольте мне показать вам, как фильтровать ячейки с текстом, выделенным жирным шрифтом, используя эту формулу:
- Перейдите на вкладку "Формулы".
- Нажмите на опцию Define Name.
- В диалоговом окне «Новое имя» используйте следующие сведения:
- Имя: FilterBoldCell
- Объем: Рабочая тетрадь
- Относится к: = GET.CELL (20, $ A2)
- Щелкните ОК.
- Перейдите в ячейку B2 (или любую ячейку в той же строке, что и первая ячейка набора данных) и введите = FilterBoldCell
- Скопируйте эту формулу для всех ячеек столбца. Он вернет ИСТИНА, если ячейка выделена жирным шрифтом, и ЛОЖЬ, если это не так.
- Теперь выберите весь набор данных, перейдите на вкладку «Данные» и щелкните значок «Фильтр».
- В столбце, где у вас есть ИСТИНА / ЛОЖЬ, выберите раскрывающийся список фильтра и выберите ИСТИНА.
Все ячейки с текстом, выделенным жирным шрифтом, теперь отфильтрованы.
Примечание. Поскольку это функция макроса, вам необходимо сохранить этот файл с расширением .xlsm. или .xls расширение.
Метод 3 - фильтрация выделенных жирным шрифтом ячеек с помощью VBA
Вот еще один способ фильтрации ячеек с текстом, выделенным жирным шрифтом, с помощью VBA.
- Щелкните правой кнопкой мыши вкладку рабочего листа и выберите Просмотреть код (или используйте сочетание клавиш ALT + F11). Это открывает серверную часть редактора VB.
- В окне редактора VB будет панель Project Explorer. Если его там нет, перейдите в «Просмотр» и выберите «Обозреватель проектов».
- На панели Project Explorer щелкните правой кнопкой мыши книгу (VBAProject), над которой вы работаете, перейдите в Insert и нажмите Module. Это вставляет модуль, в который мы поместим код VBA.
- Дважды щелкните значок модуля (чтобы убедиться, что ваш код включен в модуль) и вставьте следующий код в панель справа:
- Перейдите на рабочий лист и используйте следующую формулу: = Полужирный шрифт (B2)
- Эта формула возвращает значение ИСТИНА, если к ячейке применено полужирное форматирование, и ЛОЖЬ в противном случае. Теперь вы можете просто отфильтровать все ИСТИННЫЕ значения (как показано в методе 2).
Опять таки! В этой книге теперь есть макрос, поэтому сохраните его с расширением .xlsm или .xls.
Надеюсь, у вас будет достаточно времени для столь необходимого перерыва на кофе 🙂
Вы знаете другой способ сделать это? Я хотел бы поучиться у вас. Оставляйте свои мысли в разделе комментариев и будьте крутыми.
CAGR или совокупный годовой темп роста — это метод расчета темпа роста определенной суммы ежегодно, по умолчанию у нас нет встроенной формулы в Excel для расчета CAGR для нас, вместо этого мы делаем категории в таблицах, а в таблицах мы применяем следующую формулу для расчета CAGR, который выглядит следующим образом: (Конечный баланс / Начальный баланс) ˄ (1 / Количество лет) — 1.
Формула CAGR в Excel (сложный годовой темп роста)
Формула CAGR в Excel — это функция, которая отвечает за возврат значения CAGR, т. Е. Значения сложного годового темпа роста из предоставленного набора значений. Если вы занимаетесь финансовым анализом или планированием, вам нужно будет рассчитать совокупный годовой темп роста в формате Excel в таблицах Excel.
Формула CAGR в Excel измеряет значение возврата инвестиций, которое рассчитывается за определенный период времени. Формула составного годового темпа роста в Excel часто используется в таблицах Excel финансовыми аналитиками, владельцами бизнеса или инвестиционными менеджерами, что помогает им определить, насколько развился их бизнес, или в случае сравнения роста доходов с компаниями-конкурентами. С помощью CAGR можно увидеть, насколько постоянные темпы роста должны приносить доход от инвестиций в год. На самом деле скорость роста должна меняться во времени или из года в год.
Например, если вы купили золото в 2010 году на сумму 200 долларов США, а в 2018 году оно стоит 500 долларов США, CAGR — это скорость, с которой эти инвестиции росли каждый год.
- Конечное значение = Конечная стоимость инвестиции
- Начальное значение = Начальная стоимость инвестиции
- п = количество инвестиционных периодов (месяцев, лет и т. д.)
Возвращаемое значение:
- Возвращаемое значение будет числовым значением, которое можно преобразовать в процент, потому что значение CAGR эффективно, когда оно выражено в процентах.
Как использовать формулу CAGR в Excel с примерами?
Давайте разберемся, как использовать формулу CAGR в Excel на примерах.
Давайте рассмотрим таблицу Excel ниже. Посмотрите на данные.
Шаг 1 — Вы можете увидеть приведенную выше таблицу, в которой столбец A отнесен к категории «YEAR», а столбец B — к категории «AMOUNT».
В столбце YEAR значение начинается с ячейки A2 и заканчивается ячейкой A10.
И снова в столбце СУММА значение начинается с ячейки B2 и заканчивается ячейкой B10.
Следовательно, мы можем видеть, что начальная стоимость инвестиции (SV) — это ячейка B2, а конечная стоимость инвестиций (EV) — это ячейка B10.
Шаг 2— Теперь у нас есть значения, которые можно ввести в формулу CAGR (Сложный годовой темп роста) в Excel. Чтобы успешно сделать это в своей электронной таблице Excel, вы должны выбрать любую из ячеек столбца C и ввести формулу, как показано ниже —
= (B10 / B2) ˆ (1/9) -1
В приведенном выше примере совокупного годового прироста в Excel конечное значение — B10, начальное значение — B2, а количество периодов — 9. См. Снимок экрана ниже.
Шаг 3 — Теперь нажмите Enter. Вы получите результат значения CAGR (сложный годовой темп роста) внутри ячейки, в которую вы ввели формулу. В приведенном выше примере значение CAGR будет 0,110383. Возвращаемое значение — это просто оценка формулы CAGR в Excel со значениями, которые были описаны выше. Посмотрите на скриншот ниже.
Шаг 4— Обратите внимание, что совокупный годовой темп роста в Excel всегда представлен в виде процентов в поле финансового анализа. Чтобы получить значение CAGR в процентах, вы должны выбрать ячейку, в которой присутствует ваше значение CAGR, и изменить формат ячейки с «General» на «Percentage». Процентное значение CAGR (совокупный годовой темп роста) в приведенном выше примере составляет 11,04%. Вы можете увидеть скриншот ниже.
Приведенные выше шаги показывают, как рассчитать совокупный годовой темп роста в электронных таблицах Excel (CAGR).
Вы также можете использовать формулу POWER в методе Excel для поиска значения CAGR в вашей электронной таблице Excel. Формула будет иметь вид «= МОЩНОСТЬ (Конечное значение / Начальное значение, 1/9) -1». Вы можете видеть, что функция POWER заменяет ˆ, который использовался в традиционной формуле CAGR в Excel. Если мы используем функцию POWER в приведенной выше таблице Excel, где мы использовали традиционный метод для определения значения CAGR, результат будет 0,110383 или 11,03%. Рассмотрим скриншот ниже.
Это гораздо менее используемый метод расчета значения или процента CAGR (сложного годового темпа роста), но также и чистый способ. Синтаксис функции RATE в excel может показаться вам немного сложным, но если вы хорошо знаете термины, это не будет слишком сложно для вас. Синтаксис функции RATE приведен ниже:
= СТАВКА (кпер; пт; пв; [fv], [type], [guess])
Давайте теперь объясним приведенные выше термины.
- кпер — (Обязательно) Это общее количество платежей, совершенных за определенный период.
- pmt — (Обязательно) Это сумма платежа, производимого за каждый период.
- pv — (Обязательно) Это текущая стоимость.
- fv — (Необязательно) Это будущее значение.
- тип — Это означает, когда наступает срок выплаты. Значение равно 0 или 1. 0 означает, что платеж подлежал оплате в начале, а 1 означает, что платеж подлежал оплате в конце периода.
IRR — это аббревиатура от Internal Rate of Return. Метод IRR полезен, когда вам нужно найти значение CAGR (совокупный годовой темп роста) для платежей различной стоимости, которые были произведены в течение определенного периода времени. Синтаксис функции IRR в Excel: «= IRR (values, [guess]). » Значения означают общий диапазон чисел, который представляет денежные потоки. Этот раздел должен содержать одно положительное и одно отрицательное значение. [Guess] в необязательном аргументе в синтаксисе, что означает ваше предположение о возможной доходности.
Ошибки формул CAGR
Читайте также: