Arhn - архитектура программирования

Excel меняет формулу ячейки

Работаю с файлом Excel ок. 10 000 строк на каждом из 12 листов. Этот файл довольно часто используется для извлечения данных (т. Е. Отсортированных, отфильтрованных, измененных вручную), но я никогда не вмешиваюсь в столбцы, содержащие формулы.

За последние пару недель я заметил, что некоторые контрольные значения в ячейках менялись. Единственная причина, по которой я это видел, заключается в том, что excel изящно указывает, когда формула отличается от формул выше и ниже (с маленькими зелеными стрелками). До этого момента я просто нажимал «Восстановить формулу столбца».

Пример:

=IFERROR(IF(G148/E148>100%,100%,G148/E148),"")

был изменен на,

=IFERROR(IF(G157/E157>100%,100%,G157/E157),"")

находясь в строке 148

Теперь это не большая проблема, чтобы изменить все значения, обычно это <100 значений. Но я боюсь, что в тот день это превратится в более 9000 значений.

Моя цель - выяснить первопричину этой проблемы и свести на нет ее дальнейшее возникновение. Я использую Excel 2010.

РЕДАКТИРОВАТЬ: Это пример проблемы, о которой я говорю. Ссылочные строки должны продолжаться по порядку, но вы заметите, что строки 337, 341 и 345 все изменились на разные строки (209, 224, 158 соответственно).

Проблема под рукой.

excel excel-formula
31.07.2014

  • Новая строка будет вставлена ​​вручную, а формула скопирована, чтобы заполнить существующие столбцы. В то время формула будет ссылаться на правильный номер строки. 31.07.2014
  • Вы единственный, кто пользуется этим листом? Есть ли шанс, что кто-то может перетаскивать ячейки или вырезать и вставлять их куда угодно? Если какая-либо из ячеек, на которые указывают ваши формулы, перемещается таким образом, ссылка будет перемещаться вместе с ними. 01.08.2014
  • Если вы вставите строку, приведенные ниже формулы будут изменены, чтобы отразить их новое местоположение. Если вы добавите строку внизу, этого не произойдет. Вы действительно выполняете вставку и не хотите, чтобы ячейки ниже отражали их новое местоположение? ЕСЛИ это так, то вам может потребоваться функция КОСВЕННО. 01.08.2014
  • @Dave - я не единственный, кто использует этот лист, но я единственный, кто его обновляет. Обычно, если я копирую и вставляю данные формулы, я проверяю, что они встроены после вставки. 01.08.2014
  • @Ron Rosenfeld - Нет, если я сделаю вставку, я бы предпочел изменить ссылку на строку. Однако на этот раз дело обстоит не так. Я не вставлял никаких новых строк в таблицу, но ссылки время от времени меняются. Может ли это быть вызвано работой с данными во время их фильтрации? Я попытаюсь опубликовать пример. 01.08.2014
  • @ColeLange Я полагаю, это зависит от деталей того, что именно вы делаете, когда работаете с данными. Но я не видел, чтобы Excel волей-неволей менял формулы ячеек. Так что должно быть что-то, что делает кто-то другой, чтобы что-то изменить. В экземпляре в вашем OP это выглядит так, как будто было вставлено 9 строк. 01.08.2014
  • @Ron Rosenfeld - Если бы были вставлены 9 строк и они ссылались на строку 157, это было бы здорово, проблема в том, что она ссылается на строку 157, но все еще находится в строке 148. Я отредактирую вопрос, чтобы попытаться сделать это более понятным. 01.08.2014
  • @ColeLange Я не имел в виду, что было фактически вставлено 9 строк; просто формула действовала именно так. 01.08.2014
  • Позвольте нам продолжить это обсуждение в чате. 01.08.2014

Ответы:


1

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

Не очень понятно. Давай попробуем еще

У меня есть стол

col1    col2    col3
1       2
2       4

в col3 мне нужна сумма элементов одной и той же строки в col1 и col2.

Без функции таблицы в Excel у меня были бы следующие формулы в таблице

=A2+B2
=A3+B3

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

=[col1]+[col2]
=[col1]+[col2]

Формула одинакова для всех строк, и таблица знает, что использовать одну и ту же строку данных.

01.08.2014

2

Ответил на свой вопрос.

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

С учетом сказанного, я считаю, что этот пост теперь является копией следующего: Как отсортировать таблицу в Excel, если в ней есть ссылки на ячейки?

01.08.2014
Новые материалы
Коллекции публикаций по глубокому обучению
Последние пару месяцев я создавал коллекции последних академических публикаций по различным подполям глубокого обучения в моем блоге https://amundtveit.com - эта публикация дает обзор 25..
Представляем: Pepita
Фреймворк JavaScript с открытым исходным кодом Я знаю, что недостатка в фреймворках JavaScript нет. Но я просто не мог остановиться. Я хотел написать что-то сам, со своими собственными..
Советы по коду Laravel #2
1-) Найти // You can specify the columns you need // in when you use the find method on a model User::find(‘id’, [‘email’,’name’]); // You can increment or decrement // a field in..
Работа с временными рядами спутниковых изображений, часть 3 (аналитика данных)
Анализ временных рядов спутниковых изображений для данных наблюдений за большой Землей (arXiv) Автор: Рольф Симоэс , Жильберто Камара , Жильберто Кейрос , Фелипе Соуза , Педро Р. Андраде ,..
3 способа решить квадратное уравнение (3-й мой любимый) -
1. Методом факторизации — 2. Используя квадратичную формулу — 3. Заполнив квадрат — Давайте поймем это, решив это простое уравнение: Мы пытаемся сделать LHS,..
Создание VR-миров с A-Frame
Виртуальная реальность (и дополненная реальность) стали главными модными терминами в образовательных технологиях. С недорогими VR-гарнитурами, такими как Google Cardboard , и использованием..
Демистификация рекурсии
КОДЕКС Демистификация рекурсии Упрощенная концепция ошеломляющей О чем весь этот шум? Рекурсия, кажется, единственная тема, от которой у каждого начинающего студента-информатика..