Что означает идентификация в excel
Есть "форма отчета" с общим доступом для заполнения значениями из выпадающих списков. Сейчас каждый пользователь, заполнивший строку, выбирает свою фамилию из списка. Ести риск подмены фамилий/ошибки выбора. Можно ли как-то задать идентификацию этих изменений? Ну типа по аналогии с текущей датой (=ТДАТА()), только чтоб было "текущий пользователь", и чтоб эту ячейку нельзя было изменить.
Есть "форма отчета" с общим доступом для заполнения значениями из выпадающих списков. Сейчас каждый пользователь, заполнивший строку, выбирает свою фамилию из списка. Ести риск подмены фамилий/ошибки выбора. Можно ли как-то задать идентификацию этих изменений? Ну типа по аналогии с текущей датой (=ТДАТА()), только чтоб было "текущий пользователь", и чтоб эту ячейку нельзя было изменить. ilistra
Вобщем я наверное не умею формулировать свои мысли. Попробую
перефразировать: есть форма с общим доступом, каждый пользователь,
входящий в windows под своим логином, заполняет ячейки либо в
произвольной форме, либо выбирает значение из списка. В столбце A
прописана формула с условиями заполнения ячеек. Куча столбцов с
выпадающими списками замедляет работу и увеличивает объем файла.
Мне бы хотелось усовершенствовать ее и облегчить работу сотрудникам
(меньше действий, производимых вручную), а именно:
1. Чтобы в столбце H автоматически прописывался логин пользователя,
внесшего изменения в столбце G
2. Аналогично со столбцом I - отображалось время внесение записи в
столбец G
3. По возможности, чтоб данные из столбца H невозможно было
изменить бесследно (чтоб не подтасовывали результаты), но и чтобы
остальные пользователи не могли удалить историю изменений как,
например, в журнале изменений.
4. Я не являюсь опытным пользователем, с макросами не имела дела
никогда.
Буду рада любой помощи, но желательно с пошаговыми инструкциями.
P.S. Ну а если кто-то поможет мне упростить формулу - это будет для
меня полным восторгом и пределом мечтаний.
Спасибо.
Вобщем я наверное не умею формулировать свои мысли. Попробую
перефразировать: есть форма с общим доступом, каждый пользователь,
входящий в windows под своим логином, заполняет ячейки либо в
произвольной форме, либо выбирает значение из списка. В столбце A
прописана формула с условиями заполнения ячеек. Куча столбцов с
выпадающими списками замедляет работу и увеличивает объем файла.
Мне бы хотелось усовершенствовать ее и облегчить работу сотрудникам
(меньше действий, производимых вручную), а именно:
1. Чтобы в столбце H автоматически прописывался логин пользователя,
внесшего изменения в столбце G
2. Аналогично со столбцом I - отображалось время внесение записи в
столбец G
3. По возможности, чтоб данные из столбца H невозможно было
изменить бесследно (чтоб не подтасовывали результаты), но и чтобы
остальные пользователи не могли удалить историю изменений как,
например, в журнале изменений.
4. Я не являюсь опытным пользователем, с макросами не имела дела
никогда.
Буду рада любой помощи, но желательно с пошаговыми инструкциями.
P.S. Ну а если кто-то поможет мне упростить формулу - это будет для
меня полным восторгом и пределом мечтаний.
Спасибо. ilistra
вдруг кому пригодится:
при изменении конкретной ячейки автоматически выставляется время и логин в windows (слепила из того, что нашла на разных форумах, так что скорей всего это не идеальный вариант)
вдруг кому пригодится:
при изменении конкретной ячейки автоматически выставляется время и логин в windows (слепила из того, что нашла на разных форумах, так что скорей всего это не идеальный вариант)
Создание и оформление электронных таблиц. Основные приемы работы.
Количество часов: 4
Цель: Знакомство с приложением MS Excel, со строением его окна, приобретение навыков работы с электронными таблицами : создание, форматирование, организация простейших вычислений
Теоретический материал:
СЧЕТ - функция СЧЁТ подсчитывает количество ячеек, содержащих числа, и количество чисел в списке аргументов. Функция используется для получения количества числовых ячеек в диапазонах или массивах ячеек.
Диапазон А2:А6 содержит только три числа, результат функции равен 3.
СЧЕТЕСЛИ - Функция СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют одному указанному пользователем критерию. , можно подсчитать количество всех ячеек, которые начинаются с определенной буквы или в которых содержатся числа, большие или меньшие указанного значения.
Функция СУММ - Суммирует все числа в интервале ячеек.
Пример: СУММ(3; 2) - суммирует 3 и 2 , сумма равна 5.
СУММ(A2:A4) – суммирует три числа в приведенном диапазоне - А2, А3, А4
Функция СУММЕСЛИ – Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазона (Диапазон. Две или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными.), соответствующие указанным условиям. Предположим, например, что в столбце с числами необходимо просуммировать только значения, большие 5. Для этого можно использовать формулу : =СУММЕСЛИ(B2:B25;">5")
Синтаксис функции: СУММЕСЛИ (диапазон, критерий, [диапазон суммирования])
Диапазон. Обязательный аргумент. Диапазон ячеек, оцениваемый по условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки, содержащие числа. Пустые ячейки и ячейки, содержащие текстовые значения, не учитываются.
Критерий. Обязательный аргумент. Критерий в форме числа, выражения, ссылки на ячейку или текста или функции, определяющий, какие ячейки необходимо просуммировать. Например, условие можно записать как 32, ">32", B5, "32", "яблоки"
Диапазон_суммирования. Необязательный аргумент. Ячейки, по которым проводится суммирование, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Microsoft Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется условие).
Функция СРЗНАЧ - возвращает среднее значение (среднее арифметическое) аргументов. Пример : диапазон (Диапазон. Две или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными.) A1:A20 содержит числа, формула =СРЗНАЧ(A1:A20) возвращает среднее значение этих чисел.
1 Откройте MS Excel, создайте таблицу с указанными полями в соответствии с вашим вариантом. Вариант выбирается по последней цифре зачетки, перечень вариантов смотреть в приложении.
2 Заполните в таблице не менее 10 записей. Сохраните в файле.
3 Вставьте перед таблицей новую строку, наберите ее название и установите его по центру таблицы.
4 Оформите таблицу, используя диалоговое окно «Формат ячеек», на панели «Число» главной вкладки меню в соответствии со следующими требованиями:
a. Выбрать оптимальную ширину столбцов и строк,
b. Названия полей выровнять по центру ячейки, записи должны иметь горизонтальное выравнивание «По центру»
c. Задайте таблице обрамление тонкой линией, поля таблицы отделите от записей иной линией,
d. Название одного из полей разверните на 90°
5 Используя функции СУММ, СУММЕСЛИ, СЧЕТ, СЧЕТЕСЛИ, СРЗНАЧ произвести вычисления согласно варианту.
6 Выбрать одну из пяти функций из теоретического материала выше, неиспользуемых вами в расчетах 5го задания. Придумать задачу, в результате решения которой использовалась бы выбранная вами функция.
7 Отсортировать таблицу: первый столбец – по возрастанию, второй – по убыванию:
a. Выделить данные таблицы (заголовки не выделять)
b. Для выполнения сортировки на ГЛАВНОЙ вкладке, выбрать панель
РЕДАКТИРОВАНИЕ, нажать кнопку СОРТИРОВКА И ФИЛЬТРАЦИЯ.
Выбрать НАСТРАИМАЕВУЮ СОРТИРОВКУ.
c. В открывшемся окне сортировки определить: столбец для сортировки - А, сортировка – по значению, порядок – по возрастанию.
d. Добавить еще один уровень сортировки, и определить : столбец для сортировки - B, сортировка – по значению, порядок сортировки определить самостоятельно.
8 Провести фильтрацию данных.
a. Выделить столбец с данными таблицы вместе с заголовком, в котором будет проверяться условие фильтрации.
b. Для выполнения фильтрации на ГЛАВНОЙ вкладке, панель РЕДАКТИРОВАНИЕ, нажать кнопку СОРТИРОВКА И ФИЛЬТРАЦИЯ.
c. Выбрать команду ФИЛЬТР.
d. Нажать на кнопку с треугольником, которая появилась рядом с названием заголовка :
e. В открывшемся окне выбрать ЧИСЛОВЫЕ ФИЛЬТРЫ, определить два фильтра самостоятельно, согласно варианту задания.
9 Постройте диаграмму на основании данных таблицы, добавьте колонтитул ;
10 Определите изображение, которое будет использоваться как фон листа.
11 Отключите линии сетки.
12 Ответьте на вопросы в этом же файле.
1. Что значит «Табличное представление данных»?
2. Какое максимальное число листов может содержать рабочая книга?
3. Перечислите команды контекстного меню клетки.
4. Что включает строка редактора формул?
5. В скольких видах может быть представлен маркер в Excel? Нарисовать их и указать назначение.
6. Как выполнить постраничное отображении е документа?
7. Назначение строки состояния окна в Excel.
8. Что означает идентифицировать объект электронной таблицы? Что такое идентификация?
9. Перечислите объекты электронной таблицы, и приведите их идентификаторы.
Приложение
Вариант 0. Молодежное правительство
Поля: Название проекта, количество человек, дата выполнения, город в котором проводится проект (всего нее более трех городов)
Вычислить: сколько задействовано человек во всех проектах,
количество проектов, проводимых в одном городе
среднее количество человек, участвующих в проектах,
Вариант 1. Кадровое агентство
Поля:Город,фирма предоставляющая вакансии, вакансия, количество указанных вакансий
Вычислить: Сколько фирм запрашивает вакансии в агентстве?
Количество видов вакансий, запрашиваемых в одном городе
Среднее количество требуемых вакансий
Вариант 2. Центр психологической помощи
Поля: Фамилия клиента, социальный статус (не более трех вариантов) , возраст, количество предыдущих обращений за психологической помощью.
Вычислить: Средний возраст клиентов,
Суммарное количество предыдущих обращений в центр
Количество клиентов одного социального статуса,
Вариант 3 . Медицинское учреждение
Поля: Фамилия пациента, социальный статус, возраст, количество больничных листов за последний год
Вычислить: Средний возраст пациентов,
Количество пациентов одного социального статуса,
Суммарное количество больничных листов за последний год
Вариант 4. Рекламное агентство
Поля: Наименование заказчика, вид рекламной продукции, дата сдачи, тираж;
Один заказчик может заказать несколько видов рекламной продукции.
Вычислить: Суммарный тираж всей рекламной продукции,
Количество видов рекламной продукции у одного выбранного заказчика.
Вариант 5. Образовательное учреждение
Поля: Фамилия студента, группа (не более трех групп), возраст, количество несданных предметов
Вычислить: Средний возраст студентов,
Суммарное количество несданных предметов
Количество несданных предметов в одной группе
Вариант 6. Издательство
Поля: Наименование заказчика, вид печатной продукции, дата сдачи, тираж;
Вычислить: Суммарный тираж всей печатной продукции,
Количество видов печатной продукции у одного выбранного заказчика
Вариант 7. Телевидение
Поля: Название проекта, количество человек, дата эфира, город где проводится съемка
Вычислить: среднее количество человек, участвующих в проектах,
сколько задействовано человек во всех проектах,
количество проектов, проводимых в одном городе
Вариант 8. Студия дизайна
Поля: Наименование заказчика, проект, дата сдачи, финансовые затраты;
Один заказчик может заказать несколько проектов.
Вычислить: Суммарные финансовые затраты,
Средняя сумма затрат,
Количество проектов у одного выбранного заказчика.
Вариант 9. Тренинговый центр (центр обучения)
Поля: Название тренинга, фамилия клиента, социальный статус (не более трех вариантов) , возраст.
Один клиент может участвовать в нескольких тренингах. Фамилии указывать вразброс.
Один Таблица Информация представлена в графическом интерфейсе, состоящий из линий si столбцов. Колонны к ним прикреплены буквы, а строки пронумерованы числами. Электронная таблица содержит 256 столбцов и 65536 1 строк. Столбцы имеют метки в верхней части окна документа и помечены буквами (A, B,…, Z), а затем двухбуквенными комбинациями (AA, AB - IV). Строки пронумерованы от 65536 до XNUMX в левой части окна документа.
На пересечении столбца и есть линия клеток. Ячейка является основной ячейкой для хранения данных. Это можно ссылаться с помощью буквы столбца и номера строки, которая является пересечением (например, A5, B7).
В ячейку можно ввести несколько типов информации:
· Текст - Конец таблице тегов, заметки, тексты;
· Величины - Цифры, даты и времени, логические значения;
· Формулы - Формула, с которой новое значение рассчитывается на основе информации из других клеток.
Активная ячейка клетки, помеченной. Информация, которую вы вводите появляется в активной ячейке. Чтобы ввести данные в другую ячейку активируется первой ячейки. После выхода из ячейки ввода , в противном случае существует вероятность того, что эти приказы не могут быть применены. Таблицы иногда может быть очень большим. В таких ситуациях, чтобы найти или ввести информацию конкретных методов путешествия должны быть использованы электронные таблицы. Движение может быть сделано с помощью мыши или клавиатуры. Для того, чтобы путешествие (по дороге) мыши с помощью вертикальной и горизонтальной полосами прокрутки находится в правой и нижней части каждой таблицы.
Каждая панель содержит две стрелки. При запуске нажмите на стрелку можно выполнить следующие операции:
▲ - прокрутка на одну строку вверх
▼ - линия спиральной один вниз
◄ - выделите одну колонку оставили
► - Прокрутка вправо один столбец
Чтобы двигаться быстрее через лист можно использовать прокрутку окон (путем перетаскивания полосы прокрутки). Место ползунка в полосе прокрутки указывает на относительное положение окна на всю область таблицы. Ячейка может быть активирована быстро, поместив курсор мышьНа базовой станции и, нажав на левую кнопку мышьAcquis.
Перемещение по таблице с помощью клавиатуры можно сделать с помощью следующих клавиш:
Как добавить столбец уникального идентификатора для повторяющихся строк в Excel?
Добавление отсутствующего идентификационного номера с помощью Kutools for Excel
Добавление столбца уникального идентификатора для повторяющихся строк с помощью формулы
Чтобы добавить уникальный идентификационный номер для повторяющихся данных, выполните следующие действия:
1. введите 1 в ячейку рядом с первыми данными, в которые вы хотите добавить идентификационный номер.
2. Затем в ячейке под ним введите эту формулу =IF(B1=B2,A1,A1+1) , Нажмите Enter Чтобы получить первый результат, перетащите дескриптор заполнения вниз, пока не появятся последние данные.
В формуле B1, B2 - первые две ячейки в столбце повторяющихся данных, A1 - первая ячейка идентификатора.
Добавление отсутствующего идентификационного номера с помощью Kutools for Excel
Если у вас есть список идентификационных номеров, который включает некоторые недостающие номера, как показано на скриншоте ниже, вы можете подать заявку. Kutools for ExcelАвтора Find Missing Sequence Number утилита для быстрого поиска и добавления недостающего числа.
После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите идентификационные номера, в которые вы хотите вставить отсутствующие номера, нажмите Кутулс > Insert > Find Missing Sequence Number .
2. Во всплывающем диалоговом окне вы можете выбрать один вариант, чтобы добавить или выделить отсутствующие числа по мере необходимости.
3. Нажмите Ok. Появится диалоговое окно с напоминанием о количестве недостающих данных, закройте его. Затем все отсутствующие числа были добавлены или выделены в столбце или рядом с ним.
Регрессионный анализ заключается в определении аналитического выражения связи зависимой случайной величины Y (называемой так же результативным признаком) с независимыми случайными величинами X1, X2,…Xm (называемыми так же факторами).
Форма связи результативного признака Y с факторами X1, X2,…Xm получила название уравнения регрессии. В зависимости от типа выбранного уравнения различают линейную и нелинейную регрессии (в последнем случае возможно дальнейшее уточнение: квадратичная, экспоненциальная, логарифмическая и т.д.).
В зависимости от числа взаимосвязанных признаков различают парную и множественную регрессии. Если исследуется связь между двумя признаками (результативным и факторным), то регрессия называется парной, если между тремя и более признаками – множественной (многофакторной) регрессией.
При изучении регрессии следует придерживаться определенной последовательности этапов:
1. Задание аналитической формы уравнения регрессии и определение параметров регрессии.
2. Определение в регрессии степени стохастической взаимосвязи результативного признака и факторов, проверка общего качества уравнения регрессии.
3. Проверка статической значимости каждого коэффициента уравнения регрессии и определение их доверительных интервалов.
Основное содержание этапов регрессионного анализа
Основное содержание выделенных этапов рассмотрим на примере множественной линейной регрессии, реализованной в режиме «Регрессия» надстройки Пакет анализа Microsoft Excel.
Этап 1. Уравнение линейной множественной регрессии имеет вид
где – теоретические значения результативного признака, полученные путем подстановки соответствующих значений факторных признаков в уравнение регрессии;
– значения факторных признаков;
– параметры уравнения (коэффициенты регрессии).
Параметры уравнения регрессии могут быть определены с помощью метода наименьших квадратов (именно этот метод используется в Microsoft Excel). Сущность данного метода заключается в нахождении параметров модели (ai), при которых минимизируется сумма квадратов отклонений эмпирических (фактических) значений результативного признака от теоретических, полученных по выбранному уравнению регрессии, т.е.
Рассматривая S в качестве функции параметров ai и проводя математические преобразования (дифференцирование), получаем систему нормальных уравнений с m неизвестными (по числу параметров ai):
где n – число наблюдений;
m – число факторов в уравнении регрессии.
Решив систему уравнений, находим значения параметров ai, являющихся коэффициентами искомого теоретического уравнения регрессии.
Этап 2. Для определения величины степени стохастической взаимосвязи результативного признака Y и факторов X необходимо знать следующие дисперсии:
- общую дисперсию результативного признака Y, отображающую влияние как основных, так и остаточных факторов:
где – среднее значение результативного признака Y;
- факторную дисперсию результативного признака Y, отображающую влияние только основных факторов:
- остаточную дисперсию результативного признака Y, отображающую влияние только остаточных факторов:
При корреляционной связи результативного признака и факторов выполняется соотношение
Для анализа общего качества уравнения линейной многофакторной регрессии используют обычно множественный коэффициент детерминации R 2 , называемый также квадратом коэффициента множественной корреляции R. Множественный коэффициент детерминации рассчитывается по формуле
и определяет долю вариации результативного признака, обусловленную изменением факторных признаков, входящих в многофакторную регрессионную модель.
Так как в большинстве случаев уравнение регрессии приходится строить на основе выборочных данных, то возникает вопрос об адекватности построенного уравнения генеральным данным. Для этого проводится проверка статической значимости коэффициента детерминации R 2 на основе F-критерия Фишера:
где n – число наблюдений;
m – число факторов в уравнении регрессии.
Примечание. Если в уравнении регрессии свободный член а0 = 0, то числитель n-m-1 следует увеличить на 1, т.е. он будет равен n-m.
В математической статистике доказывается, что если гипотеза H0 : R 2 = 0 выполняется, то величина F имеет F-распределение с k = m и l = n-m-1 числом степеней свободы, т.е.
Гипотеза H0 : R 2 = 0 о не значимости коэффициента детерминации R 2 отвергается, если .
При значениях R 2 >0,7 считается, что вариация результативного признака Y обусловлена в основном влиянием включенных в регрессионную модель факторов X.
Этап 3. Возможна ситуация, когда часть вычисленных коэффициентов регрессии не обладает необходимой степенью значимости, т.е. значения данных коэффициентов будут меньше их стандартной ошибки. В этом случае такие коэффициенты должны быть исключены из уравнения регрессии. Поэтому проверка адекватности построенного уравнения регрессии наряду с проверкой значимости коэффициента детерминации R 2 включает в себя так же и проверку значимости каждого коэффициента регрессии.
Значимость коэффициентов регрессии проверяется с помощью t-критерия Стьюдента:
где – стандартное значение ошибки для коэффициента регрессии .
В математической статистике доказывается, что если гипотеза H0 : ai = 0 выполняется, то величина t имеет распределение Стьюдента с k = n-m-1 числом степеней свободы, т.е.
Гипотеза H0 : ai = 0 о незначимости коэффициента регрессии отвергается, если .
Кроме того, зная значение tкр, можно найти границы доверительных интервалов для коэффициентов регрессии:
Для работы с регрессией открываем вкладку Сервис –> Анализ данных –> Регрессия (см. рис. 2.24.).
В диалоговом окне Регрессия задаются следующие параметры:
3. Флажок Метки – устанавливается в активное состояние, если первая строка (столбец) во входном диапазоне содержит заголовки. Если заголовки отсутствуют, флажок следует деактивировать. В этом случае будут автоматически созданы стандартные названия для данных выходного диапазона.
4. Уровень надежности – установите данный флажок в активное состояние, если в поле, расположенном напротив флажка необходимо ввести уровень надежности отличный от уровня 95%, применяемого по умолчанию. Установленный уровень надежности используется для проверки значимости коэффициента детерминации R 2 и коэффициентов регрессии аi. (Уровень надежности оставляем по умолчанию 95 %).
5. Константа-ноль – установите данный флажок в активное состояние, если требуется, чтобы линия регрессии прошла через начало координат (т.е. а0 = 0).
6. Выходной интервал/Новый рабочий лист/Новая рабочая книга.
В положении Новый рабочий лист открывается новый лист, в который начиная с ячейки А1, вставляются результаты анализа. Если необходимо задать имя открываемого нового рабочего листа, введите его имя в поле, расположенное напротив соответствующего положения переключателя.
В положении Новая рабочая книга открывается новая Книга, на первом листе которой, начиная с ячейки А1, вставляются результаты анализа.
В первой таблице сгенерированы результаты по регрессионной статистике. Эти результаты соответствуют следующим статистическим показателям:
1. Множественный R – коэффициенту корреляции R;
2. R-квадрат – коэффициенту детерминации R 2 ;
3. Стандартная ошибка – остаточному стандартному отклонению
- Наблюдения – числу наблюдений n.
В следующей таблице сгенерированы результаты дисперсионного анализа, которые используются для проверки значимости коэффициента детерминации R 2 .
1. Столбец df – число степеней свободы.
Для строки Регрессия число степеней свободы определяется количеством факторных признаков m в уравнении регрессии .
Для строки Остаток число степеней свободы определяется числом наблюдений n и количеством переменных в уравнении регрессии .
Для строки Итого число степеней свободы определяется суммой .
2. Столбец SS – сумма квадратов отклонений.
Для строки Регрессия – это сумма квадратов отклонений теоретических данных от среднего:
Для строки Остаток – это сумма квадратов отклонений эмпирических данных от теоретических:
Для строки Итого – это сумма квадратов отклонений эмпирических данных от среднего:
3. Столбец MS – дисперсии, рассчитываемые по формуле
Для строки Регрессия – это факторная дисперсия .
Для строки Остаток – это остаточная дисперсия .
4. Столбец Значимость F – значение уровня значимости, соответствующее вычисленному значению Fp.
В последней таблице сгенерированы значения коэффициентов регрессии ai и их статические оценки.
1. Коэффициенты – значения коэффициентов ai.
2. Стандартная ошибка – стандартные ошибки коэффициентов ai.
3. t-статистика – расчетные значения t-критерия, вычисляемые по формуле
4. Р-значение – значения уровней значимости, соответствующие вычисленным значениям tp.
5. Нижние 95% и Верхние 95% - соответственно нижние и верхние границы доверительных интервалов для коэффициентов регрессии ai.
Читайте также: