Excel에서 데이터 연결을 자동으로 새로 고치고 워크 시트를 다시 계산하도록하는 방법에는 여러 가지가 있습니다. 이러한 트릭은 Microsoft 365 고객 용 Excel의 주식 데이터 유형에서 더 중요합니다.
일반적으로 Excel은 변경시 자동으로 업데이트됩니다. 요즘에는 셀이 값을 변경하지만 Excel이 워크 시트를 업데이트하지 않는 상황이 있습니다. 즉, 최신 Excel은 Microsoft가 아직 완전히 적응하지 못한 방식으로 변경되었습니다.
이상적으로는 Excel에는 n초 또는 분마다 워크 시트를 새로 고치는 전체 설정이 있습니다. 현재로서는이를 수행하기위한 해결 방법이 필요합니다.
다음은 데이터를 강제로 새로 고치거나 다시 계산해야하는 몇 가지 상황입니다. 주의하십시오.
주식 데이터 유형
주식 데이터 유형을 사용하면 자동으로 업데이트하는 기능이 더 중요합니다. 사용자는 워크 시트가 위도를 파악하기를 원할 것입니다. 가격을 자동으로 책정합니다. 현재 미리보기 버전에서는 할 수없는 일입니다. 멋진 자동 시세 표시대신 새로 고침을 클릭하여 최신 가격을 확인해야합니다.
주식 및 지역 데이터 유형은 호기심 많은 동물입니다. 외부 소스에 대한 데이터 연결이지만 Excel 데이터 연결로 표시되지 않습니다. 즉, 일반 데이터 연결 에서처럼 자동 데이터 새로 고침을 설정할 수 없습니다. 실제로 Stock 또는 Geo 데이터 유형에 대한 노출 된 컨트롤이 없습니다.
NOW () 및 기타 휘발성 함수
NOW () 함수는 Excel이있을 때마다 최신 날짜 및 시간으로 업데이트됩니다. 워크 시트를 다시 계산합니다. 그러나 그렇게 할 것이 없다면 Now ()는 값을 변경하지 않습니다. Excel에서 Now () 및 나머지 워크 시트를 업데이트하려면 몇 가지 외부 요소가 필요합니다. 즉, 워크 시트를 한 눈에 볼 수 있고 초 단위까지임을 알 수 있어야하지만 Excel에서 즉시사용할 수는 없습니다.
VBA 사용자 지정 함수는 다음을 사용하여 휘발성으로 태그 지정할 수도 있습니다. 함수 코드의 다음 줄 :
Application.Volatile
이 줄은 Excel에서 워크 시트를 업데이트 / 재 계산할 때마다 함수가 실행되도록합니다.
추가주의
예방 조치로 워크 시트가 자동으로 업데이트되도록 하시겠습니까? 많은 오래된 Excel 손이 Excel이 제대로 업데이트되지 않은 상황을 기억하여 벨트 및 중괄호접근 방식을 좋아합니다 (적어도 가끔).
코드
자동을 강제하는 표준 방법 Excel 업데이트는 VBA 코드의 짧은 스 니펫입니다. 우리가 사용하는 것은 다음과 같습니다. 동일한 주제에 대한 많은 변형이 있습니다. 전체 코드는 기사 하단에 있습니다.
세 가지 기능이 있습니다.
RefreshAllDataConn
데이터 연결 (Workbooks (ThisWorkbook.Name) .RefreshAll)의 실제 새로 고침을 수행하고 마지막 상태 표시 줄에 마지막으로 새로 고침 된 시간을 표시하기 위해 두 개의 선택적 행을 추가했습니다.
특별히주의하고 싶다면 명시 적으로 재 계산을 강제하는 줄을 추가하십시오. ActiveSheet.Calculate 또는 극단적 인 Application.CalculateFull (대용량 워크 시트의 속도를 늦추고 드물게 사용)
AutoRefresh
분마다 RefreshAllDataConn 하위 실행 또는 설정 한 값 줄 Application.OnTime Now + TimeValue ( “00:01:00”), “AutoRefresh”
Workbook_Open
워크 시트를 열 때 자동으로 실행되는 내장 Excel 함수 . 이 경우 자동 새로 고침이 시작됩니다.
데이터 연결 해결 방법
VBA 접근 방식의 단점은 .xlsm 워크 시트가 필요하다는 것입니다 (매크로 활성화 된 Excel 워크 시트). 보안 문제로 인해 매크로 사용 파일을 공유하는 데 문제가있을 수 있습니다.
PowerQuery / Get and Transform이 도입됨에 따라 워크 시트를 강제로 재 계산하는 또 다른 방법이 있습니다. 해결 방법이며 완벽하지는 않지만 가능하며 매크로를 사용하는 워크 시트가 필요하지 않습니다.
요컨대 자동 새로 고침 기능이있는 데이터 쿼리 설정이 있는지 확인하세요. 데이터 연결이없는 경우 워크 시트에 작은 연결을 추가합니다.
쿼리가 자동으로 새로 고쳐지면 일시적인 함수가 포함 된 워크 시트도 새로 고쳐 져야합니다.
이상적으로는 Stock 및 Geo 데이터 유형도 새로 고쳐야합니다.
모든 Excel 데이터 쿼리에는 쿼리 속성에서 몇 가지 새로 고침 옵션이 제공됩니다. 대부분은 기본적으로 꺼져 있습니다.
nnn 분마다 새로 고침 – 권장되는 60 분으로 기본적으로 꺼져 있습니다.
파일을 열 때 데이터 새로 고침
백그라운드 새로 고침 사용
모두 새로 고침시이 연결 새로 고침
자동 새로 고침 해결 방법은 작고 실질적으로 중요하지 않은 데이터 연결. 그런 다음 매분 또는 원하는 시간에 업데이트하도록 해당 데이터 연결을 구성합니다. 이렇게하면 위에서 언급 한 휘발성 함수를 포함하여 워크 시트가 업데이트됩니다.
이 해결 방법의 일부 버전은 동일한 컴퓨터에있는 작은 csv 파일에 대한 링크를 추가합니다. 워크 시트의 표에서 데이터 연결을 사용하여 동일한 결과를 얻었습니다.
단일 셀로 작은 표를 만듭니다. 셀은 무엇이든 가질 수 있지만 나중에 설명 할 이유 때문에 NOW ()가 포함 된 셀을 만듭니다.
표를 선택한 다음 데이터 | 데이터 얻기 | 다른 출처에서 | 테이블 / 범위에서. 정확한 메뉴 항목은 Excel 버전에 따라 다를 수 있습니다.
쿼리 편집기가 열리면 닫고로드하기 만하면됩니다. . 데이터 연결 창에 쿼리가 표시됩니다.
쿼리를 마우스 오른쪽 버튼으로 클릭하고 속성을 선택하여 필요한 설정입니다.
나에게 적합한 새로 고침 빈도를 설정하세요.
정리를 위해 소스 테이블 (오른쪽)을로드 된 쿼리 (왼쪽)의 동일한 시트로 이동합니다. 소스 테이블 셀에서 NOW ()를 사용했기 때문에 워크 시트가 언제 새로 고쳐 졌는지 쉽게 확인할 수 있습니다.
VBA 코드 예제
Sub RefreshAllDataConn() " Refresh all Data Connections. " This should include Stock and Geo data types, even though they aren"t listed. Workbooks(ThisWorkbook.Name).RefreshAll " Show update time on status bar to confirm. " comment these lines out if not needed. Application.DisplayStatusBar = True Application.StatusBar = "Refreshed at: " & Now()End SubSub AutoRefresh()" to run a Refresh All on the workbook every n minutes RefreshAllDataConn " Repeat every minute or change to whatever value you prefer. Application.OnTime Now + TimeValue("00:01:00"), "AutoRefresh"" this is a simple example. There"s no coded way to exit this function.End SubPrivate Sub Workbook_Open()" Starts the automatic refresh when the workbook is opened," commented out as a precaution. " AutoRefreshEnd Sub
자동 Excel 워크 시트 목록 또는 목차 만들기
공휴일이있는 Excel NetworkDays () 솔루션 완성 & 휴가